Need macro to create new sheets based on a list, and populate those sheets with existing tables

[ElleTark]

I have a working macro that generates a bunch of new worksheets based on a list in a range of cells. What I need to add to this macro (or perhaps create another macro to accomplish) is a way to populate those newly generated sheets with content (i,e., copy and paste a worksheet into the new, blank ones).

Here is what I have so far:

Sub CreateSheetsFromAList()
    Dim MyCell As Range, MyRange As Range
    
    Set MyRange = Sheets("PBSCoverSheet").Range("B15")
    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

Thank you in advance for any help!

 

[Andrew Poulsom]

Welcome to MrExcel.

What's the name of the worksheet that you want to copy? If it's Sheet1 try:

Worksheets("Sheet1").Cells.Copy ActiveSheet.Range("A1")

 

[ElleTark]

I added your code at the end of my existing code, and it worked, but it only copied the template to the last worksheet that was created (the 10th out of 10). Should I put it in a different place within my code?

Also, is there a way for macros to run automatically without the user having to click something? If not, is there a way to create a button on the first worksheet that will run the macro after the user has created the list that will generate the new worksheets?

Thank you so much, you are providing me with a great deal of help.

 

[Andrew Poulsom]

The line I posted needs to go inside your loop, before Next MyCell.

You can add a button from the Forms Toolbar or a shape from the Drawing Toolbar to your worksheet and assign your macro to it (right click Assign Macro).

 

[ElleTark]

Figured it out! Thanks again!

 

[ElleTark]

Have an added wrinkle to this problem: I would like a cell on each newly generated sheet to contain the name of that sheet. Is there a reference that I can add to the macro that will insert the sheetname into a cell on the new sheet?

 

[Andrew Poulsom]

Example:

With ActiveSheet
    .Range("A1").Value = .Name
End With

You can also use a formula to return the sheet name:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1),1)+1,255)

 

[mattTc17]

I used this macros and it works beautifully! Any way to copy the print area settings from the template worksheet as well? We have to print hard copies of each worksheet for employees to sign and right now it's printing documents that are two pages wide *SMH*.

 

[Andrew Poulsom]

Insert a blank worksheet with the print settings that you want. Then instead of adding a worksheet make a copy of the blank worksheet.

 

[mattTc17]

Insert a blank worksheet with the print settings that you want. Then instead of adding a worksheet make a copy of the blank worksheet.

Sorry, I assumed it was a print area problem but I double-checked and actually what is happening is that the page setup is defaulting back to (Scaling>Adjust to: "100" % normal size) even though the worksheet I'm copying in the macro is set to (Scaling>Fit to: "1" page(s) wide by "1000" tall). It's odd that this doesn't propagate, but I found the below link to apply the same settings to multiple tabs so it's no big deal:

http://support.microsoft.com/kb/213257