PDA

View Full Version : VBA Code is too long and slow to load data from on worksheet to another


dlpessleo
08-24-2014, 10:13 PM
Hi All,

I am brand new to VBA and wrote this code to copy data from one worksheet to another. The code works fine, but is very long and takes a long time to copy the data.

Can you please help me shorten it so that it works faster?? Any help would be appreciated:yes

The code is as follows:


Sub Findnextrow()
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D13").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B13").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A13").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D14").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B14").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A14").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D15").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B15").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A15").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D16").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B16").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A16").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D17").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B17").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A17").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D18").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B18").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A18").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D19").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B19").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A19").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D20").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B20").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A20").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D21").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B21").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A21").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("E10").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 2).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B5").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 3).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("D22").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 4).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("B22").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 5).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet3.Select
Range("A22").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 6).Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
mancubus
08-24-2014, 11:02 PM
pls use code tags when posting your code. click # button and [ CODE ][ /CODE ] tags (without spaces before/after brackets) will be inserted. paste your code between opening [ CODE ] and closing [ /CODE ] tags. you can type in these tags as well.

try

Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Sheet3.Range("E9").Value

instead of

Sheet3.Select
Range("E9").Select
Selection.Copy
Sheet6.Select
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


avoid SELECTs...
dlpessleo
08-24-2014, 11:08 PM
Thanks for the feedback. I'll try your suggestion:)
mancubus
08-24-2014, 11:23 PM
you are welcome.

for a contiguous range:


Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(12, 3).Value = Sheet3.Range("E9:G20").Value

resize the destination cell by the number of rows & columns of the range which will be copied.
p45cal
08-25-2014, 08:26 AM
try:
Sub Macro1b()
Set erow = Sheet6.Cells(Sheet6.Rows.Count, 1).End(xlUp).Offset(1, 0)
With Sheet3
erow.Resize(10, 2).Value = Application.Transpose(.Range("E9:E10").Value)
erow.Offset(, 2).Resize(10).Value = .Range("B5").Value
erow.Offset(, 3).Resize(10).Value = .Range("D13:D22").Value
erow.Offset(, 4).Resize(10).Value = .Range("B13:B22").Value
erow.Offset(, 5).Resize(10).Value = .Range("A13:A22").Value
End With
End Sub