Refer to Sheets by number

[Un-Do Re-Do]

I have been using the syntax "Sheets(1)" and "Sheets(2)" just in case their worksheet names changes.
The code snippet below copies values from sheet 1 to sheet 2.
I noticed in Excel 2016 that when I add a new sheet, it copies values to this new sheet, not the original "Sheets(2)".
How can it work without specifying the name of the sheet?

Do Until RowNumber = 1000
Sheets(2).Cells(RowNumber, 1) = Sheets(1).Cells(RowNumber, 1)
RowNumber = RowNumber + 1
Loop

[mikerickson]

When you refer to sheets by number, that number is their order in the workbook.
What was Sheets(2) can become Sheets(3) if the user moves them.

It sounds like what you are looking for is to refer to sheets by their code name. This link explains the different ways to refer to a sheet
https://www.mrexcel.com/forum/excel-...-etcetera.html

[Un-Do Re-Do]

Thank you.

[Un-Do Re-Do]

OK, I'm running into problems ... have done research but ... nothing.
See the code below ... I'm trying to find the last row in Sheet2 (this is the VBA name Sheet2, not the tab name "Sheet2").
I now understand that Sheets(2) refers to the second sheet in the order laid out in the workbook. It may have nothing to do with VBA name Sheet2.
So if I insert another sheet, it will refer to the second in order, which may not be VBA Sheet2.
I realize I can refer to it as Sheets("Sheet2") but the tab name may change ... it has to be the VBA name Sheet2.
Obviously Sheets(Sheet2) doesn't work.

Sub test()
    Dim RowLast2 As Long
    RowLast2 = Sheets(2).UsedRange.SpecialCells(xlCellTypeLastCell).Row
    MsgBox RowLast2
End Sub

[xladept]

Well then you'll need to address them by name

[romperstomper]

It’s just:

RowLast2 = Sheet2.UsedRange.SpecialCells(xlCellTypeLastCell).Row

[xladept]

When you add sheets just add them at the end:

Worksheets.add(After:=Worksheets(Worksheets.count))

[Un-Do Re-Do]

Sorry but I do have to rearrange them.

[Un-Do Re-Do]

Was there no other way?
Thanks anyway.

[xladept]

Not if you change the order

[Un-Do Re-Do]

RowLast2 = Sheet2.UsedRange.SpecialCells(xlCellTypeLastCell).Row

No, this is isn't working for me.

[bakerman2]

What do you mean by

this is isn't working for me.

In Post#4 you say yourself that Sheet2 is the sheet's CodeName ( the one NOT between parenthesis)so why wouldn't that work ?

[Un-Do Re-Do]

Sorry but I don't understand the work in post #14.

[Un-Do Re-Do]

OK, now I know why.
I'm calling up the macro from an Add-In.
It appears there is no way around it, not that I can see???

[shg]

Sub UDRD()
  Dim wks           As Worksheet
  Dim wks2          As Worksheet

  For Each wks In ActiveWorkbook.Worksheets
    If wks.CodeName = "Sheet2" Then
      Set wks2 = wks
      Exit For
    End If
  Next wks

  If wks2 Is Nothing Then
    MsgBox "No Sheet2!"
  Else
    wks2.Range("A1").Value = "Bob"
  End If
End Sub

[shg]

It sets the worksheet variable wks2 to the sheet in the active workbook whose codename is Sheet2.

[Un-Do Re-Do]

OK thanks I may get this to work.

[Un-Do Re-Do]

Great it works. Thanks.

[bakerman2]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.