Sparky1983
New Member
- Joined
- Feb 19, 2021
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
Hi
I have the following code working so that it copies the “Template” tab and renames it based on a table in the summary tab
Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Summary")
For Each c In sh2.Range("A3", sh2.Cells(Rows.Count, 1).End(xlUp))
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value: ActiveSheet.Range("G5") = c.Value
Next
End Sub
The "Template" has pre-populated information up to column GE but a lot of this can be deleted each time (e.g. the first tab may need all the info in the first 40 columns but second tab may only need the first 24 columns)
I have therefore included in column B on “Summary” the number of columns required for each tab created but I am unsure on how to include this in the above so that it automatically deletes the excess columns not required and to then adjust the print range to show F2:Lastcolum133.
Any help would be much appreciated.
Thanks!
I have the following code working so that it copies the “Template” tab and renames it based on a table in the summary tab
Private Sub CommandButton1_Click()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Sheets("Template")
Set sh2 = Sheets("Summary")
For Each c In sh2.Range("A3", sh2.Cells(Rows.Count, 1).End(xlUp))
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value: ActiveSheet.Range("G5") = c.Value
Next
End Sub
The "Template" has pre-populated information up to column GE but a lot of this can be deleted each time (e.g. the first tab may need all the info in the first 40 columns but second tab may only need the first 24 columns)
I have therefore included in column B on “Summary” the number of columns required for each tab created but I am unsure on how to include this in the above so that it automatically deletes the excess columns not required and to then adjust the print range to show F2:Lastcolum133.
Any help would be much appreciated.
Thanks!