Copy worksheet and rename based on cell range

**jpruffle** · 08-06-2011, 03:29 PM

Hi,

I have created a master sheet that I want to replicate. I have a range say A1:A15 with names (test values) of Aug 11, Sep 11 etc.

Instead of creating forms for the next few years manually. I want to create them copying the master and then rename based on the cell value, then cycle through.

I have some starting code but would really appreciate some guidance:

Sub Macro1()
    'select your template/master sheet
    Sheets("Master").Select
    
    'copy the sheet and move the new sheet to the end
    Sheets("Master").Copy After:=Sheets(Sheets.Count)
    
    'select the new sheet and rename it with today's date
    Sheets("Master (2)").Select
    Sheets("Master (2)").Name = Format(Now(), "mmm-yy")
    
    'go back to the template/master
    Sheets("Master").Select

End Sub

This code just uses the current date. I would like to use a cell range.

Thank you,

JP

**watersev** · 08-06-2011, 03:59 PM

hi, jpruffle, please check attachment, run code "test" hope this helps.

To make actions over objects in VB it's not required to select them.

**jpruffle** · 08-06-2011, 04:15 PM

Thank you that works a treat. Will test it with my work later.

**JBeaucaire** · 08-06-2011, 04:19 PM

A slight update on watersev's macro, one would assume there is a template sheet to copy, and also that you would like to list any number of items in column A of your "List" sheet.

Sub CopyTemplate()
Dim wsList As Worksheet
Dim wsTemplate As Worksheet
Dim cell As Range

Set wsList = Sheets("Master")
Set wsTemplate = Sheets("Template")
Application.ScreenUpdating = False

For Each cell In wsList.Range("A:A").SpecialCells(xlConstants)
    wsList.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = cell
Next

Application.ScreenUpdating = True
End Sub

**jpruffle** · 08-07-2011, 03:28 PM

Thank you, both methods work great.

In addition to creating new sheets named as per range A1:A15 (with test values of Aug 11, Sep 11 etc). I also have additional information in columns B1:B15 and C1:C15. I would like the value in column B to be copied to cell D1 (text value) and the value in column C to be copied to cell D2 (format DD/MM/YYYY) on the new sheet that corresponds to the test values.

Is that at all possible. I have created a new workbook to explain.

Thank you in advance.

JP

**JBeaucaire** · 08-07-2011, 07:57 PM

Like so:

Sub CopyTemplate()
Dim wsList As Worksheet
Dim wsTemplate As Worksheet
Dim cell As Range

Set wsList = Sheets("Master")
Set wsTemplate = Sheets("Template")
Application.ScreenUpdating = False

For Each cell In wsList.Range("A:A").SpecialCells(xlConstants)
    wsList.Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = cell
    Range("D1") = cell.Offset(,1).Value
    Range("D2") = Format(cell.Offset(,2).Value, "DD/MM/YYYY")
Next

Application.ScreenUpdating = True
End Sub