Saturday, 21 January 2017

Excel VBA to loop and copy paste on variable ranges to variable ranges

I have a loop which changes the ranges of the copy cells and the paste cells.
This is working with Select - but is causing the code to run slowly.
How can I improve this to not use the Select?



    Dim i As Long
Dim x As Long
Dim y As Long



Dim lastcell As Long



Dim countnonblank As Integer, myrange As Range
Set myrange = Sheets("Label Create Worksheet").Columns("A:A")
countnonblank = Application.WorksheetFunction.CountA(myrange)

lastcell = Int(countnonblank / 9) + 1

For x = 0 To lastcell


i = i + 1

y = y + IIf(x = 0, 0, 9)




Sheets("Label Create Worksheet").Select
Range(Cells(2 + y, 1), Cells(2 + y, 6)).Select
Selection.Copy


Sheets("Data").Select
Cells(1 + i, 1).Select
ActiveSheet.Paste


Sheets("Label Create Worksheet").Select
Range(Cells(3 + y, 1), Cells(3 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy


Sheets("Data").Select
Cells(1 + i, 11).Select
ActiveSheet.Paste

Sheets("Label Create Worksheet").Select
Range(Cells(4 + y, 1), Cells(4 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy


Sheets("Data").Select
Cells(1 + i, 21).Select
ActiveSheet.Paste

Sheets("Label Create Worksheet").Select
Range(Cells(5 + y, 1), Cells(5 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Data").Select

Cells(1 + i, 31).Select
ActiveSheet.Paste

Sheets("Label Create Worksheet").Select
Range(Cells(6 + y, 1), Cells(6 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Data").Select
Cells(1 + i, 41).Select

ActiveSheet.Paste

Sheets("Label Create Worksheet").Select
Range(Cells(7 + y, 1), Cells(7 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Data").Select
Cells(1 + i, 51).Select
ActiveSheet.Paste


Sheets("Label Create Worksheet").Select
Range(Cells(8 + y, 1), Cells(8 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Data").Select
Cells(1 + i, 61).Select
ActiveSheet.Paste


Sheets("Label Create Worksheet").Select
Range(Cells(9 + y, 1), Cells(9 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Data").Select
Cells(1 + i, 71).Select
ActiveSheet.Paste

Sheets("Label Create Worksheet").Select

Range(Cells(10 + y, 1), Cells(10 + y, 6)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Data").Select
Cells(1 + i, 81).Select
ActiveSheet.Paste


Next x




Set myrange = Nothing

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...