Declaring a tab name as a variable

T

timmulla

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
 
J

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
 
J

JNW

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top