PDA

View Full Version : I need help referring to last and first sheets



Foxtrot
03-14-2009, 05:25 AM
Good morning.

I'm trying to produce a macro that will insert a new worksheet, move that to the end of the list, then copy and paste a form from the first sheet onto the new sheet. Finally I want it to rename the new sheet with today's date.

Is all this possible?

Here's a macro I've recorded so far:

Sub Macro5()
Sheets("Sheet1").Select
Sheets.Add
Sheets("Sheet6").Select
Sheets("Sheet6").Move After:=Sheets(4)
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Sheets("Sheet6").Select
ActiveSheet.Paste
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "3-14"
End Sub

The problem is, I repeat this process every day so (as I understand it) I need to be able to refer to "new sheet", "last sheet", and "first sheet".

can anyone help?
:doh:

mdmackillop
03-14-2009, 05:41 AM
Option Explicit
Sub FirstAndLast()
Dim ws As Worksheet
On Error GoTo Exits
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = Format(Date, "dd-mm")
Sheets(1).Cells.Copy ws.Cells(1, 1)
Exits:
MsgBox Err.Description
End Sub

Foxtrot
03-14-2009, 05:54 AM
awesome,
thanks mdmackillop. That does the job perfectly.

mdmackillop
03-14-2009, 05:58 AM
Happy to assist. If your question is Solved, please mark it so using the Thread Tools dropddown.
Regards
MD