Results 1 to 5 of 5

Code to create new sheet and rename, regardless of any other sheets in workbook

Threaded View

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Atlanta, GA
    MS-Off Ver
    Excel 2007
    Posts
    63

    Code to create new sheet and rename, regardless of any other sheets in workbook

    I've searched quite a bit, but can't find the exact answer I'm looking for.
    I was wondering if anyone could provide the code for the following.
    Create a new sheet and rename it 'FUTA Max"

    I know that seems simple as this
    'Create PT using NAMED RANGE so PT dynamically selects all rows
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "=RANGE", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
            :="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion:= _
            xlPivotTableVersion12
        Sheets("Sheet1").Select
        Cells(1, 1).Select
        Sheets("Sheet1").Select
        Sheets("Sheet1").Name = "FUTA Max"
        ActiveSheet.PivotTables("PivotTable1").Name = "FUTA_Max"
    BUT, this only works if the worksheet created = "Sheet 1". I get a debug error when there is already a Sheet 1 or any other Sheet # for that matter. Somtimes the new worksheet is Sheet 1, sometimes it is Sheet 7, etc..

    I need the code that would create a sheet and rename it to "FUTA Max" no matter what sheets are already in the workbook.
    I've seen many "almost" resolutions that would probably be easily coded to fit my situation, but I am a VBA newb and can't really manipulate other people's code. This is part of a larger macro, but that is the first part.

    Thanks,

    Thanks,
    Last edited by csh8428; 07-18-2012 at 03:56 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1