VSB error autogenerate worksheet from list

[Closed]
Report
Posts
1
Registration date
Wednesday March 2, 2016
Status
Member
Last seen
March 2, 2016
-
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
-
Hello,

So I have an excel that has mutiple worksheets
"2016 Census"
"Tracker Template"

What I did was use a code to automatically pull data from the census onto a template and autocreate new worksheet that follow that template.

So I have the following code:

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("2016 Census").Range("A7")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets("Tracker Template").Copy After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

It works perfectly for the first time it is run. I have my first worksheet "2016 Census" and then I have a templated worksheet that a new worksheet is created from the list on 2016 Census.

My issue is that the "2016 Census" list gets updated regularly, so when I run the code after its already been run and all the worksheet have already been populated (for example, I add a name to the Census list and the code was ran previously so the other 30+ worksheets have already been populated), I can't rerun the code without a run-time error '1004'. It states "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by visual basic.

Some how I need the code to identify to only add worksheets that have not already been identified.

(P.S. I am completely naive about this coding stuff, I got the code from another message board and pretty much copy and pasted and individualized the sheet names).

Appreciate any help.

2 replies


Your problem is in this statement:
Sheets(Sheets.Count)

You need a logic tree for checking if the file exists.
Check to see if the same file is there, if so, rename the file to the file name, and the date-time. this is a very ugly solution, and really will need some houskeeping, or your disk will bloat out! Also, you must remember what the last file name was. I would recommend writing that back to a cell on your sheet! Then who ever saves it, can see what it is, and where it is. You can also map to a network drive so everyone saves to the same place. They will also know where to get it.

I would suggest looking at office 365!
Posts
1318
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 20, 2021
238
Hello Shayla03,

What ac3mark says is fundamentally true but have a look at the following link to a test work book I did for another Poster about a year ago:-

https://www.dropbox.com/s/9wz94jjsptvf061/Create%20and%20Name%20New%20Sheets%282%29.xlsm?dl=0

It creates new sheets without the error problem and is a little tidier than what you already have.

You may be able to adapt it to your work book (but please test it in a copy of your work book first).

The code is in Module 1.

I hope that this helps.

Cheerio,
vcoolio.