Subscript out of range

i have fixed the infraction. Can anyone offer any assistance? From what i have been told, the person who helped me with this did it in office 07 and i currently have office 03. This is the last part of code that i have to figure out to complete the workbook i have been working on for weeks.

Thanks

Where is Your code failing ie which line code is highlighted when the code bombs?

I get an error on this line

Windows("plans.xls").Activate
I checked the file names and verified no leading or trailing characters in all sheets and file names.
When i hit F8 and step down on the highlighted line i hover over and get =empty until i step past each line.

thanks

here are the source files if it helps.

same error. Unfortunately this code has stumped a few people for why it wont work.
Its still errors at same spot.
Thanks

Originally Posted by OnErrorGoto0

Or

Please Login or Register  to view this content.

i replaced the code with this and now its bombing at this line

Set WBTarget = Workbooks("plans.xls")

for some reason it always has something to do with the target file. I have verified names over and over and its there but it wont work.

Originally Posted by OnErrorGoto0

If that fails, then you do not have that workbook open in the same instance of Excel.

you are correct. I forgot to reopen that sheet before i replaced the code. now i can get alot further down in the code to this and it stops.

WBSource.Sheets(SourceSheet1).Copy Before:=WBTarget.Sheets(Var1)
Var2 = ActiveSheet.Name

when i highlight the bad line at this part of the line the error is:
WBTarget.Sheets(var1)= <object variable or with block variable is not set>

Thats the part i wanted the macro to do for me. The goal of this macro is to take the sheet from template.xls and replace each sheet in plans.xls and copy the data ranges in each old sheet and paste it into the new sheet that is replacing it then rename the new sheet the same as the old one and delete the old sheet. how should i handle that variable? I am not very knowledgeable on code.

Originally Posted by OnErrorGoto0

My mistake, you have actually assigned it a value. In fact you could just use

Please Login or Register  to view this content.

I believe its almost done!! The code doesnt error out anymore but i am not sure its doing anything. It flashes like its doing something but i dont see any change to the sheets in the target book. To test this i went back to template.xls and changed cell a1 since it should stay intact throughout the macro run and that change should appear in the result of replace the old sheets in the target file. At the moment it is not updating the sheets that i can tell.
Suggestions?

Originally Posted by OnErrorGoto0

Is this any better

Please Login or Register  to view this content.

that gets the sheet from template.xls copied into plans.xls but then subscript error at line
WBSource.Sheets(SourceSheet1).Copy Before:=ws
when i hit f8 to step through the code it gets to "table of contents" then jumps down to end select then back to "table of contents" then to WBsource and errors.

Originally Posted by OnErrorGoto0

There is a space on the end of the sheet name in your template file - i.e. it is "2012 DRH Bid Template " rather than "2012 DRH Bid Template"

Wow its doing something strange. I opened the files back and template.xls sheet name 2012 DRH Bid Template had been renamed one of the books from plans.xls
and the data ranges had been copied into it. Then the 2012 DRH Template sheet was copied into Plans.xls
Very strange

which lines do i modify? i modified strTemplatePath= WBTarget.Path & "\" & "template.xls"
to

strTemplatePath = WBTarget.Path & "\" & "c:\documents and settings\rstevens1\desktop\template.xls"

anywhere else?

Originally Posted by OnErrorGoto0

That should just be

Please Login or Register  to view this content.

no other change required

I believe that finallly didnt it!! One last thing.. In my books i have links that i receive an alert to update when i open them. I went to edit links and turned off the alert and told it to automatically update but i still get the message as the macro is working.
If i can fix that all will be done!!

Originally Posted by OnErrorGoto0

Please test

Please Login or Register  to view this content.

Thank you so much! That was the final piece to my huge puzzle i have been putting together for quite a while. I really appreciate your help!!!
Awesome!