End Sheet not the last sheet

  • Thread starter Thread starter Microsoft Communities
  • Start date Start date
M

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
 
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.
 
Sub addsheetbeforelastsheet()
Sheets.Add before:=Sheets(Sheets.Count)
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Microsoft Communities said:
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.
Click to expand...
 
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)


Microsoft Communities said:
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.
Click to expand...
 
Or be explicit when you add the newsheet:
Sheets.Add before:=Sheets("EndSheet")

Assuming that EndSheet is at the far right <vbg>.
 
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.
 
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)


Microsoft Communities said:
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.
Click to expand...
 
Back
Top