MS Excel Macro Question?

Question

Following is a portion of a macro I created that isn't quite correct:

Sheets("Blank Sheet").Select
Sheets("Blank Sheet").Copy After:=Sheets(4)

This macro will copy the sheet to a specific location. After so many sheets, it will still copy the sheet to that same specific location.

I need the macro to always copy the sheet to the very end of the workbook, no matter how many sheets there are. What is the syntax for this?

Answer 1

Sheets.Count, instead of 4

Answer 2

The first answerer is right, however the code can be simplified.

Sheets("Blank Sheet").Copy After:=Sheets(Sheets.Count)

You don't need your first line of code to copy the sheet.

However, if you are just wanting to add a totally blank sheet it's better to just use:

Sheets.Add After:=Sheets(Sheets.Count)

Another thing that might be good for you to know is that worksheets have both names and codenames. The worksheet name is the name that is on the worksheet tab. The worksheet codename is held in the background and the user doesn't see it. You can see the worksheet codename in the Project window in Visual Basic Editor. You can also find the code name by using the CodeName property. For example:

MsgBox Worksheets(1).CodeName

That would display the code name of the first worksheet in your active workbook. The nice thing about code names is that they don't change when the user changes the tab name. They can only be changed via VBA code or through the Visual Basic Property window. And code names are a little easier to work with too. You don't have to do code like:

Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)

Then worry that the user later changes the tab name. If you call Sheet1's code name "Sam", here's how easy it would be:

Sam.Copy After:=Sheets(Sheets.Count)

You don't have to put codenames in parenthesis and put "Sheets" or "Worksheets" in front of it.