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!

 

[Superstar31]

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!

Can anyone walk me through what his script does? So I can make it my own

I've already renamed PBSCoverSheet to Sheet1

but I'm having trouble figuring out where my name range goes and well pretty much everything!

 

[mattTc17]

Can anyone walk me through what his script does? So I can make it my own

I've already renamed PBSCoverSheet to Sheet1

but I'm having trouble figuring out where my name range goes and well pretty much everything!

Is there a way to attach a file in this forum? I can send you what I did with this macro, having the file might make it make a little more sense.

 

[Superstar31]

Is there a way to attach a file in this forum? I can send you what I did with this macro, having the file might make it make a little more sense.

Hi thanks for the quick response.. I actually figured it out...

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

Set MyRange = Sheets("NameList").Range("A2") <-- Namelist is the tab that contains the names to be created -- A2 is the start (must be more than 1 name (i think)
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
Worksheets("MainData").Cells.Copy ActiveSheet.Range("A1") <-- MainData is data to copy into the newly created tabs (formulas stay)
Next MyCell
End Sub

 

[LAN86]

Hi

used the following:

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

Set MyRange = Sheets("TOTAL").Range("A98")
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
Worksheets("TOTAL").Cells.Copy ActiveSheet.Range("A1")
Next MyCell
End Sub

Which worked perfectly, thanks!

The issue I have now is that I got 4 graphs on the worksheet I want to copy and after running the macro, the copied graphs are still referring to the original sheet ("TOTAL"). Anybody know how to fix this? THANKS!

 

[mattTc17]

I had to use =VALUE() to fix similar problems associated with names of things.

 

[Superstar31]

Hi

used the following:

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

Set MyRange = Sheets("TOTAL").Range("A98")
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
Worksheets("TOTAL").Cells.Copy ActiveSheet.Range("A1")
Next MyCell
End Sub

Which worked perfectly, thanks!

The issue I have now is that I got 4 graphs on the worksheet I want to copy and after running the macro, the copied graphs are still referring to the original sheet ("TOTAL"). Anybody know how to fix this? THANKS!

yea same problem. my workaround was to create a graph macro that looped for the number of sheets that was created

Sub WorkingMacro()
'Make Start worksheet Active
Sheets("Start").Activate
Dim n
Dim V
Worksheets("Start").Range("G2").Select
V = ActiveCell.Value
n = 0
Do Until n = V
Set sh = ActiveSheet

On Error Resume Next
Do While sh.Next.Visible <> xlSheetVisible
If Err <> 0 Then Exit Do
Set sh = sh.Next
Loop
sh.Next.Activate
On Error GoTo 0

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("C8:Q12")

n = n + 1
Loop
End Sub

I had to use =VALUE() to fix similar problems associated with names of things.

is this referring to the graph issue

 

[LAN86]

yea same problem. my workaround was to create a graph macro that looped for the number of sheets that was created

Sub WorkingMacro()
'Make Start worksheet Active
Sheets("Start").Activate
Dim n
Dim V
Worksheets("Start").Range("G2").Select
V = ActiveCell.Value
n = 0
Do Until n = V
Set sh = ActiveSheet

On Error Resume Next
Do While sh.Next.Visible <> xlSheetVisible
If Err <> 0 Then Exit Do
Set sh = sh.Next
Loop
sh.Next.Activate
On Error GoTo 0

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("C8:Q12")

n = n + 1
Loop
End Sub



is this referring to the graph issue

thanks for the help, figured it out:

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

Set MyRange = Sheets("TOTAL").Range("A98")
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
Worksheets("TOTAL").Cells.Copy ActiveSheet.Range("A1")
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Range("B78:N80")
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Range("P78:AB80")
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Range("AD78:AP80")
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Select
ActiveChart.SetSourceData Source:=Range("AR78:BD80")
Next MyCell
End Sub

works perfectly!

 

[mattTc17]

Does anybody know if there is a way to attach files in this forum? That would make understanding how this works much easier for anybody who comes across it. It took me awhile to get it working for my application but if I had an example file it probably would've gone much quicker.

 

[BryanSims]

I used the original macro with some tweaks and its awesome. Thank you guys, I am new to creating macros. I currently have the macro set up to create new sheets based on a range, i also set it up to copy a template from another sheet onto those new sheets.

Now i need to create a macro to populate these new sheets that have the template on them with data from a table. What would be the best way to go about linking said table with these new sheets? Each row in this front table is a new sheet named after the 1 column, i need the data in the rest of the columns to populate cells on the new sheets. For example, cell A1 "Air Conditioner 1" is the name of the newly created sheet, then cell A2 is the first data point, and i want the data from cell A2 to be put into the new sheet (named after A1 "Air conditioner 1") in cell D20, cell A3 in cell D23 and so on. I can deal with the custom linking. But I need a macro that can hunt for the sheet name based on the 1 column, and use that to link the 2 and 3 column to cells on that sheet, and i need to do that for a lot of sheets. Let me know if that makes no sense, feel free to email me or reply with questions, i would also be happy to send my excel workbook to any one willing to help if it needs clarification, thanks in advance!