Hi Simon,
In your previous message, you stated the following:
"your short macro simply copies a specific named sheet and renames it to a
specific name". That description is exactly what I want the macro to do (with
the flexibility of the specific worksheet name to be copied, referenced by
the contents of a cell in the worksheet called "Start"). Then, the finished
product of the copied worksheet needs to be renamed as the contents of
another cell reference within the Start worksheet.
I don't understand the VBA that you posted; it is too complicated for me. I
am not sure what the "Target.Value" means in the statement
(Target.Value).Copy and how to assign this "Target.Value". Also, the
statement: ActiveSheet.Name = ActiveSheet.Range("A1").Value renames the
worksheet with a value within that copied worksheet. I want it to reference
the contents of another Sheet "Start" .
Would you be willing to send me some revised VBA code with the changes I'm
suggesting here?
Thank you,
Susan
"Simon Lloyd" wrote:
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