VBA Macros Copy Paste Seed Up Help

[tva9128]

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]

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]

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]

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