incorporate string variable into VB code

[itsthebike]

This is a follow-up to an earlier post. I have provided a few more
details about what I need to do.

I need to find out how to incorporate a string variable (i.e. the
active worksheet's tab name; assume: "active_wksht") directly into my
VB coding. For example, the following code begins to create a Pivot
Table. It references a specific worksheet, i.e. the active worksheet
("9-24-05").

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'9-24-05!R3C1:R200C5").CreatePivotTable TableDestination:= _
"'[Tally.xls]9-24-05'!R3C8", TableName:="PivotTable1", DefaultVersion:=
_
xlPivotTableVersion10


Specifically, I'd like to find out how to incorporate the string
variable into the code, so that it reads, in part, as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"*active_wksht*!R3C1:R200C5").CreatePivotTable TableDestination:= _
"'[Tally.xls]'*active_wksht*!R3C8", TableName:="PivotTable1",
DefaultVersion:= _
xlPivotTableVersion10


This will cause the code to base the pivot table on the data in the
active worksheet without my having to manually edit the code to refer
to the active worksheet. I'd also need some help with the placement
(where needed) of double quotes, apostrophes, etc. to handle the
string, since apostrophes are needed around the worksheet name in the
code.

 

[June Macleod]

You could use ActiveSheet.name and ActiveWorkbook.name.

The syntax would be something like
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
Activesheet.name & "!R3C1:R200C5"