Referencing Sheet Code name

Hi All,

I have a regular excel extract that im obtaining, however the sheet name is never the same, it is a reference of the system run number "report1442310827144"

Im wanting to reference the sheet and from what i have seen in forums is that you can use the sheet code name. In this case it is "sheet1".

However when i go to reference the sheet code name, i seem to have an issue

My original code is below:-

With Workbooks(fname).Worksheets("report1442310827144")

            Set rngeSource = .Range("B19", .Range("D" & Rows.Count).End(xlUp).Offset(-12))
        End With

Any help would be greatly appreciated!

Originally Posted by TMS

?sheet1.CodeName
Sheet1
?sheets("Sheet1").codename
Sheet1
?sheets(1).codename
Sheet1

Regards, TMS

Thanks TMS, however when i run the code and replace the Worksheets("report1442310827144") with sheet1.codename i get an error message '438' Object doesnt support this property or method.

Is there something i am doing wrong?

Cheers,
RJNC

Originally Posted by TMS

?sheet4.codename
Sheet4
?sheet4.name
report1442310827144
?sheets(sheet4.name).name
report1442310827144
?sheets(4).name
report1442310827144

It all depends what you know about the structure of the workbook and what the sheet names, and code names might be.

You can loop through the sheets to determine what sheet names are present. For example:

for i = 1 to worksheets.Count: debug.Print sheets(i).codename, sheets(i).name: next i
Sheet1        Sheet1
Sheet2        Sheet2
Sheet3        Sheet3
Sheet4        report1442310827144

Regards, TMS

Thanks TMS for your help on this one, the full code is below apologies it would have probably been easier to do that at the get go to understand the structure. I am referencing a sheet in another workbook. Again any help would be really appreciated, I just cant seem to get it right.

Dim rngeDestination As Range, rngeSource As Range
Dim i As Long

Set rngeDestination = ThisWorkbook.Worksheets("Data").Range("C" & Rows.Count).End(xlUp).Offset(1)

Workbooks.Open Filename:= _
"G:\Report Sept15.xlsx"
fname = ActiveWorkbook.Name

With Workbooks(fname). Worksheets("report1442310827144")

            Set rngeSource = .Range("B19", .Range("D" & Rows.Count).End(xlUp).Offset(-12))
        End With

    rngeDestination.Resize(rngeSource.Rows.Count, 3).Value = rngeSource.Value
                    
        rngeDestination.Offset(, -1).Resize(rngeSource.Rows.Count).Value = Application.Max(rngeDestination.Parent.Range("B:B")) + 1
                
Workbooks("Report Sept15.xlsx").Activate
ActiveWindow.Close False

End Sub

Originally Posted by romperstomper

The code must also be in the same workbook to use the codename directly, or you must set a reference to the target workbook from the code workbook. You can also loop through all the sheets and check the codename form another workbook.

Thanks reomperstomper, that is the issue then, im referencing a workbook that i require the codename from, is it achievable then? This is my full code.

Dim rngeDestination As Range, rngeSource As Range
Dim i As Long

Set rngeDestination = ThisWorkbook.Worksheets("Data").Range("C" & Rows.Count).End(xlUp).Offset(1)

Workbooks.Open Filename:= _
"G:\Report Sept15.xlsx"
fname = ActiveWorkbook.Name

With Workbooks(fname). Worksheets("report1442310827144")

            Set rngeSource = .Range("B19", .Range("D" & Rows.Count).End(xlUp).Offset(-12))
        End With

    rngeDestination.Resize(rngeSource.Rows.Count, 3).Value = rngeSource.Value
                    
        rngeDestination.Offset(, -1).Resize(rngeSource.Rows.Count).Value = Application.Max(rngeDestination.Parent.Range("B:B")) + 1
                
Workbooks("Report Sept15.xlsx").Activate
ActiveWindow.Close False

End Sub

Originally Posted by TMS

What is the structure of the workbook you are opening? How many worksheets are there? If it's just one, you can use Sheets(1) without needing to refer to the sheet name or sheet code name. If there's more than one, you can loop through them checking the sheet name, or part of the sheet name. What does the sheet name look like? Is there any part uniquely identifiable?

Perfect! Thanks for the solution and the assistance TMS, works well.