create a copies of template worksheet based on a template

October 14, 2012 at 23:39:35
Specs: Windows XP
Hi there, I want to create copies of a "template" worksheet. The number of times it needs to copy is based on the number of location which can be derived by counting values from cell A7 onwards in the Index sheet.

What I want to do is similar to the solution found here:

However I do not need the TOC page and I have modified the code to suit what I want.
Sub createlocsheet()

Dim lastRw As Long
Dim nxtName As Long
Dim lastShe As Long

With Sheets("Index")
lastRw = .Range("A7" & Rows.Count).End(x1Up).Row
For nxtName = 7 To lastRw
lastSht = Sheets.Count
Sheets("Loc Template").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = Sheets("Index") .Range("B" & "nxtName")

End With
End Sub

The problem is when I run it I receive "Run-time error 1004". in the line starting with "lastRw = ..."
I am not sure how to debug.

Thanks in advance.

See More: create a copies of template worksheet based on a template

Report •

October 15, 2012 at 05:06:36
First, before posting any more code in this forum, please click on the blue line at the end of this post and read the instructions found via that link.

Second, the reason you are getting that error is because you are referencing a row that doesn't exist in the spreadsheet. Let's say there are 65,535 rows in a spreadsheet. Range("A7" & Rows.Count) will convert to Range("A765,535") which would be illegal.

The correct syntax is Range("A" & Rows.Count) which will refer to the last row in any spreadsheet, whether it's Excel 2003, 2007 or 2010.

Finally, if you want to learn how to debug VBA code, start here:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

October 15, 2012 at 16:23:41
Thanks for getting back and sorry about not pasting the code properly. There was a typo in the code I think.

Report •

Related Solutions

Ask Question