incorporate variable into VB code

Hi ItsTheBike,

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

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

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


---
Regards,
Norman



"itsthebike" <itsthebike.1voxqc_1127275518.43@excelforum-nospam.com> wrote
in message news:itsthebike.1voxqc_1127275518.43@excelforum-nospam.com...
>
> 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.
>
>
> --
> itsthebike
> ------------------------------------------------------------------------
> itsthebike's Profile:
> http://www.excelforum.com/member.php...o&userid=27425
> View this thread: http://www.excelforum.com/showthread...hreadid=469406
>

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.