Macro to insert a worksheet into a workbook, rename the work sheet and copy and past data

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:

Please Login or Register  to view this content.

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