Change hardcoded filename

C

CLR

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
 
B

Bob Phillips

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)
 
C

CLR

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 said:
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").CurrentRegion

ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=rng2.Address(1,1,xlR1C1,1), _
TableDestination:=rng1.Address(1,1,xlR1C1,1), _
TableName:="PivotTable1"

--
Regards,
Tom Ogilvy



CLR said:
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
Click to expand...
Click to expand...
 
T

Tom Ogilvy

Activeworkbooks is a typo and should be activeworkbook.

--
Regards,
Tom Ogilvy


CLR said:
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 said:
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").CurrentRegion

ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=rng2.Address(1,1,xlR1C1,1), _
TableDestination:=rng1.Address(1,1,xlR1C1,1), _
TableName:="PivotTable1"

--
Regards,
Tom Ogilvy



CLR said:
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
Click to expand...
Click to expand...
Click to expand...
 
C

CLR

That did the trick!!!
Thanks muchly kind Sir

Vaya con Dios,
Chuck, CABGx3



Tom Ogilvy said:
Activeworkbooks is a typo and should be activeworkbook.

--
Regards,
Tom Ogilvy


CLR said:
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 said:
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").CurrentRegion

ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:=rng2.Address(1,1,xlR1C1,1), _
TableDestination:=rng1.Address(1,1,xlR1C1,1), _
TableName:="PivotTable1"

--
Regards,
Tom Ogilvy



:

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
Click to expand...
Click to expand...
Click to expand...
 
C

CLR

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Format Pivot total rows 2
Pivot Table dynamic range code needed 3
PivotTable Macro not working 2
Invalid Procedure call or argument 1
Please Help! Need to add a Variable Range in my Excel Macro withPivot Tables 3
Named argument not found 1
Pivottable References 1
create pivot table in VBA 4

Top