Macro
 

Hi

I have created a simple macro to perform a task to put some data into pivot
table. The only problem I have with this is that before I run the macro I
will always have to change the Sheet Tab parameters to exactly whats on the
macro ('Gqs 29')see below. Is it possible for me to change this so it works
on any spreadsheet and any tab without me having to always rename the tab Gqs
29 before the macro works?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Gqs 29'!R21C2:R65536C8").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable2"

Macro
 

Maybe

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & Activehseet.Name & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="", _
TableName:="PivotTable2"

---
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Nbiggss" wrote in message
...
Hi

I have created a simple macro to perform a task to put some data into
pivot
table. The only problem I have with this is that before I run the macro I
will always have to change the Sheet Tab parameters to exactly whats on
the
macro ('Gqs 29')see below. Is it possible for me to change this so it
works
on any spreadsheet and any tab without me having to always rename the tab
Gqs
29 before the macro works?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Gqs 29'!R21C2:R65536C8").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable2"

Macro
 

sName = Sh.Name
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & sName & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="",
TableName _
:="PivotTable2"

--
Regards,
Tom Ogilvy



"Nbiggss" wrote:

Hi

I have created a simple macro to perform a task to put some data into pivot
table. The only problem I have with this is that before I run the macro I
will always have to change the Sheet Tab parameters to exactly whats on the
macro ('Gqs 29')see below. Is it possible for me to change this so it works
on any spreadsheet and any tab without me having to always rename the tab Gqs
29 before the macro works?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Gqs 29'!R21C2:R65536C8").CreatePivotTable TableDestination:="",
TableName _
:="PivotTable2"

Macro
 

NG wrap-around

sName = Sh.Name
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & sName & "'!R21C2:R65536C8").CreatePivotTable TableDestination:="",
_
TableName:="PivotTable2"


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Nbiggss" wrote in message
...
Thanks for your reply

I have tried your suggestion but I seem to be encountering trouble at the
point below

TableDestination:="",
TableName _
:="PivotTable2"

"Tom Ogilvy" wrote:

sName = Sh.Name
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'" & sName & "'!R21C2:R65536C8").CreatePivotTable
TableDestination:="",
TableName _
:="PivotTable2"

--
Regards,
Tom Ogilvy



"Nbiggss" wrote:

Hi

I have created a simple macro to perform a task to put some data into
pivot
table. The only problem I have with this is that before I run the
macro I
will always have to change the Sheet Tab parameters to exactly whats
on the
macro ('Gqs 29')see below. Is it possible for me to change this so it
works
on any spreadsheet and any tab without me having to always rename the
tab Gqs
29 before the macro works?

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Gqs 29'!R21C2:R65536C8").CreatePivotTable
TableDestination:="",
TableName _
:="PivotTable2"