Select First And Last Worksheet in a Workbook

I'm trying to write a macro that will select the last sheet, and copy its contents into a new sheet (to go at the end of the workbook). The Macro will also delete the first sheet in the workbook.

I can't seem to find code for selecting the first and last sheet in a series. I cannot use the sheet's name as the position of the sheets change every week.

I've been trying for ages but just can't get excel to understand what I'm trying to do. If anyone can help, I'd really appreciate it.

Chris

Hello chrismann85,

You can use a single line of code without declaration...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

The code I have at the moment is:

Please Login or Register  to view this content.

I wanted to copy the last worksheet and move things around in it but it copies and pastes all of the 52 worksheets, where am i going wrong?

Thanks for your reply Roy, I can't create a template because the information is used from the last worksheet and this is updated every week. This is why I needed to copy the last worksheet, delete the first column, move the other columns back to enter data in the new column on the right hand side. I hope that makes sense.

Thats works great, i got rid of that "clear selection", I'm not sure why it was there myself.

Is there any way of naming the sheet as 'WEEK... followed by the number put into the input box'? (where it asks for the week number)

Thanks for all your help,

Chris