Referencing Sheet Code name

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