Finding last sheet

  • Thread starter Thread starter RJH
  • Start date Start date
R

RJH

Hello,
I have to following code to create a new sheet as the 'Last' sheet in the
book. In other words, each week I need to create a new sheet by copying the
last sheet and pasting it as the 'new' last sheet. I need to know how to
find the last sheet in the book, copy it and create a new last sheet so that
this will execute week after week without using sheet names. "Sheet 3"
below should be 'last sheet' until the 'new' last sheet is created &
renamed. Then I need to find the 'new' last sheet and clear certain
contents to start the new week with a blank (but formatted) sheet. I know
I'm not doing a very good job of explaining this, my apologies.

Thanks for your help!

Bob Howard


Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Sheets(3)
Sheets("Sheet3 (2)").Select

Dim Message, Default, Title, WeekDate
Message = "Enter date for WEEK ENDING"
Default = "Week Ending ??"
WeekDate = InputBox(Message, Title, Default)

Sheets("Sheet3 (2)").Name = "Week Ending " & WeekDate

Sheets("Week Ending 2-21").Select
Range("C3,D5,E6").Select
Range("E6").Activate
Selection.ClearContents
Range("A1").Select
 
Dim sh as Object
set sh = Sheets(Sheets.count)

sh.copy after:=sh

.. . .

Activesheet.Name = "Week Ending " & WeekDate
 
Back
Top