Macro to copy last worksheet & insert new sheet

[dolphinv4]

Hi,

I have an excel file that is supposed to have the various
months. I did a macro that copies the last worksheet (ie,
April) and inserts a new worksheet (ie, April (2)) with
all the formulas & format of worksheet "April".

My question is:

1) how can I amend the VB Code such that the macro will
copy the last worksheet showing the last month instead of
keep copying from "April'"?

2) is it possible to have the new duplicated worksheet
to automatic detect what is the last month and
automatically name the new worksheet as the new month?

3) I have a cell in "April" that references a cell in
the previous month's sheet. How can I make sure that the
same cell in the NEW sheet copies from the cell in the
last month instead of following my sheet "April"?

My current VB Code looks like this:

Sub Worksheet()
'
' Worksheet Macro
' Macro recorded 29/04/2004 by Valerie Ho
'
' Keyboard Shortcut: Ctrl+w
'
Sheets("Apr'04").Select
Sheets("Apr'04").Copy Before:=Sheets(14)
Range("A7:E77").Select
Selection.ClearContents
Range("G8:G76").Select
Selection.ClearContents
Range("F10").Select
Selection.AutoFill Destination:=Range("F10:F76"),
Type:=xlFillDefault
Range("F10:F76").Select
With Range("D81")
.Value = DateSerial(Year(.Value), Month(.Value) +
2, 0)
End With


End Sub



THANKS a lot to you guys/gals out there in advance!

Val

 

[Earl Kiosterud]

Val,

If the month's worksheets will contain the same tables, you should seriously
consider putting all the data in one sheet with a column for month (or
better yet, date). Spreading similar data over multiple sheets precludes
the use of a lot of good Excel tools. You'll almost certainly run into this
as you develop your app. And you won't have to do this macro either.

 

[dolphinv4]

Hi,

but I can't put all things into a single sheet. mainly
coz we have to show the ending balance for each mth n do
some analysis of the ending balance. So have to do
separate sheets for diff mths.

Val