Declaring a tab name as a variable

[Guest]

Can anyone help me adjust the following code to work properly. I'm getting
errors with the TabName variable. I'm trying to set the variable to the tab
name of the file that will be opened.

Sub FormatFiles()

Dim TabNAME As String
Dim WB As Workbook
Do
Set WB = Workbooks.Open(Filename:=ActiveCell)
Set TabName = ActiveSheet.Name

My Macro code - (This will be my own simple code to format the WB
workbook that was just opened)

Workbooks("Master file").Activate
Sheets("TabNAME").Range("B5:B102").FormulaR1C1 .... (This formula is a
vlookup formula the WB file.)


WB.Close True 'Save Changes
ActiveCell.Offset(1).Select
Loop Until ActiveCell = ""
End Sub


Any help would be appreciated

 

[Jim Cone]

"Set" is used for objects only.
Change Set TabName = ActiveSheet.Name
-to-
TabName = ActiveSheet.Name
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"timmulla" <[email protected]>
wrote in message
Can anyone help me adjust the following code to work properly. I'm getting
errors with the TabName variable. I'm trying to set the variable to the tab
name of the file that will be opened.

Sub FormatFiles()
Dim TabNAME As String
Dim WB As Workbook
Do
Set WB = Workbooks.Open(Filename:=ActiveCell)
Set TabName = ActiveSheet.Name

My Macro code - (This will be my own simple code to format the WB
workbook that was just opened)

Workbooks("Master file").Activate
Sheets("TabNAME").Range("B5:B102").FormulaR1C1 .... (This formula is a
vlookup formula the WB file.)

WB.Close True 'Save Changes
ActiveCell.Offset(1).Select
Loop Until ActiveCell = ""
End Sub


Any help would be appreciated

 

[Guest]

You also need to remove the quotation marks from your variable when it is
used as a sheet name.

i.e. Sheets(TabNAME).Range("B5:B102").FormulaR1C1