Hi guys,
Just wanted to see if there was anyway to speed this code up... I read that you can assigned cell values instead of copy/paste method but cant figure it out.
Basically,
Column A on destination is copying IDs from source.
Columns D, F, H are copying times using vlookup.
Columns C, E, G are saying if there is a time, then this.
the "speedup.scr" is the usual turning off screenupdates, setting calculations to manual then auto, etc. I just have that code exported to save me time so I dont always have to re-write it.. just import and call it..
Thank you for any help.
Just wanted to see if there was anyway to speed this code up... I read that you can assigned cell values instead of copy/paste method but cant figure it out.
Basically,
Column A on destination is copying IDs from source.
Columns D, F, H are copying times using vlookup.
Columns C, E, G are saying if there is a time, then this.
the "speedup.scr" is the usual turning off screenupdates, setting calculations to manual then auto, etc. I just have that code exported to save me time so I dont always have to re-write it.. just import and call it..
Thank you for any help.
VBA Code:
Sub copy()
speedup.scr False
Dim Source As Worksheet, Destination As Worksheet
Set Source = ThisWorkbook.Worksheets("CAN Daily Hours Summary")
Set Destination = ThisWorkbook.Worksheets("Adjustment_Data")
Destination.Range("A1").CurrentRegion.Offset(1).Cells.clear
Source.Activate
Range("A:A").Select
With Selection
.NumberFormat = "General"
.Value = .Value
End With
Range("A2", Range("A2").End(xlDown)).copy Destination.Range("A2")
Destination.Activate
LastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(3).row
Range("B2").Formula = "=IF(IFERROR(LEFT('CAN Daily Hours Summary'!B2, SEARCH("","", 'CAN Daily Hours Summary'!B2)-1),"""")=0,"""",IFERROR(LEFT('CAN Daily Hours Summary'!B2, SEARCH("","", 'CAN Daily Hours Summary'!B2)-1),""""))"
Range("B2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("B2:B" & LastRow1)
Range("C2").Formula = "=IF(D2<>"""",""Role Premium"","""")"
Range("C2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("C2:C" & LastRow1)
Range("D2").Formula = "=INDEX('CAN Daily Hours Summary'!$K:$K,MATCH(A2,'CAN Daily Hours Summary'!$A:$A))"
Range("D2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("D2:D" & LastRow1)
Range("E2").Formula = "=IF(F2<>"""",""RolePrmium2OT"","""")"
Range("E2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("E2:E" & LastRow1)
Range("F2").Formula = "=INDEX('CAN Daily Hours Summary'!$L:$L,MATCH(A2,'CAN Daily Hours Summary'!$A:$A))"
Range("F2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("F2:F" & LastRow1)
Range("G2").Formula = "=IF(H2<>"""",""RolePrmium2DT"","""")"
Range("G2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("G2:G" & LastRow1)
Range("H2").Formula = "=INDEX('CAN Daily Hours Summary'!$M:$M,MATCH(A2,'CAN Daily Hours Summary'!$A:$A))"
Range("H2").Select
If Not LastRow1 <= 2 Then Selection.AutoFill Destination:=Range("H2:H" & LastRow1)
speedup.scr True
End Sub