VBA - multiple wshts based on a template

[ltb81]

I have recently decided to dive into the world of VBA and have found it to be quite useful. However, I am having some problems coming up with the appropriate code for creating a template.

I have a spreadsheet with an "Input" tab and a "Template" tab. The "Template" tab has information in "G4:G85". The "Input" tab list each additional tab to be created based on the template tab as well as how many times the information in "G4:G85" on the "template" tab needs to be copied. So, some tabs need to have "G4:G85" copied over to "G4:H85" while others may need to have this info copied over to "G4:N85".

For example, if the Inputs tab has the following in column B:

1
2
3

And the following in column C:
4
2
3

I need to have 3 new tabs created (1, 2, 3) with the new "1" tab repeating the template info in "G4:J85" ; the new "2" tab repeating the template info in "G4:H85; and the new "3" tab repeating the template info in "G4:I85"

I created the following code which creates only 1 tab. I need help figuring out how to loop this so it creates the tabs as described above.

Any pointers would be appriciated!

-loren


Sub Create_Tabs()

'Copy_Range refers to G4:G85 listed on the inputs tab
Dim Copy_Range
Copy_Range = Sheets("Input").Range("f3")

'Paste_Range refers to G4:J85 listed on the inputs tab
Dim Paste_Range
Paste_Range = Sheets("Input").Range("G3")

For x = 1 To Sheets("Input").Range("b23")

'Copies the Template
ActiveWorkbook.Sheets("Template").Copy _
Before:=ActiveWorkbook.Sheets("Template")

'Renames the copied Template (2)
Sheets("Template (2)").Name = x

'Copies the info into the next column(s)
Sheets("Template").Range(Copy_Range).Copy
ActiveSheet.Paste Destination:=Sheets(x).Range(Paste_Range)

'Moves the new worksheet to the end of the workbook
Sheets(1).Move After:=Sheets(Sheets.Count)

Next

End Sub

 

[acw]

Hi

Try this.

Sub aaa()
  For Each ce In Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)
    ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
    ActiveSheet.Name = ce.Value
    Range("G4:G85").Resize(82, ce.Offset(0, 1)).Value = Sheets("sheet3").Range("G4:G85").Value
  Next ce
End Sub

It assumes that the source data is in sheet3 starting in B1.


HTH

Tony

 

[ltb81]

Thanks for the code. I am a bit confused by what replaces "ce".

 

[acw]

Hi

Nothing. ce is a variable that is used to loop through the entries in column B.


Tony