Good afternoon,
I am having a subscript out of range error with this macro in excel.
I had some help with getting this going so i hope the error is something simple.
Basically i have two workbooks "template.xlsm" and "plans.xlsx"
I play around with formulas and formatting in template.xlsm and over time i replace all of the worksheets in plans.xlsx with the worksheet from template.xlsm
I copy a set of ranges from the old workbooks in "plans.xlsx" and copy them into the updated template that copied into the workbook from "template.xlsm" for each sheet i am replacing. Then i rename each new worksheet the same name as the old sheet and then delete the old sheet. This has become such a cumbersome process because i have so many sheets now that i need replaced each time. See macro below and please let me know what i am missing. This code was written by someone else since i am not up to speed on macros.
I had a couple of lines that were causing the error that i will highlight in red.

Thanks for any help. It will be greatly appreciated!


Sub Macro1
'Assign Workbook Variables
WBSource = "template.xlsm"
WBTarget = "plans.xlsx"

'Assign Source and Target Range Variables
SourceRng1 = "A4:A86"
SourceRng2 = "C4:C86"
SourceRng3 = "B11"
TargetRng1 = "A4"
TargetRng2 = "C4"
TargetRng3 = "B1"

'Assign Source and Target WorkSheet Name Variables
SourceSheet1 = "2012 DRH Bid Template"



For Each ws In Worksheets
Select Case ws.Name
Case "Table of Contents", "Taxes and Labor"
' do nothing in above worksheet names
Case Else
Var1 = ws.Name
Workbooks(WBSource).Sheets(SourceSheet1).Copy Before:=Workbooks(WBTarget).Sheets(Var1)
Var2 = ActiveSheet.Name
'Copy Ranges to Targets
Sheets(Var1).Range(SourceRng1).Copy Sheets(Var2).Range(TargetRng1)
Sheets(Var1).Range(SourceRng2).Copy Sheets(Var2).Range(TargetRng2)
Sheets(Var1).Range(SourceRng3).Copy Sheets(Var2).Range(TargetRng3)
'Remove old sheet and rename new
Application.DisplayAlerts = False
Sheets(Var1).Delete
Application.DisplayAlerts = True
ActiveSheet.Name = Var1
End Select
Next ws
End Sub