variable sheet for Macro

stealthdevil · April 30th, 2007, 03:16 PM

Hi,

I'm working on creating a macro that will generate pivot tables based off a worksheet of data. I've used the macro recorder, but I'm having a problem. Every time a new sheet is made the sheet number goes up. Say, it makes a pivot table on sheet3. The macro will record sheet3 as where the pivot table should be put when the macro runs. But, I'll need to run the macro more than once, so I'll delete pivot tables. So when the next sheet is generated it would be sheet4, which gives an error b/c the macro looks for sheet 3. Is there someway to make this a variable?

Thanks,

David

stealthdevil · April 30th, 2007, 03:35 PM

Well, it seems to be working now. I commented out the below, automatically generated, code and now it works fine.

    'Sheets("Sheet17").Select
    'Sheets("Sheet17").Name = "View2 - By STAW"
    'Range("D12").Select

stealthdevil · May 1st, 2007, 11:37 AM

What code can I use to put a name, that would be constant, on the tab of a generated sheet?

ltpeter · May 1st, 2007, 09:23 PM

In my Opinion I wiil set the new sheet name is Pivot, and generated pivottable in that sheet.

Please using this Code

Code:

Sub CheckSheetName()
dim sht as Worksheet

For each sht in ThisWorkbook.Worksheets
  If sht.Name = "Pivot" Then
    Application.DisplayAlerts = False
    Sht.Delete
    Application.DisplayAlerts = False
  Else
   Sheets.Add After:=Sheets(Sheets.Count)
   ActiveSheet.Name ="Pivot"
  End If
Next

End Sub

N. Yauvasuta
MVP Excel

stealthdevil · May 2nd, 2007, 12:54 PM

I didn't get this code to work for me. I get an error saying the name is already used or is a key word.

stealthdevil · May 2nd, 2007, 01:05 PM

Well, back to the first problem....
I'm trying to make seven pivot tables. I will start out with one worksheet that has all the data the pivot tables use. I'm trying to automate this with a Macro. I've created the first 2 and they work perfectly except that the name doesn't show up on the tab, it just shows Sheet6 or Sheet7 or....
I'm using the Macro recorder for each Pivot Table then cutting the code and pasting it into a Main Macro, one pivot table after the other. When I run it the first 2 are created, but then I get an error at the beginning of the 3rd PT. This is the error I get:
Application-defined or object-defined error

This is the code where the error happens:

Code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R4805C40").CreatePivotTable TableDestination:="", TableName:= _"PivotTable3"

I've tried building it off of the main data sheet and I've also tried building it off of the first pivot b/c they share the same fields, but I get the error either way.

ltpeter · May 3rd, 2007, 11:18 AM

Only MS Excel 2002 and above using PivotCache to generated report.

I recommend You using PivotTable Wizard Method to create the PivotTable to prevent the error

Please try this code

Code:

Dim pvt As PivotTable

Sub CheckPivotTable()
    Dim sht As Worksheet
    Application.DisplayAlerts = False
    For Each sht In ThisWorkbook.Worksheets
        If sht.Name = "Pivot" Then sht.Delete
    Next
    Sheets.Add After:=Sheets(Worksheets.Count)
    ActiveSheet.Name = "Pivot"
    Application.DisplayAlerts = True
End Sub

Sub GeneratePivot()
CheckPivotTable
Range("A4").Select
With ActiveSheet.PivotTableWizard(xlDatabase, ThisWorkbook.Names("Database").RefersToRange, _
    ActiveCell, "SalePivot")
    .AddFields "SalesPerson", "Type", Array("Year", "Month")
    .PivotFields("Sales").Orientation = xlDataField
    .PivotFields("Units").Orientation = xlDataField
    .PivotFields("Sum of Units").Function = xlAverage
    .PivotFields("Sum of Sales").NumberFormat = "#,##0.00"
    .PivotFields("Average of Units").NumberFormat = "#,##0.00"
End With
End Sub

N. Yauvasuta
MVP Excel

allenm · May 8th, 2007, 03:48 PM

When deleting a sheet of a specific name for a workbook with a large number of sheets, I find using an error trap allows me to attempt sheet delete without looping through several sheets potentially several times. This may not seem like much but any time you can avoid a coded loop that's interpreted the faster your code overall will run. The example below disables display of alerts when attempting to delete the sheet.

Example:
------------------------------------------------------------------------------

Private Sub My_Sub()

  dim sToDelete As String ', Other dim declarations
'Other lines of code leading up to delete up to here
  sToDelete = "Pivot" 'Or whatever is determining the name of sheet to delete in your loop
  Call Sheet_Delete(sToDelete)
'Other Lines of code after delete after

End Sub

Public Sub Sheet_Delete(sPassed As String)

'Deletes passed sheet if it exists
  Application.DisplayAlerts = False
  On Error Goto Sheet_Delete_Failed
    Worksheets(sPassed).Delete
  On Error Goto 0
Sheet_Delete_Failed:
  Application.DisplayAlerts = True

End Sub
------------------------------------------------------------------------------

Hope this is useful.

stealthdevil · May 10th, 2007, 09:45 AM

Thanks for the replies guys!
I'm still working on this. I've decided to try keeping the sheets for the 7 pivottables and just clearing them each time I run the macro using this code:

Code:

    Worksheets("View1 - Summary").Range("A1:P1999").Clear
    Worksheets("View2 - By STAW").Range("A1:P1999").Clear
    Worksheets("View3 - By OO#").Range("A1:P1999").Clear
    Worksheets("View4 - By Chain").Range("A1:P1999").Clear
    Worksheets("View5 - By LeadCtn").Range("A1:P1999").Clear
    Worksheets("View6 - Exports").Range("A1:P1999").Clear
    Worksheets("View7 - By Brand").Range("A1:P999").Clear

Thanks for the code snippets Itpeter! I've been trying to use this form, but haven't got it to work for me yet.

Can someone explain how this following code works for me? TableDestination is set equal to a Range, but I don't know what sheet the range is for. When I run it the Pivot Table forms over my data, but it needs to be on a different sheet. How can I tell it where to put the pivot table?

Code:

  ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,SourceData:= _ "Data!R1C1:R4805C40").CreatePivotTable TableDestination:=Range("A1"), _TableName:="PivotTable3"

allenm · May 10th, 2007, 03:46 PM

In TableDestination:= you just specify a range so it assumes the current sheet. You can specify it further:
TableDestination:=Worksheets("Destination").Range( "A1")