Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
Hello,
If this is not the correct group, please let me know which one is. I am pretty new to Excel scripting. I am able to do what I need most of the time. My problem is that I want to place a pivot table in a scriptand that works, but the data range is never the same. I have recorded a macro an that works for said data. It gives me the following: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be different almost each time. I have tried to take a range that is way too big, but then the Pivot table gives me the wrong results. So what is the best way to select the correct range each time? I have searched with Google for a few days, but came up with nothing. :-( I am working with Excel 2000. Sorry, no option in updating or upgrading. Company policy. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
Hi
you could insert some code to find the lastrow Dim lastrow as long Lastrow = Cells(Rows.Count, 1).End(xlUp).row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" -- Regards Roger Govier "houghi" wrote in message ... Hello, If this is not the correct group, please let me know which one is. I am pretty new to Excel scripting. I am able to do what I need most of the time. My problem is that I want to place a pivot table in a scriptand that works, but the data range is never the same. I have recorded a macro an that works for said data. It gives me the following: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be different almost each time. I have tried to take a range that is way too big, but then the Pivot table gives me the wrong results. So what is the best way to select the correct range each time? I have searched with Google for a few days, but came up with nothing. :-( I am working with Excel 2000. Sorry, no option in updating or upgrading. Company policy. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
I get a 'Runtime error 1004'. Reference is not valid. With 'debug' the whole line is yellow. When I change !Range(Cells(2,1),cells(lastrow,5)) back to !R2C1:R37C5 then it works again. Could it be that this not work because I work with Excel 2000? If that is the case, is there a different solution? Roger Govier wrote: Hi you could insert some code to find the lastrow Dim lastrow as long Lastrow = Cells(Rows.Count, 1).End(xlUp).row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
Hi
What value shows for lastrow? -- Regards Roger Govier "houghi" wrote in message ... I get a 'Runtime error 1004'. Reference is not valid. With 'debug' the whole line is yellow. When I change !Range(Cells(2,1),cells(lastrow,5)) back to !R2C1:R37C5 then it works again. Could it be that this not work because I work with Excel 2000? If that is the case, is there a different solution? Roger Govier wrote: Hi you could insert some code to find the lastrow Dim lastrow as long Lastrow = Cells(Rows.Count, 1).End(xlUp).row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!Range(Cells(2,1),cells(lastrow,5)) _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
I have added the following
lastrow = Cells(Rows.Count, 1).End(xlUp).Row MsgBox lastrow That shows 37. To be sure I even did ` lastrow = "37" ` so there won't be an issue with calculation lastrow. Roger Govier wrote: Hi What value shows for lastrow? houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
Can you open that report.xls file to find the last used row?
dim RptWkbk as workbook dim ActWks as worksheet dim LastRow as long dim PTRng as range set actwkbk = activesheet set rptwkbk = workbooks.open(Filename:="y:\Report.xls") with rptwkbk.worksheets(1) 'I used column A to find the last used row lastrow = .cells(.rows.count,"A").end(xlup).row set ptrng = .range("a2:E" & lastrow) end with 'do more stuff??? with actwks .parent.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ptrng.address(external:=true)) _ .CreatePivotTable TableDestination:=.Range("A4"), _ TableName:="PivotTable1" 'do more stuff rptwkbk.close savechanges:=false ==== Uncompiled, untested. Watch for typos. houghi wrote: Hello, If this is not the correct group, please let me know which one is. I am pretty new to Excel scripting. I am able to do what I need most of the time. My problem is that I want to place a pivot table in a scriptand that works, but the data range is never the same. I have recorded a macro an that works for said data. It gives me the following: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be different almost each time. I have tried to take a range that is way too big, but then the Pivot table gives me the wrong results. So what is the best way to select the correct range each time? I have searched with Google for a few days, but came up with nothing. :-( I am working with Excel 2000. Sorry, no option in updating or upgrading. Company policy. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
Thanks, I get the basic idea, but unfortunatly I am still in the copy
and paste fase of learning the language, so the compile error I get is a stopper for me. I do not know how to solve that. I have added sub Pivot_2() at the beginning and End Sub at the end. The rest will be for monday, as I have no access to Excel over the weekend. Dave Peterson wrote: Can you open that report.xls file to find the last used row? dim RptWkbk as workbook dim ActWks as worksheet dim LastRow as long dim PTRng as range set actwkbk = activesheet set rptwkbk = workbooks.open(Filename:="y:\Report.xls") with rptwkbk.worksheets(1) 'I used column A to find the last used row lastrow = .cells(.rows.count,"A").end(xlup).row set ptrng = .range("a2:E" & lastrow) end with 'do more stuff??? with actwks .parent.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ptrng.address(external:=true)) _ .CreatePivotTable TableDestination:=.Range("A4"), _ TableName:="PivotTable1" 'do more stuff rptwkbk.close savechanges:=false ==== Uncompiled, untested. Watch for typos. houghi wrote: Hello, If this is not the correct group, please let me know which one is. I am pretty new to Excel scripting. I am able to do what I need most of the time. My problem is that I want to place a pivot table in a scriptand that works, but the data range is never the same. I have recorded a macro an that works for said data. It gives me the following: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be different almost each time. I have tried to take a range that is way too big, but then the Pivot table gives me the wrong results. So what is the best way to select the correct range each time? I have searched with Google for a few days, but came up with nothing. :-( I am working with Excel 2000. Sorry, no option in updating or upgrading. Company policy. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
I had a couple of typos (sorry).
This compiles, but I didn't test it: Option Explicit Sub testme() Dim RptWkbk As Workbook Dim ActWks As Worksheet Dim LastRow As Long Dim PTRng As Range Set ActWks = ActiveSheet '<--changed Set RptWkbk = Workbooks.Open(Filename:="y:\Report.xls") With RptWkbk.Worksheets(1) 'I used column A to find the last used row LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set PTRng = .Range("a2:E" & LastRow) End With 'do more stuff??? With ActWks .Parent.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=PTRng.Address(external:=True)) _ .CreatePivotTable TableDestination:=.Range("A4"), _ TableName:="PivotTable1" End With '<--added 'do more stuff RptWkbk.Close savechanges:=False End Sub houghi wrote: Thanks, I get the basic idea, but unfortunatly I am still in the copy and paste fase of learning the language, so the compile error I get is a stopper for me. I do not know how to solve that. I have added sub Pivot_2() at the beginning and End Sub at the end. The rest will be for monday, as I have no access to Excel over the weekend. Dave Peterson wrote: Can you open that report.xls file to find the last used row? dim RptWkbk as workbook dim ActWks as worksheet dim LastRow as long dim PTRng as range set actwkbk = activesheet set rptwkbk = workbooks.open(Filename:="y:\Report.xls") with rptwkbk.worksheets(1) 'I used column A to find the last used row lastrow = .cells(.rows.count,"A").end(xlup).row set ptrng = .range("a2:E" & lastrow) end with 'do more stuff??? with actwks .parent.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=ptrng.address(external:=true)) _ .CreatePivotTable TableDestination:=.Range("A4"), _ TableName:="PivotTable1" 'do more stuff rptwkbk.close savechanges:=false ==== Uncompiled, untested. Watch for typos. houghi wrote: Hello, If this is not the correct group, please let me know which one is. I am pretty new to Excel scripting. I am able to do what I need most of the time. My problem is that I want to place a pivot table in a scriptand that works, but the data range is never the same. I have recorded a macro an that works for said data. It gives me the following: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "'Y:\\[Report.XLS]Sheet1'!R2C1:R37C5" _ ).CreatePivotTable TableDestination:=Range("A4"), TableName:="PivotTable1" However the range can go from R2C1:R3C5 to R2C1:R7000C5 and will be different almost each time. I have tried to take a range that is way too big, but then the Pivot table gives me the wrong results. So what is the best way to select the correct range each time? I have searched with Google for a few days, but came up with nothing. :-( I am working with Excel 2000. Sorry, no option in updating or upgrading. Company policy. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. houghi -- Knock-knock. Who's there? Under the Patriot Act, we don't have to tell you that. -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot and VB scripting range problem
Dave Peterson wrote:
I had a couple of typos (sorry). This compiles, but I didn't test it: Option Explicit <snip This works. Great, thanks. houghi -- Let's not be too tough on our own ignorance. It's the thing that makes America great. If America weren't incomparably ignorant, how could we have tolerated the last eight years? -- Frank Zappa, in 1988 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scripting in Excel 2007 | Excel Discussion (Misc queries) | |||
Scripting from command line | Excel Discussion (Misc queries) | |||
Scripting Question | Setting up and Configuration of Excel | |||
Excel scripting,programming | New Users to Excel | |||
Scripting.FileSystemObject | Excel Discussion (Misc queries) |