Probably need a loop of some sort to achieve that. Are you already running some loop in your current code? Can you show what you have in your code so fa
There is no loop in the VBA - and in fairness this macro is very basic where it it just rewriting data based on the on variance.
But where I am having issues is I keep forgetting to include a reason when I apply a variance and this then causes an issue further down the line and potentially I have to start again because I did not provide a reason.
SUB A_Rewrite
Application.ScreenUpdating = False
Dim varResponse As Variant
varResponse = MsgBox("Are you sure you want to re-write with carry over ammendment? 'Yes' or 'No'", vbYesNo, "Selection")
If varResponse <> vbYes Then Exit Sub
Sheets("Carry Over Sheet").Select
Columns("E:AI").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
Sheets("Carry Over Sheet").Select
Range("M8:M200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("V8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Carry Over Sheet").Select
Range("S8:S200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("X8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Carry Over Sheet").Select
Range("Y8:Y200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("Z8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Carry Over Sheet").Select
Range("AE8:AE200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("AB8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Carry Over Sheet").Select
Range("AK8:AK200").Select
Selection.Copy
Sheets("Consolidated View").Select
Range("AD8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Consolidated View").Select
Range("W8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",V8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"
Range("Y8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",X8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"
Range("AA8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",Z8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"
Range("AC8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",AB8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"
Range("AE8").Select
ActiveCell.Formula = _
"=IF(M8="""","""",AD8/VLOOKUP(M8,'Data Sheet'!F:I,4,0)/VLOOKUP(M8,'Data Sheet'!F:N,9,0)*1000)"
Range("W8:W" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("Y8:Y" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("AA8:AA" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("AC8:AC" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Range("AE8:AE" & Range("K" & Rows.Count).End(xlUp).Row).FillDown
Application.Goto Range("A1"), True
End Sub