Excel - previous sheet name when defining sourcedata for chart

Question

I am trying to write a macro to create a pivot table in a new sheet. I'm having problems eliminating the dependence on sheet names, as the names of my data changed daily, but the format does not.

My macro errors here with "Run-time error 438: Object doesn't support this property of method"

'create pivot table
Range("A1:J25").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    ActiveSheet.Last & "!R1C1:R25C10", Version:=xlPivotTableVersion14 _
    ).CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion14

What can I replace "sheetx!R1C1:R25C10" with that doesn't rely on a name?

UPDATE: After looking a little closer at my code (I've been using the macro recorder) I noticed that the new sheet had not been selected yet. I removed the reference to a sheetname all together, and just referenced the cells. This works for now, but I'm still interested in the answer to my original question as I'm sure I will deal with that problem in the near future. — skkevinperson, Dec 27 '13 at 08:06

L42 · Answer 1 · 2013-12-27T08:34:30.173

You can try this:

Sub CreatePivot()

Dim dataSource, destination As String
Dim wb As Workbook
Dim data_sheet, new_sheet As Worksheet

Set wb = ThisWorkbook
Set data_sheet = wb.Sheets("my_data") 'assuming you have Sheet named "my_data"
Set new_sheet = wb.Sheets.Add 'add the sheet
new_sheet.Name = "my_pivot" 'Rename it

dataSource = data_sheet.Name & "!" & data_sheet.Range("A1:J25").Address(ReferenceStyle:=xlR1C1) 'Set the source data, you can make this dynamic if you want
destination = new_sheet.Name & "!" & new_sheet.Range("A1").Address(ReferenceStyle:=xlR1C1) 'Set the destination which is the newly created sheet

'then add the pivot
wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataSource, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=destination, TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14

'take note that version differs depending on your Excel version

End Sub

Hope this gets you started.

I also appreciated your approach, this gives me a good example to reference when writing my own code instead of using the macro-recorder. — skkevinperson, Dec 31 '13 at 04:59

score 1 · Accepted Answer · answered Dec 27 '13 at 08:27

Glad you've got it working ... if the sheet name changes but the position of the sheet remains the same, you could reference by index:

Const DataSheetIndex As Integer = 1
Dim sourceData As String

sourceData = Worksheets(DataSheetIndex).Name & "!R1C1:R25C10"

Or if the name changes but at least has some consistency, e.g. initial characters, you could use the Worksheets collection ...

Dim ws As Worksheet

For Each ws In Worksheets

If (Left$(ws.Name, 4) = "test") Then

    sourceData = ws.Name & "!R1C1:R25C10"

End If

Next ws

And a link for different ways of referencing worksheets, in case one of those appears more appropriate for your use.

Thanks for answering the original question, and the link to the resources is appreciated! — skkevinperson, Dec 31 '13 at 04:56

score 0 · Answer 3 · edited Mar 06 '15 at 13:51

0

I just had the same problem and solved it by renaming the actual sheet first before creating pivot.

activesheet.name = "Data"

So in your case it would be:

Range("A1:J25").Select
activesheet.name = "Data" 
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R25C10", Version:=xlPivotTableVersion14 _
.........

I hope this helps.

edited Mar 06 '15 at 13:51

AJPerez

3,435
10
61
91

answered Mar 06 '15 at 13:04

Andy79

1

Excel - previous sheet name when defining sourcedata for chart

3 Answers3