"SourceData" and "TableDestination" for PivotTable

[linesite]

I download data from a web site on weekly basis and break information down by branch location and employees assigned to each branch--

Data source will fluctuate depending on new hire employees and those that quit or get laid off.

I used macro recorder to help create this section of VBA to help automate the PivotTable process.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "Data!R1C1:R134C4"

Two worksheets exist in this workbook; my data source named "Data" and the new worksheet for PivotTable data that I create in the beginning of the macro named "PT" for the location of PivotTable.

The "R1C1:R134C4" of this code will not always represent the data source each time I download data from the web site. It may contain less rows than the previous week or more depending on how many people we hire. What code can I insert to replace this section of code to include this fluctuation of data rows?

Should I replace code for "TableDestination:="Sheet10!R3C1"" with "TableDestination:="PT!R3C1""?

 

[bigroo]

Hi Linesite,

I have pretty much the same issue as well. I recorded a macro create a pivot table and then adjusted the code to look for last row.

'Find Last used row on Test
Lastrow = Sheets("Test").Range("C" & Rows.Count).End(xlUp).Row
Debug.Print Lastrow

'Which sheet is the data coming from
Sheets("Test").Select
Range("C33:J" & Lastrow).Select

'Add a new sheet and create the Pivot Table
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Test!R33C3:R" & Lastrow & "C10", Version:=xlPivotTableVersion10). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet1").Select

See if this will help you out?

 

[bigroo]

Hi Linesite,

Sorry for the above, here is my code within the correct tags.

'Find Last used row on Billable Copy Report Sheet
    Lastrow = Sheets("Billable Copy Report").Range("C" & Rows.Count).End(xlUp).Row
    Debug.Print Lastrow
        
'Which sheet is the data coming from
    Sheets("Billable Copy Report").Select
    Range("C33:J" & Lastrow).Select
    
'Add a new sheet and create the Pivot Table
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Billable Copy Report!R33C3:R" & Lastrow & "C10", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion10
    Sheets("Sheet1").Select

 

[linesite]

Hey bigroo--

"ActiveSheet.UsedRange" in "SourceData:=" works in my worksheet. This code creates the PivotTable.

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange). _
CreatePivotTable TableDestination:="", TableName:="PT", DefaultVersion:=xlPivotTableVersion11