End Sheet not the last sheet

[Microsoft Communities]

Is there a way to move a sheet so that is the last sheet in a workbook.
I have a sheet named "EndSheet" which in some cases may not be the end
sheet. I would like to move it so that it is the last sheet in the workbook.

Thanks in advance
Ed Davis

 

[Dave Peterson]

Can't you just click on that sheet tab and drag it to the right?

 

[Microsoft Communities]

I would but I have a macro that copies a sheet to a new one everyday and
wanted to do it in the same macro as the system adds all the sheets between
"StartSheet" and "EndSheet". When my copy macro adds a sheet it puts it on
the end of the workbook.

 

[Don Guillett]

Sub addsheetbeforelastsheet()
Sheets.Add before:=Sheets(Sheets.Count)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I would but I have a macro that copies a sheet to a new one everyday and
wanted to do it in the same macro as the system adds all the sheets between
"StartSheet" and "EndSheet". When my copy macro adds a sheet it puts it on
the end of the workbook.

 

[Rick Rothstein]

Don's method is probably the best approach (once EndSheet is at the end,
don't Insert any sheets after it). However, in case your users get "frisky"
and move things around on you, this line of code will make EndSheet the last
sheet...

Worksheets("EndSheet").Move After:=Worksheets(Worksheets.Count)

--
Rick (MVP - Excel)

I would but I have a macro that copies a sheet to a new one everyday and
wanted to do it in the same macro as the system adds all the sheets between
"StartSheet" and "EndSheet". When my copy macro adds a sheet it puts it on
the end of the workbook.

 

[Dave Peterson]

Or be explicit when you add the newsheet:
Sheets.Add before:=Sheets("EndSheet")

Assuming that EndSheet is at the far right <vbg>.

 

[Microsoft Communities]

I have decided to use the following which I think will do the same thing.
The only change I had to make was put the -1 on the end.
It seems to work fine in inserts the new sheet just before the last sheet.

Sheets("Daily").Copy After:=Sheets(Sheets.Count - 1)

Thank you everyone for your help.

 

[Rick Rothstein]

Of course, using that assumes no one has (whether intentionally or by
accident) manually moved the EndSheet from where your code is assuming it to
be.

--
Rick (MVP - Excel)

I have decided to use the following which I think will do the same thing.
The only change I had to make was put the -1 on the end.
It seems to work fine in inserts the new sheet just before the last sheet.

Sheets("Daily").Copy After:=Sheets(Sheets.Count - 1)

Thank you everyone for your help.