I am new to
VBA, I need help applying the below VBA code to two specific work sheets
by Region and
by Model. The code simply finds the last column which has the name
total for the year and copies the previous months values into a new column. The code works fine if executed on each worksheet separately.
Sub Insert_New_Col() Dim R As Range, BeforeR As Long 'Find 'Totals' in row 5 of active sheet Set R = Rows(3).Find(what:="Total for the Year", lookat:=xlWhole) 'identify the column to copy (last month) BeforeR = R.Column - 1 If R Is Nothing Then MsgBox ("The word 'Totals' was not found in Row 5 - macro terminated!") Exit Sub ElseIf Not R Is Nothing Then 'copy last month's column Columns(BeforeR).Copy 'insert copied cells before the Totals column Columns(R.Column).Insert Shift:=xlRight Application.CutCopyMode = False End If End Sub