Sheet select not working

[Corleone]

I have the code below which works fine, however I would rather change the sheet references in the code to the actual sheet numbers
ie sheet20.select instead of Sheets("132627 Liverpool Lime Street").Select

when I try this it crashes

thanks

Sub LNW_N_SLIDES()
'
' Macro3 Macro
'

'
    Range("V15").Select
    Sheets("LNW N").Select
    ActiveSheet.DrawingObjects.Select
    Selection.Delete
    Range("A1").Select
    Workbooks.Open Filename:= _
        "\\BBPFC01\CitrixData\P3e\IP Signalling\Dashboard Commentary\DASHBOARDS\Project Dashboards LNW N.xlsm" _
        , UpdateLinks:=3
    ActiveWindow.SmallScroll Down:=-30
    Range("A1:Z201").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=48
    Range("A60").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    Sheets("132627 Liverpool Lime Street").Select
    Range("A1:Z201").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=57
    Range("A119").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    Sheets("132357 Weaver to Wavertree").Select
    ActiveWindow.SmallScroll Down:=0
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=51
    Range("A178").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    Sheets("TBC004").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=66
    Range("A237").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    Sheets("TBC005").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=48
    Range("A296").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    Sheets("TBC006").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=72
    Range("A355").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    Sheets("TBC007").Select
    Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Windows("slides.xlsm").Activate
    ActiveSheet.Paste
   
    Range("U1").Select
    Windows("Project Dashboards LNW N.xlsm").Activate
    ActiveWindow.Close False
End Sub

 

[Fluff]

I assume that you are talking about the sheet codename.
If so, you can only directly use them in the workbook containing the macro.

 

[jproffer]

Sheet20.Select should work (provided there IS a sheet20 of course.

On a side note, you don't need to do all that selecting, really. When you see:

(blah blah blah).Select

and the next line is:

Selection.(whatever you want to do to range blah blah blah)

You can combine those to:

Range(blah blah blah).(whatever you want to do)

 

[Fluff]

@jproffer
As Sheets("132627 Liverpool Lime Street") is not in ThisWorkbook, you cannot directly use its codename.

 

[jproffer]

I guess I'd just never tried. That's new to me too...thanks for the education. (continuing education...almost every time I'm on here, I have an "ohhhhhhhh...hmm, well what-a-ya know" moment)

To the OP: Sorry for the mis-information.

 

[Corleone]

I assume that you are talking about the sheet codename.
If so, you can only directly use them in the workbook containing the macro.

Thanks for the responses all - I think this solves it

cheers

 

[Fluff]

One workround if your interested is like

   Dim Ws As Worksheet
   Dim wbk1 As Workbook
   Dim Wbk2 As Workbook
   Dim Dic As Object
   
   Set wbk1 = ThisWorkbook
   Set Dic = CreateObject("scripting.dictionary")
   Set Wbk2 = Workbooks.Open(FileName:= _
      "\\BBPFC01\CitrixData\P3e\IP Signalling\Dashboard Commentary\DASHBOARDS\Project Dashboards LNW N.xlsm" _
      , UpdateLinks:=3)
      
   For Each Ws In Wbk2.Worksheets
      Dic.Add Ws.CodeName, Ws
   Next Ws
   
   'Windows("Project Dashboards LNW N.xlsm").Activate
   '("132627 Liverpool Lime Street")
   Dic("[COLOR=#ff0000]Sheets20[/COLOR]").Range("A1:Z201").CopyPicture Appearance:=xlScreen, format:=xlBitmap
   
   'Windows("Project Dashboards LNW N.xlsm").Activate
   '("132357 Weaver to Wavertree")
   Dic("[COLOR=#ff0000]Sheet21[/COLOR]").CopyPicture Appearance:=xlScreen, format:=xlBitmap

Where the values in red are the codenames for the sheets that I've commented out

 

[Corleone]

ill give it a go and report back - thanks