Hello Friends,

I am trying to save some steps in my tasks. I need to create a macro that will add a worksheet to a workbook rename the worksheet and then copy data from a pivot table to that worksheet. I have used the Macro recorder to do this. However when I run the macro I get a run time error because the name of the sheet is static in the code and so I think the code is looking for specific sheet. How can I revise it s that it rename whatever the next numbered worksheet is that is added? Please help. It seems this should be pretty simple but I have been banging my head for a couple of days now. Here is the code I currently have:


Sheets("Test Pivot").Select
    Sheets.Add
    Sheets("Sheet27").Select
    Sheets("Sheet27").Name = "test2"
    Sheets("Test Pivot").Select
    Cells.Select
    Selection.Copy
    Sheets("test2").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Columns.AutoFit
    Columns("F:T").Select
    Application.CutCopyMode = False
    Selection.Style = "Comma"
End Sub

How do I change the code to read instead of "Sheet27" something like Lastsheet so it is looking for the last numbered sheet added not a specific number - this is where the macro times out.

Thanks in advance for your help.

Kwals