|
Why R1C1 range parameters in VBA Pivot Table setup/
2007 (compatibility mode)
AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
Just read your thought. I'll take a look at what (external:=true) does.
I need to determine how the data range gets into the needed parameters necessary to prepare the table. My guess is that the last "range" selected is the external. Thanks Dave! Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
I'm not sure what you mean, but external:=true is a way to tell excel that you
want to include the workbook name and worksheet name and row/column address. Try this in a test macro: With ActiveCell MsgBox .Address(False, False) _ & vbLf & .Address(True, True) _ & vbLf & .Address(True, True, xlR1C1) _ & vbLf & .Address(external:=True) End With By using external:=true, I don't have to worry about building the string that would make sure the address was what I wanted. wrote: Just read your thought. I'll take a look at what (external:=true) does. I need to determine how the data range gets into the needed parameters necessary to prepare the table. My guess is that the last "range" selected is the external. Thanks Dave! Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe -- Dave Peterson |
Why R1C1 range parameters in VBA Pivot Table setup/
OK I believe I got your point by using:
PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
Dave,
What a perfect way to explain a point! EagleOne Dave Peterson wrote: I'm not sure what you mean, but external:=true is a way to tell excel that you want to include the workbook name and worksheet name and row/column address. Try this in a test macro: With ActiveCell MsgBox .Address(False, False) _ & vbLf & .Address(True, True) _ & vbLf & .Address(True, True, xlR1C1) _ & vbLf & .Address(external:=True) End With By using external:=true, I don't have to worry about building the string that would make sure the address was what I wanted. wrote: Just read your thought. I'll take a look at what (external:=true) does. I need to determine how the data range gets into the needed parameters necessary to prepare the table. My guess is that the last "range" selected is the external. Thanks Dave! Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
IIRC, the pivottable code will work with either R1C1 reference style or A1
reference style--in fact, it can even be the range itself. Did you try? ....,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe -- Dave Peterson |
Why R1C1 range parameters in VBA Pivot Table setup
For others where this may be a learning moment about PivotTables with VBA:
I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
[Can not even post it right]
For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
This is one of those strings that I don't like to build:
TableDestination:="Pivot of Transactions!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ I would have thought that this would fail. I would have guessed that you'd have needed something like this: TableDestination:="'Pivot of Transactions'!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ But that's still not good for the lazy. I would have used something like: Dim TabDest as range with worksheets("pivot of transactions") set tabdest = .cells(1, PivotRange.Columns.Count +2) end with Then I could let excel/vba do the heavy lifting: TableDestination:=tabdest.address(external:=true) or simply TableDestination:=tabdest And did .Create really work for you--ahhh. You're using xl2007. .Create isn't available in xl2003, so be careful if you have to share with people who use earlier versions. wrote: [Can not even post it right] For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe -- Dave Peterson |
Why R1C1 range parameters in VBA Pivot Table setup/
Thank you so much for the compatibility issue as that is a major concern.
Gee! Dave, why the issue over "'" vs "" who could possibly notice the difference? These dammmmed computers. And also, TableDestination:=tabdest. What can I say? Thank you! EagleOne Dave Peterson wrote: This is one of those strings that I don't like to build: TableDestination:="Pivot of Transactions!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ I would have thought that this would fail. I would have guessed that you'd have needed something like this: TableDestination:="'Pivot of Transactions'!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ But that's still not good for the lazy. I would have used something like: Dim TabDest as range with worksheets("pivot of transactions") set tabdest = .cells(1, PivotRange.Columns.Count +2) end with Then I could let excel/vba do the heavy lifting: TableDestination:=tabdest.address(external:=true) or simply TableDestination:=tabdest And did .Create really work for you--ahhh. You're using xl2007. .Create isn't available in xl2003, so be careful if you have to share with people who use earlier versions. wrote: [Can not even post it right] For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
|
Why R1C1 range parameters in VBA Pivot Table setup/
Dave,
From an" Old-fart" - 64 like me, thanks for your insight and knowledge. VBA is, absolutely, a necessary "evil" to make XL life more efficient in an age when it seems that few want to work very hard. Three years ago, if someone said VBA, I would have said what? EagleOne Dave Peterson wrote: If you're gonna make sure your code works in every version that will use it, it may be best to develop in the earliest version of excel. Then test it in newer versions. Usually, they'll be fewer things to change. wrote: Thank you so much for the compatibility issue as that is a major concern. Gee! Dave, why the issue over "'" vs "" who could possibly notice the difference? These dammmmed computers. And also, TableDestination:=tabdest. What can I say? Thank you! EagleOne Dave Peterson wrote: This is one of those strings that I don't like to build: TableDestination:="Pivot of Transactions!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ I would have thought that this would fail. I would have guessed that you'd have needed something like this: TableDestination:="'Pivot of Transactions'!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ But that's still not good for the lazy. I would have used something like: Dim TabDest as range with worksheets("pivot of transactions") set tabdest = .cells(1, PivotRange.Columns.Count +2) end with Then I could let excel/vba do the heavy lifting: TableDestination:=tabdest.address(external:=true) or simply TableDestination:=tabdest And did .Create really work for you--ahhh. You're using xl2007. .Create isn't available in xl2003, so be careful if you have to share with people who use earlier versions. wrote: [Can not even post it right] For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
And now you say Q@#Qing ^@#$ing VBA!
<vbg wrote: Dave, From an" Old-fart" - 64 like me, thanks for your insight and knowledge. VBA is, absolutely, a necessary "evil" to make XL life more efficient in an age when it seems that few want to work very hard. Three years ago, if someone said VBA, I would have said what? EagleOne <<snipped |
Why R1C1 range parameters in VBA Pivot Table setup/
Amen. I am a damnnnned CPA trapped in VBA heaven/hell
Dave Peterson wrote: And now you say Q@#Qing ^@#$ing VBA! <vbg wrote: Dave, From an" Old-fart" - 64 like me, thanks for your insight and knowledge. VBA is, absolutely, a necessary "evil" to make XL life more efficient in an age when it seems that few want to work very hard. Three years ago, if someone said VBA, I would have said what? EagleOne <<snipped |
Why R1C1 range parameters in VBA Pivot Table setup/
2007 (compatibility Mode 2003)
Dave, The macro recorder records exactly: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C6", TableName _ :="PivotTable3", DefaultVersion:=xlPivotTableVersion10 If I copy that very code into a Sub(), I get an error #5 "Invalid procedure call or argument" I am missing something obvious. I have activated the sheet via AccountFileBook.Activate Dave Peterson wrote: This is one of those strings that I don't like to build: TableDestination:="Pivot of Transactions!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ I would have thought that this would fail. I would have guessed that you'd have needed something like this: TableDestination:="'Pivot of Transactions'!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ But that's still not good for the lazy. I would have used something like: Dim TabDest as range with worksheets("pivot of transactions") set tabdest = .cells(1, PivotRange.Columns.Count +2) end with Then I could let excel/vba do the heavy lifting: TableDestination:=tabdest.address(external:=true) or simply TableDestination:=tabdest And did .Create really work for you--ahhh. You're using xl2007. .Create isn't available in xl2003, so be careful if you have to share with people who use earlier versions. wrote: [Can not even post it right] For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
|
Why R1C1 range parameters in VBA Pivot Table setup/
Dave, I got this from Google Search.
Debra Dalgleish Jan 15, 2007 gave an answer to the same issue: "Assuming the worksheets all have data starting in the top left corner, you could use code similar to the following, substituting your field names: " '=================== Sub CreatePTs() Dim wb As Workbook Dim ws As Worksheet Dim wsPT As Worksheet Dim strName As String Dim strNamePT As String strName = "Pivot_" strNamePT = "PT_" Set wb = ActiveWorkbook For Each ws In wb.Worksheets If Left(ws.Name, 6) < strName Then wb.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ws.Range("A1").CurrentRegion).CreatePi votTable _ TableDestination:="", TableName:=strNamePT & ws.Name Set wsPT = ActiveSheet With wsPT .Name = strName & Left(ws.Name, 25) .PivotTableWizard TableDestination:=.Range("B4") With .PivotTables(1) .AddFields RowFields:="Year" .PivotFields("Total").Orientation = xlDataField End With End With End If Next ws End Sub ... If anyone knows why the automation error though? Dave Peterson wrote: Maybe someone who uses xl2007 will chime in. wrote: 2007 (compatibility Mode 2003) Dave, The macro recorder records exactly: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C6", TableName _ :="PivotTable3", DefaultVersion:=xlPivotTableVersion10 If I copy that very code into a Sub(), I get an error #5 "Invalid procedure call or argument" I am missing something obvious. I have activated the sheet via AccountFileBook.Activate Dave Peterson wrote: This is one of those strings that I don't like to build: TableDestination:="Pivot of Transactions!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ I would have thought that this would fail. I would have guessed that you'd have needed something like this: TableDestination:="'Pivot of Transactions'!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ But that's still not good for the lazy. I would have used something like: Dim TabDest as range with worksheets("pivot of transactions") set tabdest = .cells(1, PivotRange.Columns.Count +2) end with Then I could let excel/vba do the heavy lifting: TableDestination:=tabdest.address(external:=true) or simply TableDestination:=tabdest And did .Create really work for you--ahhh. You're using xl2007. .Create isn't available in xl2003, so be careful if you have to share with people who use earlier versions. wrote: [Can not even post it right] For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
Why R1C1 range parameters in VBA Pivot Table setup/
Using Debra's template I found success with XL 2003 AccountFileBook.PivotCaches.Add(SourceType:=xlData base, SourceData:= _ Sheets("Pivot of Transactions").Range("A1").CurrentRegion). _ CreatePivotTable TableDestination:="'Pivot of Transactions'!R1C6", _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Notice notice the change after the SourceData:= From: "Pivot of Transactions!R1C1:R2458C4" To: Sheets("Pivot of Transactions").Range("A1").CurrentRegion) I also played with the 2003 vs 2007 issue (to no avail): Pivot of Transactions! vs 'Pivot of Transactions'! So the above works for XL 2003. It appears to be something related to how the data source is presented. I also noted the KB item but am not sure of its relevance. http://support.microsoft.com/kb/263498/en-us EagleOne Dave Peterson wrote: This is one of those strings that I don't like to build: TableDestination:="Pivot of Transactions!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ I would have thought that this would fail. I would have guessed that you'd have needed something like this: TableDestination:="'Pivot of Transactions'!" & _ Cells(1, PivotRange.Columns.Count +2).address, _ But that's still not good for the lazy. I would have used something like: Dim TabDest as range with worksheets("pivot of transactions") set tabdest = .cells(1, PivotRange.Columns.Count +2) end with Then I could let excel/vba do the heavy lifting: TableDestination:=tabdest.address(external:=true) or simply TableDestination:=tabdest And did .Create really work for you--ahhh. You're using xl2007. .Create isn't available in xl2003, so be careful if you have to share with people who use earlier versions. wrote: [Can not even post it right] For others where this may be a learning moment about PivotTables with VBA: I needed a PivotTable in a w/b, the entirety of which, is created with a massive macro. Previously, I had not created a Pivot table in VBA. So, clever EagleOne created a pivot table with the Macro-recorder on. Clever EagleOne, got stumped by the Recorder's use of R1C1 formula referencing. "Was R1C1 the secret to successful PTs? Well Dave popped my bubble with "either A1 or R1C1 referencing" will work just fine, via his clever use of MsgBox. Unfortunately, the fact that my named "PivotRange" range was being "clarified/modified via VBA" went right over my head. Ultimately, "my" PT code ended up being Dave's 1st comment: AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ PivotRange.Address(External:=True), VERSION:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Pivot of Transactions!" & Cells(1, PivotRange.Columns.Count + 2).address, _ TableName:="PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Either xlA1 or R1C1 will work; see Dave's example. To those interested in using VBA for table placement on the same worksheet, notice the code: TableDestination:="Your-sheet-name!" & Cells(1, PivotRange.Columns.Count + 2).address, .... which places the table in Row 1 but two columns to the right of the table data. A round of applause for the MVP's who get -$0- for all of the help they give. EagleOne Dave Peterson wrote: IIRC, the pivottable code will work with either R1C1 reference style or A1 reference style--in fact, it can even be the range itself. Did you try? ...,soucedata:=pivotrange, ... But if you're going to use the .address, you'll want to include the external:=true parm. That way you won't have to worry about the wrong worksheet being used. wrote: OK I believe I got your point by using: PivotRange.address(ReferenceStyle:=xlR1C1) after looking up: Range.Address Property "Returns a String value that represents the range reference in the language of the macro. expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)" Thanks Dave Dave Peterson wrote: If pivotrange is a variable declared as a range, then how about: ...,soucedata:=pivotrange.address(external:=true), ... wrote: 2007 (compatibility mode) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!R1C1:R2458C4", VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!R1C5", TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 Curious why R1C1 style formula references? Also, why does a range reference like PivotRange not work i.e.: Dim myRange as Range PivotRange = Range(Range("A1:D1"), Range("A1:D1").End(xlDown)) AccountFileBook.PivotCaches.Create(SourceType:=xlD atabase, SourceData:= _ "Pivot of Transactions!" & PivotRange, VERSION:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="Pivot of Transactions!" & Cells(1, _ PivotRange.Columns.Count +1) , TableName:= _ "PivotOfTrans", DefaultVersion:=xlPivotTableVersion10 TIA EagleONe |
| All times are GMT +1. The time now is 05:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com