Susan Schmid;382062 Wrote:
Hi Simon,
The comments are helpful. Perhaps, I didn't describe the situation as
thoroughly enough.
I created the following macro at work to activate by pressing Ctrl+A
but I
don't want it to be the same value(s) of 200904 (or 200905) because I
am
dealing with monthly reports. I also don't want to manually change it
everytime. So, I created an additional spreadsheet named "Start" where
cell
B3 is the current monthly pull of data 200905 and cell B4 is the
previous
monthly pull of data 200904. Once, the previous month's pull of data
worksheet is copied and renamed to the current month's pull of data. It
is
ready for me to update the spreadsheet with the new info. Each month
the
corresponding worksheet names will change.
Right now, I am in a tough position trying to refer to the contents of
these
cells in this macro. Any thoughts?

' CopyNewMonth Macro
' Macro recorded 5/12/2009 by sschmid to copy the worksheet for the
current
latest month (previous month's pull) into a new worksheet for the month
to be
added (current month's pull).
' Modified 5/17/2009 by sschmid in order to change Sheets(17)to
'Sheets("Benchmark")
'
' Keyboard Shortcut: Ctrl+a
' Update month

Sheets("200904").Copy Befo=Sheets("Benchmark")

'Already selected
'Sheets("200904 (2)").Select

Sheets("200904 (2)").Name = "200905"
End Sub

"Simon Lloyd" wrote:

Susan, my code does what yours does, just assign the keyboard shortcut
to it. Take a look at my code, it works like this, on your Start sheet
you will have some names (or numbers) in column A i.e 200904....etc,
when you click that cell it will copy that particular sheet and rename
it as the contents of A1 of that sheet (that was for illustration
purposes, you can have it named whatever you want from wherever you
want), once the sheet is copied it will put it in a new workbook unless
you remove the ' from this line After:=Sheets(Sheets.Count), if you want
to place the sheet Before simple substitute it for
Befo=Sheets("Benchmark"), after the sheet has been created and
renamed you are taken back to where you started, your short macro simply
copies a specific named sheet and renames it to a specific name.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=106281