Copy Excel Template

[wjmay1961]

I hoping someone can help, I have a template within the 2003 Excel Workbook, I need some help to duplicate the worksheet template based on the number of days working. I already have a cell calculation in (c2) to capture the results between the start date and end date. Also I would like the duplicated sheets to be renamed to match the dates selected.

 

[sergioMabres]

let us see the sample workbook, I can help you, you should upload the file in Skydrive or Dropbox or any service that does no request registering to download the file, you should describe what the macro has to do in a detailed way

 

[wjmay1961]

Sergio, the file can be found at Download Subcontractor Package.xls from Sendspace.com - send big files the easy way

 

[sergioMabres]

sorry cannot download the file

 

[sergioMabres]

I found the real downlod link, here is the macro to duplicate the template and rename the new sheet

Sub cpytmp()
    Dim sh As Integer
    Dim pn As String
    sh = Sheets.Count
    Sheets("Template").Select
    Sheets("Template").Copy After:=Sheets(sh)
    Sheets(sh + 1).Select
    pn = Sheets(1).Range("C2").Value & "-Page"
    Sheets(sh + 1).Name = pn
End Sub

 

[sergioMabres]

may the number of days is in J8 so use
pn = Sheets(3).Range("J8").Value & "-whatever-u-want-here"

 

[wjmay1961]

Sergio, thank you for a quick response and I do appreciate your efforts. What I was hoping for was to have the code generate "x" number of sheets based on the value in "J8". I would expect 7 sheets to be generated with the date value on each tab.

 

[sergioMabres]

ok here it is

Sub cpytmp()
    Dim sh As Integer
    Dim pn As String
    Dim sn, i As Integer
    If IsNumeric(Sheets(3).Range("J8").Value) Then
        sn = Val(Sheets(3).Range("J8").Value)
    Else
        sn = 1
    End If
    sh = Sheets.Count
    For i = 1 To sn
        Sheets("Template").Select
        Sheets("Template").Copy After:=Sheets(sh)
        sh = sh + 1
        pn = "Day-" & i
        Sheets(sh).Name = pn
    Next i
End Sub

Is this what you are looking for?
Sergio

 

[wjmay1961]

Sergio, you almost have it, the procedure works, but I was hoping the actual day of the week, i.e. Wed, Apr-24-13 would show as the tab name.

 

[sergioMabres]

I guess start date is in M4 but am not sure
Here is the corrected macro

Sub cpytmp()
    Dim sh As Integer
    Dim pn As String
    Dim sn, i As Integer
    Dim st As Date
    ' I guess start date is in M4
    If IsDate(Sheets(3).Range("M4").Value) Then
        st = Sheets(3).Range("M4").Value
    Else
        st = Date
    End If
    ' Number of sheets to add is in J8
    If IsNumeric(Sheets(3).Range("J8").Value) Then
        sn = Val(Sheets(3).Range("J8").Value)
    Else
        sn = 1
    End If
    ' Adds sn sheets
    sh = Sheets.Count
    For i = 1 To sn
        Sheets("Template").Select
        Sheets("Template").Copy After:=Sheets(sh)
        sh = sh + 1
        ' You can change the format to your futures preferences
        pn = Format(st + i - 1, "ddd, mmm-dd-yy")
        Sheets(sh).Name = pn
    Next i
End Sub

If you like it push "Like"