PDA

View Full Version : VBA Macros Copy Paste Seed Up Help



tva9128
01-09-2018, 06:39 PM
Hello, I am new to using VBA and I have one that is used to copy data from one sheet to another. I am trying to speed up the macro and used the below. Can someone tell me why this is still taking so long? There are ton of formulas in my file, but even just 100 lines is taking forever. Any help would be good.


Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("data")
Set Target = ActiveWorkbook.Worksheets("Issue Comparison")
j = 4 ' Start copying to row 1 in target sheet

For Each c In Source.Range("BH4:BH15000") ' Do 12000 rows
If c = "Review" Then
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c

Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting
End Sub

offthelip
01-10-2018, 04:38 AM
The reason your macro is slow is because however good VBA is it is very very slow at interacting with the worksheet. I just did a quick test on my computer and the time taken to interact with one cell was about 0.1 milliseconds. You are interacting with 15000 cells individually about 3 or four times in your loop so that is going to take a least a minute.
What is interesting is that you can load all the contents of a worksheet into memory in almost the same time as it takes to load a single cell. So the way to speed up you macro by a factor of 500 or so is to load the source worksheet into a variant array and then do the copying between this array and an output array and then write the whole array back, just two accesses to the worksheet so it will be done in well under a second. So try this code:
Note I have got rid of the things which you put in it to try a speed it up which are not necessary with this code:


Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Variant
Dim Target As Variant
' Change worksheet designations as needed
Source = ActiveWorkbook.Worksheets("data").Range("A1:BH15000")
Target = ActiveWorkbook.Worksheets("Issue Comparison").Range("A1:BH15000")

j = 4 ' Start copying to row 1 in target sheet
For i = 4 To 12000 ' Do 12000 rows
If Source(i, 60) = "Review" Then
For k = 1 To 60
Target(j, k) = Source(i, k)
Next k
j = j + 1
End If
Next i

ActiveWorkbook.Worksheets("Issue Comparison").Range("A1:BH15000") = Target

End Sub

tva9128
01-10-2018, 12:17 PM
Thanks for the help, this works perfectly. The only issue I am having is that I need it to copy through BJ not BH. How would I update it accordingly to accommodate the other columns I need returned?

[QUOTE=offthelip;375043]The reason your macor is slow is because however good VBA is it is very very slow at interacting with the worksheet. I just did a quick test on my computer and the time taken to interact with one cell was about 0.1 milliseconds. You are interacting with 15000 cells individually about 3 or four times in your loop so that is going to take a least a minute.
What is interesting is that you can load all the contents of a worksheet into memory in almost the same time as it takes to load a single cell. So the way to speed up you macro by a factor of 500 or so is to load the source worksheet into a variant array and then do the copying between this array and an output array and then write the whole array back, just two accesses to the worksheet so it will be done in well under a second. So try this code:
Note I have got rid of the things which you put in it to try a speed it up which are not necessary with this code:


Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Variant
Dim Target As Variant
' Change worksheet designations as needed
Source = ActiveWorkbook.Worksheets("data").Range("A1:BH15000")
Target = ActiveWorkbook.Worksheets("Issue Comparison").Range("A1:BH15000")

j = 4 ' Start copying to row 1 in target sheet
For i = 4 To 12000 ' Do 12000 rows
If Source(i, 60) = "Review" Then
For k = 1 To 60
Target(j, k) = Source(i, k)
Next k
j = j + 1
End If
Next i

ActiveWorkbook.Worksheets("Issue Comparison").Range("A1:BH15000") = Target

End Sub[/QUOT

offthelip
01-10-2018, 12:30 PM
Sub CopyYes() Dim j As Integer
Dim Source As Variant
Dim Target As Variant
' Change worksheet designations as needed
Source = ActiveWorkbook.Worksheets("data").Range("A1:BJ15000")
Target = ActiveWorkbook.Worksheets("Issue Comparison").Range("A1:BJ15000")

j = 4 ' Start copying to row 1 in target sheet
For i = 4 To 12000 ' Do 12000 rows
If Source(i, 60) = "Review" Then
For k = 1 To 62
Target(j, k) = Source(i, k)
Next k
j = j + 1
End If
Next i

ActiveWorkbook.Worksheets("Issue Comparison").Range("A1:BJ15000") = Target

End Sub