You are here:

Excel/VBA Conditional statement help

Advertisement


Question
Hi Tom, I hope you can help with this... It seems so simple but I'm really struggling.

I'm using Excel 2007 and I need a way of creating a number of new sheets, with names from a list, and then copying a 'template' sheet with all my formulas and formatting etc into each of these.

I am struggling as I need to be able to update the list that the sheets are created from and re-run the macro to create them as additional new sheets and copy the template, but not overwrite the existing sheets!

Here is as far as I have got so far... I'm very new to VBA in Excel! I have 9 sheets with data etc hence startig to copy from sheet 10. I have tried adding an If function to only paste the template into a blank sheet but it always fails (almost definitely my own errors!)

Sub NEW_SHEETS()
   Application.ScreenUpdating = False
   Call CREATE_SHEETS
   Call POPULATE_SHEETS
   Application.ScreenUpdating = True
End Sub

Private Sub CREATE_SHEETS()
   Dim MyCell As Range, MyRange As Range
   Set MyRange = Sheets("NOTES").Range("B2")
   Set MyRange = Range(MyRange, MyRange.End(xlDown))
   For Each MyCell In MyRange
       Sheets.Add After:=Sheets(Sheets.Count)
       Sheets(Sheets.Count).Name = MyCell.Value
   Next MyCell
End Sub

Private Sub POPULATE_SHEETS()
   Sheets("TEMPLATE").Select
   Range("A1:AX74").Select
   Selection.Copy
Dim i As Integer
For i = 10 To Sheets.Count
   Sheets(i).Select
   Range("A1:AX74").Select
   ActiveSheet.Paste
Next i
End Sub

I hope that all makes sense,

Thanks!

Matt

Answer
Matt,

as I understand it, you have two related problems.  You want to add any non-existent sheets that are in your list in Sheet NOTES and if you create a sheet, then copy the template.  I would do this all in one routine

Sub NEW_SHEETS()
  Application.ScreenUpdating = False
  Call CREATE_SHEETS
  Application.ScreenUpdating = True
End Sub


Private Sub CREATE_SHEETS()
  Dim MyCell As Range, MyRange As Range
  Dim sh1 As Worksheet
  With Worksheets("NOTES")
  Set MyRange = .Range("B2")
  Set MyRange = .Range(MyRange, MyRange.End(xlDown))
  End With
  For Each MyCell In MyRange
      Set sh1 = Nothing
      ' check if this sheet already exists
      On Error Resume Next
       Set sh1 = Worksheets(MyCell.Value)
      On Error GoTo 0
      If sh1 Is Nothing Then
        ' now we know to create a sheet for this name/cell
        Worksheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = MyCell.Value
        Set sh1 = ActiveSheet
        Worksheets("Template").Range("A1:AX74").Copy sh1.Range("A1:AX74")
      End If
  Next MyCell
End Sub

I tested this and it worked as I expected/as I understand your requirement.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.