VBA to select all data on a sheet, varies each time, then create pivot

[LauraWork]

hi all

I recorded a Macro and I'm chaning all the parts to work regardless of how much data I have but I cant get the pivot to work, I just need it to select all the data on the sheet which will always have 19 columns but the row number will vary each time I run the report.

how can I amend this: I have trieds lots of solutions from here and other places and cant get anything to work. I cant preselect as this shows the word 'blank' and I cant have that in the report: I also need to make sure the pivot is on a new sheet.

Heres the snapshot:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R2007C17", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select

thanks in advance

 

[par60056]

I do this all the time. Here is a bit of code that may help you. You have most of it.

dim lastRow as Long
dim lastCol as Integer

lastRow = sheets("Data").usedrange.rows.count
lastCol = sheets("Data").usedrange.columns.count
sheets.Add
[COLOR=#333333]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _[/COLOR]
[COLOR=#333333]"Data!R1C1:R[/COLOR][COLOR=#ff0000]"&lastRow&"[/COLOR][COLOR=#333333]C[/COLOR][COLOR=#ff0000]"&lastCol[/COLOR][COLOR=#333333], Version:=xlPivotTableVersion10).CreatePivotTable _[/COLOR]
[COLOR=#333333]TableDestination:="C3", TableName:="PivotTable1", DefaultVersion _[/COLOR]
[COLOR=#333333]:=xlPivotTableVersion10[/COLOR]
[COLOR=#333333]Sheets("Sheet1").Select[/COLOR]
[COLOR=#333333]Cells(3, 1).Select[/COLOR]

 

[LauraWork]

Hi, I have tried your code and I get a compile error and its highlighting the C bit below as the error - any ideas?

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R2007C17", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="&lastRow&"C"&lastCol, TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

 

[par60056]

You changed the wrong field. The idea is to put the lastrow and lastcol in the "SourceData" not the "TableDestination"

 

[LauraWork]

Oh yeah sorry #fail!

I have copied the code above exact and it still wont work? any ideas?




Dim lastRow As Long
Dim lastCol As Integer

lastRow = Sheets("Data").UsedRange.Rows.Count
lastCol = Sheets("Data").UsedRange.Columns.Count
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R"&lastRow&"C"&lastCol, Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="C3", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select

 

[LauraWork]

is this right?

Dim lastRow As Long
Dim lastCol As Integer
lastRow = Sheets("Data").UsedRange.Rows.Count
lastCol = Sheets("Data").UsedRange.Columns.Count
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R" & lastRow & "C" & lastCol & "", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="C3", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet1").Select
Cells(3, 1).Select

 

[par60056]

That looks right. However, you should not need to do "Sheets("Sheet1").Select" The sheet you just added may have any name. It will still be the active sheet.

What did it say when you tried that?

 

[LauraWork]

That looks right. However, you should not need to do "Sheets("Sheet1").Select" The sheet you just added may have any name. It will still be the active sheet.

What did it say when you tried that?

yes that works great thanks - but I am making multiple pivots from multiple data sources so I will need to select the last pivots pasted value data for the next if you see what I mean so I copied the code again and it says I can't use it twice so how can I get around that?

 

[LauraWork]

Actually I may have fixed it I just renamed 'lastRow' as 'lastRowa' etc

 

[BioExcel]

Hi,

I have an issue with below code, which is similar to the one mentioned earlier in this thread. I'm using excel 2010.
Problem is that I keep receiving an error on the bold part of the code. anyone who sees the problem with this code?

Error message = Runtime error 5. Invalid procedure call or argument
code =

Sub Pivot()
'
' Pivot Macro
'

'
Sheets("Overzicht").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select

Dim lastRow As Long
Dim lastCol As Integer
lastRow = Sheets("Filter").UsedRange.Rows.Count
lastCol = Sheets("Filter").UsedRange.Columns.Count

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Filter!R1C1:R" & lastRow & "C" & lastCol, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Overview!R1C1", TableName:="Departed", DefaultVersion _
:=xlPivotTableVersion14
With ActiveSheet.PivotTables("Departed").PivotFields("Year departed")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Departed").AddDataField ActiveSheet.PivotTables( _
"Departed").PivotFields("DL"), "Count of DL", xlCount
With ActiveSheet.PivotTables("Departed").PivotFields("Place")
.Orientation = xlRowField
.Position = 1
End With
Range("E2").Select
ActiveSheet.PivotTables("Departed").RowGrand = False
Range("A13").Select
ActiveSheet.PivotTables("Departed").ColumnGrand = False
End Sub