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