Optimize VBA code and get rid of Workbook.activate and range.select?

Question

I've got a macro which copies a code from one workbook to another one. My code is pretty long and I would like to shorten it if possible (also to be a bit more independent from the macro-recorder)

My code looks like this:

Workbooks("export.XLSX").Activate
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).row).Copy
Workbooks("ORDERS.CSV").Activate
Range("X3").Select
Selection.End(xlDown).Offset(1, -18).Select
ActiveSheet.Paste
Workbooks("export.XLSX").Activate

This block repeats itself for different columns. Is there a way to shorten this?

More examples:

Range("C2:C" & Range("A" & Rows.Count).End(xlUp).row).Copy 
Workbooks("ORDERS.CSV").Activate 
Range("X3").Select Selection.End(xlDown).Offset(1, -14).Select 
ActiveSheet.Paste 
Workbooks("export.XLSX").Activate 
Range("G2:G" & Range("A" & Rows.Count).End(xlUp).row).Copy 
Workbooks("ORDERS.CSV").Activate 
Range("X3").Select Selection.End(xlDown).Offset(1, -13).Select 
ActiveSheet.Paste 
Workbooks("export.XLSX").Activate

We need to see more examples of your code before we can recommend improvements. — Dai, Jul 12 '13 at 06:34
see [this Q&A](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) as a beginning.... — Kazimierz Jawor, Jul 12 '13 at 06:39

score 0 · Accepted Answer · answered Jul 12 '13 at 08:44

Firt of all add Application.ScreenUpdating = False

Workbooks("export.XLSX").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Copy
Workbooks("ORDERS.CSV").Range("X3").End(xlDown).Offset(1, -18).Select.PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

and follow the same pattern, then Application.Screenupdating = True

Optimize VBA code and get rid of Workbook.activate and range.select?

1 Answers1