Report

Need to create multiple tabs based on data in table [Solved]

Ask a question Kylana - Last answered on Dec 29, 2016 at 03:36 AM by vcoolio
Good Afternoon,

I'm trying to find a way to automate a few things to save some time:

-Use the data in an input tab to create multiple tabs/documents with a saved template
-Then use data in that same tab to fill out a few details

The data table is a list of units in a building with bedroom/bathroom information and occupancy
The template I have is for inspections, each unit needs to be inspected, the forms are then printed out so it doesn't need to stay in excel if that's easier. I just don't know where to start.

I'm figuring I'll need to use a macro but that's new for me, I don't know where to start but once I do I should be able to run with it.

Thanks!
See more 
Helpful
+0
moins plus
Hello Kylana,

Could you show us an example of how your work book is set out please showing inputs and expected results and especially the set out of your template.

Upload a sample of your work book (be careful with any sensitive data) to a free file sharing site such as DropBox, ge.tt or SpeedyShare then post the link to your file back here.

We should then be able to work through it with you to a satisfactory conclusion.

Cheerio,
vcoolio.
KylanaG 4Posts Tuesday December 27, 2016Registration date December 28, 2016 Last seen - Dec 27, 2016 at 06:55 PM
Thanks! I can't save the workbook but I included a couple images with data removed. Does that help?

The "Project" on the input is actually the building. I'm looking to copy the Building Name, Unit Number, time in unit & bed/bath into this template. Some projects the table is only maybe 20-40 units but sometimes it's a couple hundred. I'm not sure having the form in excel would work best but my end goal is to be able to print out a page for each unit in each building for the inspections.
Reply
Leave a comment
Helpful
+0
moins plus
Hello Kylana,

I don't see any images in your last post.

Create a copy of your work book, use dummy data (just a few rows will be fine) and then upload it as per my post #1.

Cheerio,
vcoolio.
KylanaG 4Posts Tuesday December 27, 2016Registration date December 28, 2016 Last seen - Dec 28, 2016 at 10:34 AM
Here are the links to the images, the spreadsheet is a beast already and a lot of the hidden tabs I can't share.

Part of the input page
https://www.dropbox.com/s/2m7q0sui3jjhxub/Input%20Page.PNG?dl=0

Part of the template which shows where things need to go
https://www.dropbox.com/s/6k1kocszpodiwlw/Template.PNG?dl=0
Reply
KylanaG 4Posts Tuesday December 27, 2016Registration date December 28, 2016 Last seen - Dec 28, 2016 at 06:35 PM
Just in case somebody wants what was used this is what I did

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

Set MyRange = Sheets("Project Data Input").Range("G18")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub
Reply
Leave a comment
Helpful
+0
moins plus
Hello Kylana,

Its good to see that you have been working on this yourself whilst waiting for a reply. Well done!
Thanks for sharing your resolution also.

Be careful with the loop type code in your last post as, although it works fine, there is no check in the code for duplicate names and Excel may throw up an error message warning you that it won't allow sheets to have the same name. However, if you delete the names in your list as you go, then there shouldn't be any issues.

Again, well done!

Cheerio,
vcoolio.

P.S.: The following thread may be of interest to you:-

http://ccm.net/forum/affich-910283-a-macro-to-create-new-copy-and-name-worksheets-based-on-a-list#top
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!