A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using Cell Contents to Select Worksheet with Same Name as Cell Con



 
 
Thread Tools Display Modes
  #11  
Old June 16th, 2009, 07:21 AM posted to microsoft.public.excel.worksheet.functions
Simon Lloyd[_293_]
external usenet poster
 
Posts: 1
Default Using Cell Contents to Select Worksheet with Same Name as Cell Con


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

  #12  
Old June 16th, 2009, 03:10 PM posted to microsoft.public.excel.worksheet.functions
Susan Schmid[_2_]
external usenet poster
 
Posts: 3
Default Using Cell Contents to Select Worksheet with Same Name as Cell

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


  #13  
Old June 16th, 2009, 09:06 PM posted to microsoft.public.excel.worksheet.functions
Simon Lloyd[_294_]
external usenet poster
 
Posts: 1
Default Using Cell Contents to Select Worksheet with Same Name as Cell Con


Susan, no disrespect but i don't think you understand or have tried and
adjusted fully to meet your requirements.

look join our forums and post your workbook with a before and after
look with a short explanation,
join our forums (shown in the link below) it's completely free, if you
do join you will have the opportunity to add attachmnets to your posts
so you can add workbooks to better illustrate your problems and get help
directly with them. Also if you do join please post in this thread (link
found below) so that people who have been following or helping with this
query can continue to do so. [/INFORMATION]
Susan Schmid;382956 Wrote:
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'

(http://www.thecodecage.com/))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon

Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Using Cell Contents to Select Worksheet with Same

Name as Cell Con - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=106281)




--
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

  #14  
Old June 22nd, 2009, 01:32 AM posted to microsoft.public.excel.worksheet.functions
Susan Schmid[_2_]
external usenet poster
 
Posts: 3
Default Using Cell Contents to Select Worksheet with Same Name as Cell

Hi Simon,

I am new to VBA Excel Macros. I shared with you one macro that I wanted to
accomplish out of 4 macros. With your help, I was able to accomplish about
50% of what I wanted to do (2 out of the 4 macros). How do I join the
Microsoft Forum? So that I can post the workbook.

Thanks,
Susan

"Simon Lloyd" wrote:


Susan, no disrespect but i don't think you understand or have tried and
adjusted fully to meet your requirements.

look join our forums and post your workbook with a before and after
look with a short explanation,
join our forums (shown in the link below) it's completely free, if you
do join you will have the opportunity to add attachmnets to your posts
so you can add workbooks to better illustrate your problems and get help
directly with them. Also if you do join please post in this thread (link
found below) so that people who have been following or helping with this
query can continue to do so. [/INFORMATION]
Susan Schmid;382956 Wrote:
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'

(http://www.thecodecage.com/))

------------------------------------------------------------------------
Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon

Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
View this thread: 'Using Cell Contents to Select Worksheet with Same

Name as Cell Con - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=106281)




--
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


  #15  
Old June 22nd, 2009, 04:59 AM posted to microsoft.public.excel.worksheet.functions
Simon Lloyd[_301_]
external usenet poster
 
Posts: 1
Default Using Cell Contents to Select Worksheet with Same Name as Cell Con


Susan Schmid;389068 Wrote:
Hi Simon,

I am new to VBA Excel Macros. I shared with you one macro that I wanted
to
accomplish out of 4 macros. With your help, I was able to accomplish
about
50% of what I wanted to do (2 out of the 4 macros). How do I join the
Microsoft Forum? So that I can post the workbook.

Thanks,
SusanSusan we are not a Microsoft forum, that is, we are not affiliated with

nor employed by Microsoft, our forums are simply help and resources for
Microsoft Office products, to join simply click the link underneath my
name and register, we will be pleased to help you, your posts will also
appear here in the Microsoft newsgroups where other users, experts and
professionas will still be able to give you guidance and help.


--
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.