Replace worksheet name with a variable
 

I want to use a worksheetname variable rather than the
hardcoded
worksheetname name, while creating a pivot table...

Please See the code below:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Raw_Data!R1C1:R884C59").CreatePivotTable
TableDestination:="",
TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True


Instead of raw_data I want to use RAWDATANAME variable....
How to replace
it?

Thanks in advance
monika

Replace worksheet name with a variable
 

change

"Raw_Data!R1C1:R884C59").CreatePivotTable

to

RAWDATANAME = "raw_data"
RAWDATANAME & "!R1C1:R884C59").CreatePivotTable


---
Message posted from http://www.ExcelForum.com/

Replace worksheet name with a variable
 

Dim wks as worksheet
Dim Rng as range

set wks = worksheets("raw_data")
with wks
set rng = .range("a1",.cells(884,59))
end with

.....sourcedata:=rng.address(external:=true), ...



monika wrote:

I want to use a worksheetname variable rather than the
hardcoded
worksheetname name, while creating a pivot table...

Please See the code below:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Raw_Data!R1C1:R884C59").CreatePivotTable
TableDestination:="",
TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True

Instead of raw_data I want to use RAWDATANAME variable....
How to replace
it?

Thanks in advance
monika

--

Dave Peterson

Replace worksheet name with a variable
 

hi..

i found & solution quite simple....i just implemented and its working
nicely.

Thanks a lot..

Monika
"Dave Peterson" wrote in message
...
Dim wks as worksheet
Dim Rng as range

set wks = worksheets("raw_data")
with wks
set rng = .range("a1",.cells(884,59))
end with

....sourcedata:=rng.address(external:=true), ...



monika wrote:

I want to use a worksheetname variable rather than the
hardcoded
worksheetname name, while creating a pivot table...

Please See the code below:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Raw_Data!R1C1:R884C59").CreatePivotTable
TableDestination:="",
TableName:= _
"PivotTable5", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True

Instead of raw_data I want to use RAWDATANAME variable....
How to replace
it?

Thanks in advance
monika

--

Dave Peterson