Help with subscript out of range

[jessier4025]

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

[techgnome]

Ok, so this is VBA(Excel) not VBScript - which is generally used in scripting or ASP Classic... VBA is the VB that's used inside of the Office products and a different animal... I'll ask a mod to move it to the Office forums...

-tg

[penagate]

Thread moved

[westconn1]

Workbooks(WBSource).Sheets(SourceSheet1).Copy Before:=Workbooks(WBTarget).Sheets(Var1)

you need to break this line down to find out which variable is causing the error

i am assuming this is in excel 07?
if the name of your workbooks and sheets are all correct, from the posts in this forum this may have been a common problem with early office 07, fixed with some service pack or other

there are work arounds to avoid the error, but possibly better to get any service packs

your code further down works with sheets, but does not specify which workbook

[jessier4025]

you need to break this line down to find out which variable is causing the error

i am assuming this is in excel 07?
if the name of your workbooks and sheets are all correct, from the posts in this forum this may have been a common problem with early office 07, fixed with some service pack or other

there are work arounds to avoid the error, but possibly better to get any service packs

your code further down works with sheets, but does not specify which workbook

I had someone to help write the code who was far more advanced than i am who was using office 07. My laptop has office 2003 so when i got the code i had to convert my files over to xlsm and xlsx. I couldnt get that to work either so i converted them back and changed the code to reflect the new file extensions.
I am not very familiar with code so am at a loss on how to fix. if i can answer any question that would fix this i would greatly appreciate it. I have a huge spreadsheet with alot of tabs that i want to apply this to and adjust some of the values to make it work with those sheets but once i get the base code i think i can handle that. This is the last piece of code i need to finish this book and its racking my brain!

[westconn1]

instead of using variables to hold the names of workbooks and sheets, use objects, try like

Sub Macro1
'Assign Workbook to objects
set WBSource = workbooks("template.xlsm")  ' change extension to xls
set WBTarget = workbooks("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 wbtarget.Worksheets  ' here should be specified which workbook
'when using a for loop and deleting sheets from within the loop, some inconsistancies may occur
Select Case ws.Name
Case "Table of Contents", "Taxes and Labor"
' do nothing in above worksheet names
Case Else
Var1 = ws.Name
WBSource.Sheets(SourceSheet1).Copy Before:=WBTarget.Sheets(Var1)
Var2 = ActiveSheet.Name  ' do not use activesheet, specify the workbook.sheet to use, better to use a sheet object and possibly better to add a new sheet
set newsht = wbtarget.sheets.add   'sheet object
' you could also set a sheet object for sheets(var1)
'Copy Ranges to Targets
wbtarget.Sheets(Var1).Range(SourceRng1).Copy newsht.Range(TargetRng1)
wbtarget.Sheets(Var1).Range(SourceRng2).Copy newsht.Range(TargetRng2)
wbtarget.Sheets(Var1).Range(SourceRng3).Copy newsht.Range(TargetRng3)
'Remove old sheet and rename new
Application.DisplayAlerts = False
wbtarget.Sheets(Var1).Delete
Application.DisplayAlerts = True
newsht.Name = Var1
End Select
Next ws
End Sub

the code you are using assumes that both workbooks are already open in the same instance of excel, i may be confused as to which workbook holds the specified sheets, change to suit if required