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

**Kwals** · 09-03-2012, 09:28 AM

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

**arlu1201** · 09-03-2012, 09:29 AM

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

**taps** · 09-03-2012, 12:04 PM

Hi Kwals,

Welcome to Excel Forum.

You can use ..

Activesheet.name="test2" instead of 

Sheets("Sheet27").Select
Sheets("Sheet27").Name = "test2"

**sarails** · 09-04-2012, 01:19 AM

Hiya Qwals,

This is a modified version of what I use to duplicate a sheet a daily basis.

Sub Copy_SheetData_Test()

Dim NewSheetName As String
Dim Wsc As Long

Wsc = ActiveWorkbook.Worksheets.Count          'This code counts the number of sheets in your workbook
                                               'It only counts current sheets so it automatically allows for
                                               'deleted sheets

NewSheetName = "Test2"

Sheets("Test Pivot").Copy After:=Sheets(Wsc)
Sheets("Test Pivot (2)").Name = NewSheetName

End Sub

I use this code below to name the sheet with a weekday (just to give you some ideas if you need something that will give you a new sheet per day

If Weekday(Date) = 2 Then	                  'day 2 is Monday – because we do not report on the Weekend
                                                  ' we want the last date to be the Friday previous
    NewSheetName = Format(Date - 3, "dd.mm.yy")	        'This gives the previous Friday as the sheet name
    Else: NewSheetName = Format(Date - 1, "dd.mm.yy")	'This makes the sheet name the previous day
    End If

I trust this helps

Iain

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions