VSB error autogenerate worksheet from list

Closed
shayla03 Posts 1 Registration date Wednesday March 2, 2016 Status Member Last seen March 2, 2016 - Mar 2, 2016 at 05:40 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Mar 3, 2016 at 01:42 AM
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 responses

Blocked Profile
Mar 2, 2016 at 06:19 PM
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!
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Mar 3, 2016 at 01:42 AM
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.
0