Monday 11 June 2012

Populating a workbook with sheets from a template

A simple little problem, populating a workbook with multiple sheets based on a template sheet, naming them appropriately and filling in some basic details:

Sub PopulateWB()
 
    Dim i As Integer
    Dim tmp As Worksheet, lstws As Worksheet
    Set tmp = Sheets("Template")
    Set lstws = Sheets("Sheet1")
 
    Application.ScreenUpdating = False
 
    '--> Set Number of Bundles Below
    '--> Create worksheets for each bundle and complete basic details
    For i = 2 To x
        tmp.Copy Before:=Sheets("Temporary PSD Report")
        ActiveSheet.Name = lstws.Cells(i, 1)
        ActiveSheet.Cells(1, 2) = lstws.Cells(i, 1)
        ActiveSheet.Cells(2, 2) = lstws.Cells(i, 2)
    Next
 
    Application.DisplayAlerts = False
    lstws.Delete
    Application.DisplayAlerts = True
 
    MsgBox ("Now Save This Workbook for the appropriate month and delete the populate code")
 
    Application.ScreenUpdating = True
 
End Sub