In 5 Seconds Auto Create Multiple Sheets In Excel - Code With Mark

[@albertkolondarov42]

Just used this creating 1700 tabs and worked perfectly, thank you!

[@xarafat786]

Can you please give me the vba code ?

[@tlee7028]

thank you Mark... so cool and easy to follow... ! it worked very nicely for me.

[@solkionatutorial1004]

Thank you so much Sir, you just made my project very easy. A million thanks for this easy trick. I hope to learn more from your channel

[@HatchosShapes]

I edited the code provided to make many copies of sheets adding the sheet number to a specified cell in each copy.
Hope it helps and thanks mark for helping me out :)
Function create_sheets()
Dim i As Long, LastRow As Long, ws As Worksheet

'Specify Number of Coppies
LastRow = 100

For i = 1 To LastRow

'copy sheet from the page named Template, update the sheet name as necessery
Sheets("Template").Copy After:=Sheets(i)
ActiveSheet.Name = i

'update reference cell in your document to the sheet number, update cell reference as necessary
ActiveSheet.Range("H5").Value = i

Next i

MsgBox "Done creating sheets"

End Function

[@rohitnate1]

Sub AddSheets()
'Updateby Extendoffice
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B2:B60")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub

[@rebeccatong8903]

It works great! Thanks!

[@rendanithomani123]

Thank you Mark. It works like Magic!!!

[@robertreed4337]

You've just made an Old Man Very Happy

[@BayanDawood-v7b]

I'm not sure what I'm doing wrong, but I keep getting a "Run-time error '9': subscript out of range.
I am trying to use this same method, but instead of names, with dates. I have created a new sheet with my dates in a row (named Date). I want the date to change each time adding 1 day to be placed in F1 in my original template (named Template). The code highlights "ActiveSheet.Name = Sheets ("Name"). Cells (i, 1) and gives me a 'debug' option as well as 'end'
This is how I entered the code:
Function create_sheets()
Dim i As Long, LastRow As Long, ws As Worksheet

Sheets("Date").Activate

LastRow = 31

For i = 1 To LastRow

'copy sheet from template
Sheets("Template").Copy After:=Sheets(i)
ActiveSheet.Name = Sheets("Names").Cells(i, 1)

'update dc number
ActiveSheet.Range("f1").Value = ActiveSheet.Name

Next i

MsgBox "Done creating sheets"

End Function

[@cosmoscarey]

Same here!

[@JamesT-dm9gc]

Same here!

[@mervyntracy]

ActiveSheet.Name = Sheets("").Cells(i, 1) ActiveSheet.Name = Sheets("").Cells(i, 1) since that is the name of your sheet.

[@caissa6187]

thaaaaank you! so helpful!!

[@varanasiphotographyservice9639]

Hi Mark, Firstly, Thank You! So much for your kind value addition to the society. I need a favor of your knowledge if possible. I want to create each month individual dates pages like 01 August 2022, 02 August 2022 ......till 31 August 2022. And on each page their is format that includes heading as S.No., Order Id, Tracking Id & Expected Shipping Date. So, what's the quickest trick to do that. Please help.

[@2004elven]

get the answer? if yes please share to me

[@NoorbaizuraIsmail]

Hi. I want to create multiple worksheet with every worksheet has details of quantity of my stock inventory. Can you create code for me. for example C9 is column I want to add the quantity from my data.

[@jasonbaertsch7747]

I have 6 columns of data, with 2322 rows. There are 6 different destinations for these 6 columns of data on my template, N3, C5, K5, S5, C6, N6. Will the code you have provided be able to accommodate this? Great video and explanation! Thanks for your help.

[@mervyntracy]

Very late to the party here. Assuming that your 'Names' equivalent sheet has the details listed in columns a-f, here is the code that you would use for the scenario above:
Function create_sheets()
Dim i As Long, LastRow As Long
Dim wsNames As Worksheet, wsTemplate As Worksheet

' Set references to worksheets
Set wsNames = ThisWorkbook.Worksheets("Names")
Set wsTemplate = ThisWorkbook.Worksheets("Template")

' Find the last row in column A of the "Names" worksheet
LastRow = wsNames.Cells(wsNames.Rows.count, "A").End(xlUp).Row

' Loop through each row in column A of the "Names" worksheet
For i = 1 To LastRow

' Copy sheet from template
wsTemplate.Copy After:=Sheets(i)

' Rename the copied sheet with the value from column A
Sheets(i + 1).Name = wsNames.Cells(i, 1).Value

' Update the value in cell B2 of the copied sheet with the new sheet name
Sheets(i + 1).Range("N3").Value = Sheets(i + 1).Name
Sheets(i + 1).Range("C5").Value = wsNames.Cells(i, 2).Value
Sheets(i + 1).Range("K5").Value = wsNames.Cells(i, 3).Value
Sheets(i + 1).Range("S5").Value = wsNames.Cells(i, 4).Value
Sheets(i + 1).Range("C6").Value = wsNames.Cells(i, 5).Value
Sheets(i + 1).Range("N6").Value = wsNames.Cells(i, 6).Value

Next i

MsgBox "Done creating sheets"
End Function

[@omprakashojha9698]

Great tip. Thank you!

[@sudipbosu8787]

Thanks!

[@drupalguy]

Helpful tip; thanks for sharing

[@ikhan.7]

Will this work in google sheets too? I just want to change dates every day and there are 7-8 names to be copied.

[@Codewithmark]

No it won't but I have create a video for you for google sheets
Check it out: ua-cam.com/video/nPsLA1hP3RY/v-deo.html
Happy Coding :-)

[@RaymondRovero]

is this working in google sheet?

[@MELDepartment]

Great job

[@JewellMichaelRealty]

Say if I wanted to copy the same sheet without any modifications, 50 times, how do I do that? I'm making a "Prospect" book for real estate agents so the info will not change, as the agent will manually input their clients names and other info. Also, how would I put those 50 sheets in a word documents and save as a PDF? Thank you.

[@TheAdinutza09]

Very useful, thank you! But i do have a problem: i need to update 3 columns in the template. Can u help me with that? Thanks alot

[@Codewithmark]

Glad you found it helpful!
Love to help you out... can you email me here ( codewithmark.com/contact ) about it.
Happy Coding

[@lefterislainas]

Hello Adina! Did you manage to find the solution to your problem? I have the same issue and it would be very kind of you if you helped me.

[@tarunnegi4709]

if i need to add one more detail in created copy than what to do, just like you copied late name in another sheet

[@gmanpublications]

That is a great concept, but I am using a form to create a new sheet, fill certain areas on the sheet based on the template, and then add it to a table of contents. Can someone point me in the right direction?

[@SultanHarbi93-fc3gr]

Thank you but i don't know why don't work if the name's is dates for example i want for each sheet name will be as date 01/July/2024.

[@sahare33]

How do we create multiple worksheets along with creating multiple workbooks for a data file?

[@2004elven]

hi , how to make value to date in marco

[@alializadeh8195]

Thanx

[@MeerMustafaAli]

Code is not working if I am selecting the range in middle of data.
Can you please help me with this.

[@johnfrancispederson]

you're awesome

[@MeerMustafaAli]

After running code, I am getting
" Run time error '9'
Subscript out of range"
Please help me with this
error line is
Sheets("Template").Copy After:=Sheets(i)
total code is
Function create_sheets()
Dim i As Long, LastRow As Long, ws As Worksheet

Sheets("Names").Activate

LastRow = 11

For i = 7 To LastRow

'copy sheet from template
Sheets("Template").Copy After:=Sheets(i)
ActiveSheet.Name = Sheets("Names").Cells(i, 1)

'update dc number
ActiveSheet.Range("b3").Value = ActiveSheet.Name

Next i

MsgBox "Done creating sheets"

End Function

[@rohitnate1]

Sub AddSheets()
'Updateby Extendoffice
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B2:B60")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub

[@avenger9mm]

its working but removing the other existing other sheets how to avoid it removing the existing sheets

[@jaymeengandhi8958]

Hi Mark,
Thank You for the video. On line 8 ( ActiveSheet.Sheet1 = Sheets("Name").Cells(i, 1)). Why do you type Name in quotations? I do not see Name anywhere on the 2 sheets. I see that one of the sheet is named "Names".

[@Codewithmark]

Hi Jaymeen,
I am not sure which line 8 you are referring to but here is the whole :
Function create_sheets()
Dim i As Long, LastRow As Long, ws As Worksheet

Sheets("Names").Activate

LastRow = 4

For i = 1 To LastRow

'copy sheet from template
Sheets("Template").Copy After:=Sheets(i)
ActiveSheet.Name = Sheets("Names").Cells(i, 1)

'update dc number
ActiveSheet.Range("b2").Value = ActiveSheet.Name

Next i

MsgBox "Done creating sheets"

End Function
you can also get this from : codewithmark.com/in-5-seconds-auto-create-multiple-sheets-in-excel

[@rohitnate1]

Sub AddSheets()
'Updateby Extendoffice
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B2:B60")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub

[@mervyntracy]

Code to automatically determine how many rows in the Names worksheet:
Function create_sheets()
Dim i As Long, LastRow As Long
Dim wsNames As Worksheet, wsTemplate As Worksheet

' Set references to worksheets
Set wsNames = ThisWorkbook.Worksheets("Names")
Set wsTemplate = ThisWorkbook.Worksheets("Template")

' Find the last row in column A of the "Names" worksheet
LastRow = wsNames.Cells(wsNames.Rows.count, "A").End(xlUp).Row

' Loop through each row in column A of the "Names" worksheet
For i = 1 To LastRow

' Copy sheet from template
wsTemplate.Copy After:=Sheets(i)

' Rename the copied sheet with the value from column A
Sheets(i + 1).Name = wsNames.Cells(i, 1).Value

' Update the value in cell B2 of the copied sheet with the new sheet name
Sheets(i + 1).Range("B2").Value = Sheets(i + 1).Name

Next i

MsgBox "Done creating sheets"
End Function

[@tanveerhussain3881]

Great

[@JohnKay79]

ugh, I only have the home version I guess, cus I don't have the Developer tab, and I searched for Visual Basic and got nothing. bummer

[@amandeepsinghbassi1600]

AFTER CREATING 200 WORKBOOK WITH IDENTICAL CONTENT RECORD
HOW TO RENAME EACH WORKSHEET WITH SPECIFIC NAME OF DEPT

[@Codewithmark]

Watch Part 2 More Tips > ua-cam.com/video/Rz8HNyoMoM4/v-deo.html

[@ABHIJEETSRIVASTAVA-go5tm]

Copy link please for code

[@travelvloggerprosen]

The title says creating multiple sheets however you are showing tabs

[@ashokkumarks]

I like to ask some questions

[@kelimutscheller1960]

No Developer option in Excel for me :(

[@ShadE97]

Add the ribbon from options

[@purohit4672]

Instead of this create one pivot and do it 😂

[@Karab777]

Where is the code?

[@Codewithmark]

Hi Kara,
You can get the code from here: codewithmark.com/in-5-seconds-auto-create-multiple-sheets-in-excel

[@rohitnate1]

Sub AddSheets()
'Updateby Extendoffice
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B2:B60")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub

[@KenSuso-c2b]

@@Codewithmark do you the similar code in google sheets?

[@rohitnate1]

Sub AddSheets()
'Updateby Extendoffice
Dim xRg As Excel.Range
Dim wSh As Excel.Worksheet
Dim wBk As Excel.Workbook
Set wSh = ActiveSheet
Set wBk = ActiveWorkbook
Application.ScreenUpdating = False
For Each xRg In wSh.Range("B2:B60")
With wBk
.Sheets.Add after:=.Sheets(.Sheets.Count)
On Error Resume Next
ActiveSheet.Name = xRg.Value
If Err.Number = 1004 Then
Debug.Print xRg.Value & " already used as a sheet name"
End If
On Error GoTo 0
End With
Next xRg
Application.ScreenUpdating = True
End Sub
Code That worked for me