Replace worksheet name with a variable

  • Thread starter Thread starter monika
  • Start date Start date
M

monika

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:=xlDatabase,
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
 
change

"Raw_Data!R1C1:R884C59").CreatePivotTable

to

RAWDATANAME = "raw_data"
RAWDATANAME & "!R1C1:R884C59").CreatePivotTable
 
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), ...
 
hi..

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

Thanks a lot..

Monika
 
Back
Top