Hi all
Could someone please help get a macro to work?
We extract a report from a system and the A column has a series of transaction dates, I want to change these to be the week-ending Sunday date, so I insert a column B put this formula in =IF(A2="","",A2+7-MOD(A2-1,7)) and fill down.
Then copy-paste B with values and delete A.
But the macro doesn't seem to work, can you see what I am doing wrong? or is there a better way to do this?
Thanks in advance.
Could someone please help get a macro to work?
We extract a report from a system and the A column has a series of transaction dates, I want to change these to be the week-ending Sunday date, so I insert a column B put this formula in =IF(A2="","",A2+7-MOD(A2-1,7)) and fill down.
Then copy-paste B with values and delete A.
But the macro doesn't seem to work, can you see what I am doing wrong? or is there a better way to do this?
Thanks in advance.
VBA Code:
Sub Macro2()
'
' Macro2 Macro
'
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]+7-MOD(RC[-1]-1,7))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B1011")
Range("B2:B").Select
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Cells.EntireColumn.AutoFit
Range("B7").Select