PDA

View Full Version : Solved: Code change to allow for Data table changes



4elephants
06-17-2008, 12:47 PM
I am trying to create a pivot table with a macro. I have recorded the macro and am trying to amend the code to make allowance for the fact that every month there will be a different number of rows (Columns will always remain constant).

I have recorded the steps a couple of different ways but I always get the same area in my pivot table.

Could someone take a llok at this little bit of code and suggest a change to make it work when the rows are more or less than 2520?

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Main!R1C1:R2520C21").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("cust")
.Orientation = xlRowField
.Position = 1
End With

Thanks in advance for any advice/assistance.

Simon Lloyd
06-17-2008, 02:27 PM
Use dynamic named ranges, they expand and contract automatically when data is added or removed!

Ago
06-17-2008, 02:32 PM
Cells(Rows.Count, 18).End(xlUp).Row
is your last cell in column R. thats the cell you are looking for isnt it?

am i reading it correct if the range is supposed to be C21:R"lastcell"?
if so you could do this.



rng = Range("C21", "R" & Cells(Rows.Count, 18).End(xlUp).Row)

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Main!R1C1:" & rng ).CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("cust")
.Orientation = xlRowField
.Position = 1
End With



i marked my changes with red.

Simon Lloyd
06-17-2008, 02:40 PM
R1C1:R2520C21 if delivered at A1 would encompass a range A1:U2520

Simon Lloyd
06-17-2008, 02:42 PM
This:

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
effectively is selecting the UsedRange

Ago
06-17-2008, 02:47 PM
This:

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
effectively is selecting the UsedRange

no, not for me.
xlToRight selects from left to right untill it hits a used cell.

so if column A and B is empty and C has 1 as value.
it will select column A and B.

or is that only me?

Simon Lloyd
06-17-2008, 02:52 PM
no, not for me.
xlToRight selects from left to right untill it hits a used cell.

so if column A and B is empty and C has 1 as value.
it will select column A and B.

or is that only me?Sort of right, given your criteria it will select all of columns A,B and C as that kind of selecting for data stops as you pointed out at the first column that contains data.

Ago
06-17-2008, 02:52 PM
R1C1:R2520C21 if delivered at A1 would encompass a range A1:U2520

so you are saying the range should be "A1", "U" &

Cells(Rows.Count, 21).End(xlUp).Row ?

Simon Lloyd
06-17-2008, 02:55 PM
Yep, effectively thats what the source is looking at a static range R1C1:R2520C21 so from A1 it's a range of A1:U2520, so to give it flexibilty you would be right.

Ago
06-17-2008, 02:57 PM
Sort of right, given your criteria it will select all of columns A,B and C as that kind of selecting for data stops as you pointed out at the first column that contains data.

oh yes i see!
if i use a "complete" row it will select used cells untill it hits a empty.
but since the number of columns is known and fixed i think is much better to just type in the number of columns in the code rather than having some unstable code doing a good guess.
there might be a empty cell somewhere in the table and all of the sudden everythings messed up...

Simon Lloyd
06-17-2008, 03:00 PM
Its worse than that, if you dont have any data in row 1 then it will select the entire worksheet!....ooops! that would be disasterous, its the same as Cells.Select

Ago
06-17-2008, 03:06 PM
ohh.. sounds bad.
i think its time that we get more information about the sheet we are talking about here so we dont mess anything up.

could you explain what rows and columns you want to use, or supply us with a sample or the workbook?

4elephants
06-17-2008, 11:36 PM
Thanks for your help guys, and sorry for the delay in responding.

I have a data sheet ?Main? and each month I import into this a customers trade details. There will always be the same number of columns (21, and all will have data in them), but dependant on turnover the number of rows may go up or down.

I produce a pivot table from the data sheet, but I have tried to record the macro a couple of different ways but I always get the same range of cells R1C1:R2520C21. I was hope that by using ctrl+shift+left (and down) arrow would give me a flexible range but it didn?t.

I just need a little tweek (as a newbie to VBA it may not be a little), to enable the pivot table to recognise the fact that the data has more or less rows than before and to include them all (or not include blank rows from the bottom).

I am attaching a sample file in the hope it makes it a little clearer.

Thanks again for you assistance.

marshybid
06-18-2008, 03:03 AM
Hi There,

if I am correctly understanding your query, you want to ensure that the pivot table you create always uses columns A to T, and that it will always include all rows which may change each week)??

If that is correct then I have amended your pivot code from your attached file as below (text in green is where I have made change or added):



Sub Pivottable()
'
' Pivottable Macro
' Macro recorded 6/12/2008 by martin.richmond
'
' Keyboard Shortcut: Ctrl+Shift+T
'
Sheets("Main").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Main!A:T").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("cust")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("cost"), "Sum of cost", xlSum
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("vat"), "Sum of vat", xlSum
Range("B3").Select
With ActiveSheet.PivotTables("PivotTable2").DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

ActiveSheet.PivotTables("PivotTable2").PivotFields("cust").PivotItems _
("(blank)").Visible = False

Range("D5").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("D5").Select
Selection.AutoFill Destination:=Range("D5:D9"), Type:=xlFillDefault
Range("D5:D9").Select
Selection.AutoFill Destination:=Range("D5:D10"), Type:=xlFillDefault
Range("D5:D10").Select
Selection.NumberFormat = "[$?-809]#,##0.00;[Red]-[$?-809]#,##0.00"
Range("D4").Select
ActiveCell.FormulaR1C1 = "Inv Total"
End Sub


By making SourceData:= _ "Main!A:T" This will include all rows to end of spreadsheet for columns A to T, in this way it doesn't matter how many rows your data is populating.

I have assumed that your first PivotField ("cust") is the key filter point and so I have added ActiveSheet.PivotTables("PivotTable2").PivotFields("cust").PivotItems _
("(blank)").Visible = False

This will ensure that the blank rows are not shown in the pivot table.

I hope that this helps.

Marshybid :hi:

4elephants
06-18-2008, 10:53 PM
Thanks for that Marshybid, it worked great. Don?t know why I didn?t think to do it that way myself.

I?ll get my head round this VBA stuff one day, course pigs might be flying when I do :rotlaugh:

Thanks again for your help :beerchug: