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

Поділитися
Вставка
  • Опубліковано 29 жов 2024

КОМЕНТАРІ • 62

  • @albertkolondarov42
    @albertkolondarov42 2 роки тому +1

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

    • @xarafat786
      @xarafat786 Рік тому

      Can you please give me the vba code ?

  • @tlee7028
    @tlee7028 9 місяців тому +1

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

  • @solkionatutorial1004
    @solkionatutorial1004 2 роки тому +1

    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
    @HatchosShapes 2 роки тому +1

    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
      @rohitnate1 2 роки тому

      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
    @rebeccatong8903 Місяць тому

    It works great! Thanks!

  • @rendanithomani123
    @rendanithomani123 2 роки тому

    Thank you Mark. It works like Magic!!!

  • @robertreed4337
    @robertreed4337 2 роки тому

    You've just made an Old Man Very Happy

  • @BayanDawood-v7b
    @BayanDawood-v7b 11 місяців тому +3

    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
      @cosmoscarey 10 місяців тому

      Same here!

    • @JamesT-dm9gc
      @JamesT-dm9gc 9 місяців тому

      Same here!

    • @mervyntracy
      @mervyntracy 8 місяців тому

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

  • @caissa6187
    @caissa6187 2 роки тому

    thaaaaank you! so helpful!!

  • @varanasiphotographyservice9639
    @varanasiphotographyservice9639 2 роки тому +2

    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
      @2004elven Рік тому +1

      get the answer? if yes please share to me

  • @NoorbaizuraIsmail
    @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
    @jasonbaertsch7747 2 роки тому +2

    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
      @mervyntracy 8 місяців тому

      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
    @omprakashojha9698 3 роки тому

    Great tip. Thank you!

  • @sudipbosu8787
    @sudipbosu8787 Рік тому

    Thanks!

  • @drupalguy
    @drupalguy 3 роки тому

    Helpful tip; thanks for sharing

  • @ikhan.7
    @ikhan.7 3 роки тому +1

    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
      @Codewithmark  3 роки тому +1

      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
    @RaymondRovero Місяць тому

    is this working in google sheet?

  • @MELDepartment
    @MELDepartment Рік тому

    Great job

  • @JewellMichaelRealty
    @JewellMichaelRealty 2 роки тому

    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
    @TheAdinutza09 2 роки тому +1

    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
      @Codewithmark  2 роки тому

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

    • @lefterislainas
      @lefterislainas 2 роки тому

      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
    @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
    @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
    @SultanHarbi93-fc3gr 3 місяці тому

    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
    @sahare33 2 роки тому

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

  • @2004elven
    @2004elven Рік тому

    hi , how to make value to date in marco

  • @alializadeh8195
    @alializadeh8195 2 роки тому

    Thanx

  • @MeerMustafaAli
    @MeerMustafaAli 2 роки тому

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

  • @johnfrancispederson
    @johnfrancispederson 2 роки тому

    you're awesome

  • @MeerMustafaAli
    @MeerMustafaAli 2 роки тому

    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
      @rohitnate1 2 роки тому

      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
    @avenger9mm 4 місяці тому

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

  • @jaymeengandhi8958
    @jaymeengandhi8958 2 роки тому

    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
      @Codewithmark  2 роки тому

      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
      @rohitnate1 2 роки тому

      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
    @mervyntracy 8 місяців тому

    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
    @tanveerhussain3881 3 роки тому

    Great

  • @JohnKay79
    @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
    @amandeepsinghbassi1600 Рік тому

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

  • @Codewithmark
    @Codewithmark  Рік тому

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

  • @ABHIJEETSRIVASTAVA-go5tm
    @ABHIJEETSRIVASTAVA-go5tm 11 місяців тому

    Copy link please for code

  • @travelvloggerprosen
    @travelvloggerprosen 2 роки тому

    The title says creating multiple sheets however you are showing tabs

  • @ashokkumarks
    @ashokkumarks Рік тому

    I like to ask some questions

  • @kelimutscheller1960
    @kelimutscheller1960 8 місяців тому

    No Developer option in Excel for me :(

    • @ShadE97
      @ShadE97 5 місяців тому

      Add the ribbon from options

  • @purohit4672
    @purohit4672 Місяць тому

    Instead of this create one pivot and do it 😂

  • @Karab777
    @Karab777 3 роки тому +1

    Where is the code?

    • @Codewithmark
      @Codewithmark  3 роки тому +1

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

    • @rohitnate1
      @rohitnate1 2 роки тому

      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
      @KenSuso-c2b Місяць тому

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

  • @rohitnate1
    @rohitnate1 2 роки тому

    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