Home Excel VBA: Generate N Number of Sheets Based on Cel Value
Reply: 1

Excel VBA: Generate N Number of Sheets Based on Cel Value

yjgzepeda
1#
yjgzepeda Published in 2017-12-07 20:00:06Z

I want to create a macro that will generate N number of template sheets based on the value of a cel. For example, User inputs 4 into this particular cell and it subsequently generates 4 new sheets in the workbook of this template.

I've searched all through Stack overflow for a question that matches mine but none do. The closest I found was this and although the inital headline question asks generally the same question, when going into detail the user who asked this changes their question to"insert number of cells based on a cell value". Still I used this as a starting point.

Sub CreateSheets()
  Dim facilitiesNum As Integer

   facilitiesNum = Range("B2").Value

  sheetsNeeded = facilitiesNum
  With ThisWorkbook.Sheets

  For i = sheetsNeeded To Master.Range("B2").Value2
  .Item("TemplateSheet").Copy After:=.Item(.Count)
  .Item(.Count).Name = sheetsNeeded
  Next
  End With
End Sub

I am new to VBA so I could be very off syntax-wise but in pseudocode my goal is

      numberOfTemplates = cell value
      numSheetsNeeded = numberOfTemplates

    For i = numSheetsNeeded To NumOfTemp:
    create sheets using numSheetsNeeded as reference for how many need to be 
    generated

How do I go about doing this?

Sphinx
2#
Sphinx Reply to 2017-12-07 20:35:11Z

If you just want to add new sheets this should be enough

Sub CreateSheets()

   Dim facilitiesNum As Long

   facilitiesNum = Range("B2").Value

   With ThisWorkbook.Sheets
      For i = 1 To facilitiesNum
          .Item("TemplateSheet").Copy After:=.Item(.Count)
          .Item(.Count).Name = i
      Next i
   End With
end sub 
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305929 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO