Hi,
I have put together the macro below to do various things (sort, add formulae, copy and reformat).
For some reason the code to insert formulae only works intermittently and I can't pinpoint the cause. Can anyone see what the issue might be?
Thanks
I have put together the macro below to do various things (sort, add formulae, copy and reformat).
For some reason the code to insert formulae only works intermittently and I can't pinpoint the cause. Can anyone see what the issue might be?
Thanks
VBA Code:
Sub CopyToStore()
'Sort columns
With ThisWorkbook.Sheets("Data").Sort
.SortFields.Add Key:=Range("A1"), Order:=xlAscending
.SortFields.Add Key:=Range("F1"), Order:=xlAscending
.SortFields.Add Key:=Range("B1"), Order:=xlAscending
.SetRange Range("A1:U100000")
.Header = xlYes
.Apply
End With
'Insert formulae
FinalRow = ThisWorkbook.Sheets("Data").Cells(Rows.Count, 2).End(xlUp).Row 'Counts # of rows in the data set
Range(ThisWorkbook.Sheets("Data").Cells(3, 22).Address, Cells(FinalRow, 22).Address).Formula = "=J3-J2"
'Copy to Store
With ThisWorkbook.Worksheets("Data")
.Range("A2:V" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Store").Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End With
'Format time value
With ThisWorkbook.Worksheets("Store")
.Range("B:B").NumberFormat = "dd/mm/yyyy hh:mm:ss.000"
End With
End Sub