Excel Macros copying non-sequential items from one tab to another, i need simple code to return to 1st sheet?

Question

Excel Macros copying non-sequential items from one tab to another, i need simple code to return to 1st sheet?

Answer 1

I don't think I'd want Danny W as my programmer.

That is really going to depend on what you are terming as the "1st" sheet.

If you are referring to the leftmost sheet on the tab list then you want to use the code:

Sheets(1).Select

That code will take you to whatever sheet is the leftmost tab.

If you want to go to a specific sheet, it is better to use the name because people can rearrange the sheet order and then you are up a creek.

If you go by the sheet name and the sheet is called Sheet2, then you could type:

Sheets("Sheet2").Select

However that still presents a possible problem, because what if someone decides to change the sheet name by changing the name on the tab.  Then you are up a creek again.

Here's how you solve it.

You use the worksheet's code name.  Each worksheet has a code name that the user can't change unless they know VBA macro code and unless you leave your coding unlocked.

When you edit your macro in Microsoft Visual Basic Editor.  Up in the left hand corner should be the Project window.  You should see the list of worksheets in that project window.  The name of the worksheet on the tab will be in parenthesis and the code name will be in front of the parenthesis.  So lets use an example.  Let's say I open a new workbook and then go to Visual Basic Editor (Alt + F11).

You should see for the worksheets:

Sheet1(Sheet1)

Sheet2(Sheet2)

Sheet3(Sheet3)

Now if you go back to the workbook and change the tab for Sheet3 and name it John instead.  It should now look like this in the Project window.

Sheet1(Sheet1)

Sheet2(Sheet2)

Sheet3(John)

You'll notice that the codename doesn't change.

So if you wanted to go to the worksheet you called John and still have it go to that worksheet even if someone renames it Tim you could use:

Sheet3.Select

Answer 2

Sheets("Sheet1").Select