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"



"itsthebike" <[email protected]> wrote
in message news:[email protected]...
>
> 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.
>
>
> --
> itsthebike
> ------------------------------------------------------------------------
> itsthebike's Profile:
http://www.excelforum.com/member.php...o&userid=27425
> View this thread: http://www.excelforum.com/showthread...hreadid=469845
>