incorporate variable into VB code

[itsthebike]

Need some help with proper VB syntax, please. I regularly create a Pivot
Table that is created from a worksheet which has been copied from the
previous week's worksheet. After copying the previous week's worksheet,
I give it a new tab name, then create the Pivot Table, from new date
entered into the worksheet.

1. I'd like to assign the new worksheet tab name to a variable. Does
the assignment statement include "wrksht.name"? What is the proper
syntax to assign the current worksheet's tab name to a variable?

2. Then, since creating a Pivot Table requires the specific worksheet
tab name be included in the VB code itself, I'd like to plug the value
of that variable (the current worksheet's tab name) into a line of VB
code that I use to create the Pivot Table, so the Pivot Table will use
the data in the current worksheet, and not the data from the previous
week. How does that literal get inserted into the VB code, so I won't
need to go into the VB Editor regularly to update the VB code itself
manually, to reflect the current worksheet's tab name.

Thanks for your help.

 

[Norman Jones]

Hi ItsTheBike,

'=================>>
Sub TryIt()
Dim sStr As String

Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)
sStr = ActiveSheet.Name

End Sub
'<<=================

 

[itsthebike]

Thanks for the reply, Norman. Now I have question 1 answered. With
regard to question 2, I still need to find out how to incorporate the
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 the Pivot Table. It references a specific
worksheet, i.e. the active worksheet ("9-24-05").

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, 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:=xlDatabase, 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.

Thanks again.