VBA - multiple wshts based on a template

ltb81

New Member
Joined
Oct 17, 2006
Messages
2
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Try this.

Code:
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
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

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


Tony
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,542
Latest member
shablagoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top