| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change hardcoded filename
Hi All........
I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change hardcoded filename
Do you mean as the source or as the destination or both. I will assume both
as an example: set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") set rng2 = ActiveWorkbooks.Worksheets("Labor").Range("C9").Cu rrentRegion ActiveSheet.PivotTableWizard _ SourceType:=xlDatabase, _ SourceData:=rng2.Address(1,1,xlR1C1,1), _ TableDestination:=rng1.Address(1,1,xlR1C1,1), _ TableName:="PivotTable1" -- Regards, Tom Ogilvy "CLR" wrote: Hi All........ I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change hardcoded filename
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Labor!R9C3:R173C22", TableDestination:="[" & Activeworkbook.Name & "]Pivots!R18C1", _ TableName:="PivotTable1" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CLR" wrote in message ... Hi All........ I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change hardcoded filename
Thanks Tom, but I'm getting an error message "Run Time Error 424 Object
Required" stoping on the line.... set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") What might I be doing wrong? Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Do you mean as the source or as the destination or both. I will assume both as an example: set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") set rng2 = ActiveWorkbooks.Worksheets("Labor").Range("C9").Cu rrentRegion ActiveSheet.PivotTableWizard _ SourceType:=xlDatabase, _ SourceData:=rng2.Address(1,1,xlR1C1,1), _ TableDestination:=rng1.Address(1,1,xlR1C1,1), _ TableName:="PivotTable1" -- Regards, Tom Ogilvy "CLR" wrote: Hi All........ I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change hardcoded filename
Activeworkbooks is a typo and should be activeworkbook.
-- Regards, Tom Ogilvy "CLR" wrote: Thanks Tom, but I'm getting an error message "Run Time Error 424 Object Required" stoping on the line.... set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") What might I be doing wrong? Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Do you mean as the source or as the destination or both. I will assume both as an example: set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") set rng2 = ActiveWorkbooks.Worksheets("Labor").Range("C9").Cu rrentRegion ActiveSheet.PivotTableWizard _ SourceType:=xlDatabase, _ SourceData:=rng2.Address(1,1,xlR1C1,1), _ TableDestination:=rng1.Address(1,1,xlR1C1,1), _ TableName:="PivotTable1" -- Regards, Tom Ogilvy "CLR" wrote: Hi All........ I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change hardcoded filename
That did the trick!!!
Thanks muchly kind Sir Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Activeworkbooks is a typo and should be activeworkbook. -- Regards, Tom Ogilvy "CLR" wrote: Thanks Tom, but I'm getting an error message "Run Time Error 424 Object Required" stoping on the line.... set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") What might I be doing wrong? Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote: Do you mean as the source or as the destination or both. I will assume both as an example: set rng1 = Activeworkbooks.Worksheets("Pivots").Range("A18") set rng2 = ActiveWorkbooks.Worksheets("Labor").Range("C9").Cu rrentRegion ActiveSheet.PivotTableWizard _ SourceType:=xlDatabase, _ SourceData:=rng2.Address(1,1,xlR1C1,1), _ TableDestination:=rng1.Address(1,1,xlR1C1,1), _ TableName:="PivotTable1" -- Regards, Tom Ogilvy "CLR" wrote: Hi All........ I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Change hardcoded filename
This one worked just fine, once I got over the "word wrap" thing <g.
Thanks for the alternative, Bob. Vaya con Dios, Chuck, CABGx3 "Bob Phillips" wrote: ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[" & Activeworkbook.Name & "]Pivots!R18C1", _ TableName:="PivotTable1" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "CLR" wrote in message ... Hi All........ I have the following code which works fine, but has the Filename hardcoded therein, and whenever I change the filename, the macro quits working. How might the code be changed to accomodate the Activefilename whatever it might be? ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Labor!R9C3:R173C22", TableDestination:="[BigOneReport1j.xls]Pivots!R18C1", _ TableName:="PivotTable1" TIA Vaya con Dios, Chuck, CABGx3 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| use Find without hardcoded value but a value from a cell | Excel Worksheet Functions | |||
| I need to change hardcoded background color | Excel Worksheet Functions | |||
| VBA Code To find Hardcoded Values in Formula | Excel Programming | |||
| change linked cells to black, hardcoded to blue | Excel Programming | |||
| DSUM with Criteria containing non-hardcoded values? | Excel Worksheet Functions | |||
Linear Mode
