refer to sheet code name instead of tab name?

[cterjesen]

Can i refer to a sheet's code name when using VBA to import into another file? The tab names could be changed so I want to use something static. The index could change as well if the order of the tabs in the other file is changed.

Here's my code to import:

Sub OpenFORECASTfile()

'OPENS PRIOR YEAR REFORECAST FILE

On Error GoTo ErrorHandler

Dim myFile2 As Variant, wbkFOR As Workbook

myFile2 = Application.GetOpenFilename("All Files,*.*")
Application.DisplayAlerts = False
Set wbkFOR = wbkFOR
Application.DisplayAlerts = True
Set wbkFOR = Workbooks.Open(Filename:=myFile2)

Application.EnableEvents = False

'GO GETS REFORECAST DATA

'On Error GoTo ErrorHandler
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ThisWorkbook.Sheets("RFUPLOAD").Activate
Module1.UnProtectIt


'COPY/PASTE DATA INTO NEW FILE

'Golf
wbkFOR.Sheet23.Range("B1:BJ225").Copy
ThisWorkbook.Sheets("RFUPLOAD").Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

It gets hung up on wbkFOR.Sheet23.Range("B1:BJ225").Copy. The error says "Object doesn't support this property or method."

Thanks!

 

[Hermanito]

you can refer to sheets in the workbook that contains the code, by just using the codename, so
ThisWorkbook.Sheets("RFUPLOAD").Range("B7").PasteSpecial
is equivalent to
shtRfUpload.Range("B7").PasteSpecial
assuming that that is the correct codename for that sheet of course.

I'm not sure you can directly refer to codenames of sheets in other workbooks (other than the one running the code).

 

[GTO]

You cannot refer to the other workbook's worksheets by their codename, like Sheet23.Range(etc...

As you know the other workbook's worksheets' codenames however, you can set references by getting the codenames string values, if that makes sense.

By example, assume Test.xls has six sheets, codenames: sht1, sht2...sht6, and thta Test.xls resides in the same folder:

Option Explicit
    
Sub exa()
Dim wb As Workbook
Dim wks1 As Worksheet, wks3 As Worksheet, wks4 As Worksheet
    
Dim wks As Worksheet
    
    
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\Test.xls")
    
    For Each wks In wb.Worksheets
        Select Case wks.CodeName
        Case "sht1"
            Set wks1 = wks
        Case "sht3"
            Set wks3 = wks
        Case "sht4"
            Set wks4 = wks
        End Select
    Next
    
    Set wks = ThisWorkbook.Worksheets(1)
    
    If Not wks1 Is Nothing Then
        wks.Cells(1).Value = wks1.Cells(5, "A").Value
    End If
    
    If Not wks3 Is Nothing Then
        wks.Cells(2, "A").Value = wks3.Cells(5, "A").Value
    End If
    
    If Not wks4 Is Nothing Then
        wks.Cells(3, "A").Value = wks4.Cells(5, "A").Value
    End If
    
    wb.Close False
End Sub

Hope that helps,

Mark

 

[RoryA]

FWIW, if you have access to the VBProject trusted, you can also just use:

   Dim strCodeName As String
   strCodeName = "test"
   MsgBox Workbooks("Book2").VBProject.VBComponents(strCodeName).Properties("Name")

for example.