JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
Hi
It looks through 5 sheets and copies data if criteria is met
CopyTo - is just to get the line no of last row + 1
Even with Application.ScreenUpdating off - its takes roughly 30 - 40 seconds which i feel is slow for this small loop
note that CopyFrom never exceeds 200
thanks
It looks through 5 sheets and copies data if criteria is met
CopyTo - is just to get the line no of last row + 1
Even with Application.ScreenUpdating off - its takes roughly 30 - 40 seconds which i feel is slow for this small loop
note that CopyFrom never exceeds 200
VBA Code:
Sub DataMerge1()
Dim WS As Variant
Application.ScreenUpdating = False
Set CurrentWS = ThisWorkbook.Sheets("DataMerge")
CopyTo = CurrentWS.Range("A5000").End(xlUp).Row + 1
For Each WS In Array(Sheet15, Sheet16, Sheet17, Sheet18, Sheet19)
CopyFrom = 2
'MONDAY
Do While WS.Cells(CopyFrom, 32) <> ""
If WS.Cells(CopyFrom, 56).Value > 0 Then
CurrentWS.Cells(CopyTo, 2).Value = WS.Cells(CopyFrom, 32).Value
CurrentWS.Cells(CopyTo, 3).Value = WS.Cells(CopyFrom, 33).Value
CurrentWS.Cells(CopyTo, 4).Value = WS.Cells(CopyFrom, 54).Value
CurrentWS.Cells(CopyTo, 5).Value = WS.Cells(CopyFrom, 55).Value
CurrentWS.Cells(CopyTo, 6).Value = WS.Cells(CopyFrom, 56).Value
CurrentWS.Cells(CopyTo, 7).Value = WS.Cells(CopyFrom, 58).Value
CopyTo = CopyTo + 1
End If
CopyFrom = CopyFrom + 1
Loop
Next WS
Application.ScreenUpdating = True
End Sub
thanks