VBA Code

NGB82KS

Board Regular
Joined
Nov 7, 2019
Messages
82
Office Version
  1. 2016
I have the following code I'm trying to make into my template EXCEL workbook. The Code should be in 2 parts, and the first part works as intended.
Part 1. Using the "Template" sheet, copy the template tab and rename it for each item under the "TABIFY" sheet until it creates all the sheets with their names from the "TABIFY" sheet.

VBA Code:
Sub NewSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Set ws = Sheets("TEMPLATE")
Set sh = Sheets("TABIFY")
Application.ScreenUpdating = 0
 For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
 Sheets("TEMPLATE").Copy After:=sh
 ActiveSheet.Name = sh.Range("A" & i).Value
 Next i
End Sub

Part 2. I need the code to finish creating all the tabs, then DELETE the "TABIFY" & "TEMPLATE" sheets and prompt the user to "save as" a regular workbook without macros. (Our company blocks the email and share of macro enabled files) so I have the delete verbiage, but not sure how to write that in after the creation of the tabs or to prompt for save as non macro enabled workbook.

Sheets("TABIFY").Delete
Sheets("TEMPLATE").Delete


Thanks to anyone who can help!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
How about
VBA Code:
Sub NewSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim Fname As String

Set ws = Sheets("TEMPLATE")
Set sh = Sheets("TABIFY")
Application.ScreenUpdating = 0
 For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
 Sheets("TEMPLATE").Copy After:=sh
 ActiveSheet.Name = sh.Range("A" & i).Value
 Next i
 Application.DisplayAlerts = False
 Sheets(Array("Template", "Tabify")).Delete
 Application.DisplayAlerts = True
Fname = Application.GetSaveAsFilename(, "Excel Files (*.xlsx), *.xlsx")
ActiveWorkbook.SaveAs Fname, 51
End Sub
 
Upvote 0
How about
VBA Code:
Sub NewSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim Fname As String

Set ws = Sheets("TEMPLATE")
Set sh = Sheets("TABIFY")
Application.ScreenUpdating = 0
 For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
 Sheets("TEMPLATE").Copy After:=sh
 ActiveSheet.Name = sh.Range("A" & i).Value
 Next i
 Application.DisplayAlerts = False
 Sheets(Array("Template", "Tabify")).Delete
 Application.DisplayAlerts = True
Fname = Application.GetSaveAsFilename(, "Excel Files (*.xlsx), *.xlsx")
ActiveWorkbook.SaveAs Fname, 51
End Sub
When i put this in, it only gets the 1st 2 rows that are alpha characters. I have 97 Rows starting with just alpha characters then number characters with spaces....
 
Upvote 0
I did not change your code, just added the bits you requested to the end of it. So not sure what you mean, as your originally stated that your code worked.
 
Upvote 0
I did not change your code, just added the bits you requested to the end of it. So not sure what you mean, as your originally stated that your code worked.
If I comment out the "Sheets(Array("Template", "Tabify")).Delete", it creates all the worksheets as necessary and prompts for the save as. If I don't comment out the delete sheets, it only creates a handful of the worksheets and then prompts for the save as, it's as though it doesn't read all the rows from TABIFY sheet before going into the delete portion.
 
Upvote 0
I see no reason why that would happen & doesn't happen for me if I run the code.
However I've realised that turning on the display is too soon, it should really be at the end
VBA Code:
Sub NewSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim Fname As String

Set ws = Sheets("TEMPLATE")
Set sh = Sheets("TABIFY")
Application.ScreenUpdating = 0
 For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
 Sheets("TEMPLATE").Copy After:=sh
 ActiveSheet.Name = sh.Range("A" & i).Value
 Next i
 Application.DisplayAlerts = False
 Sheets(Array("Template", "Tabify")).Delete
 Fname = Application.GetSaveAsFilename(, "Excel Files (*.xlsx), *.xlsx")
 ActiveWorkbook.SaveAs Fname, 51
 Application.DisplayAlerts = True
End Sub
 
Upvote 0
Solution
I see no reason why that would happen & doesn't happen for me if I run the code.
However I've realised that turning on the display is too soon, it should really be at the end
VBA Code:
Sub NewSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim Fname As String

Set ws = Sheets("TEMPLATE")
Set sh = Sheets("TABIFY")
Application.ScreenUpdating = 0
 For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
 Sheets("TEMPLATE").Copy After:=sh
 ActiveSheet.Name = sh.Range("A" & i).Value
 Next i
 Application.DisplayAlerts = False
 Sheets(Array("Template", "Tabify")).Delete
 Fname = Application.GetSaveAsFilename(, "Excel Files (*.xlsx), *.xlsx")
 ActiveWorkbook.SaveAs Fname, 51
 Application.DisplayAlerts = True
End Sub
1639163382314.png
In my last test, it didnt create a tab for wiclass or 33-38class worksheets. Is it possible that i need to create a table with name and use that table instead of just saying everything on tabify in column a?
 
Upvote 0
It never has created a sheet for the first value, as the loop starts in A2. Is the Tabify sheet the active sheet when you run the code?
 
Upvote 0
It never has created a sheet for the first value, as the loop starts in A2. Is the Tabify sheet the active sheet when you run the code?
Yeah I wasn't on the TABIFY worksheet, once I switched to it it was fine.... Thank you!
 
Upvote 0
Ok, if you use
VBA Code:
 For i = 2 To sh.Range("A" & Rows.Count).End(xlUp).Row
then it won't matter what sheet is active when you run the code.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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
Back
Top