| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Cell Contents to Select Worksheet with Same Name as Cell Con
I am a quantitative analyst who is the leader of our department's Excel users
group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell Con
Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell Con
hiya
two comments 1) have a look at and a play with the INDIRECT formula from the excel help. (you can have in Cell A1 a text saying " sheet2!A4 ". And if you use =INDIRECT(A1) excel will return the value from sheet 2 cell A4. I think this might be what you mean. 2) If you really mean activating sheets, it needs to be done with code like this. Sub allllen() Sheets(Range("B2").Value).Activate End Sub -- Allllen "Alan Sexter" wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell Con
hiya
two comments 1) have a look at and a play with the INDIRECT formula from the excel help. (you can have in Cell A1 a text saying " sheet2!A4 ". And if you use =INDIRECT(A1) excel will return the value from sheet 2 cell A4. I think this might be what you mean. 2) If you really mean activating sheets, it needs to be done with code like this. Sub allllen() Sheets(Range("B2").Value).Activate End Sub -- Allllen -- Allllen "Alan Sexter" wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell
Simon Lloyd,
I just talked with my friend from Chicago, and we would both prefer VBA. Thanks for responding - I look forward to your upcoming response! Alan Sexter "Simon Lloyd" wrote: Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell
Hi,
1. Let's suppose that cell A1 of Sheet2 contain the myData, then in cell A1 of Sheet1 enter the formula =Sheet2!A1. This cell will now display "myData". Now select cell A1 on Sheet1 and press Ctrl+[ This will take you to Sheet2!A1. No VBA necessary. 2. If you like VBA here are two solutions. Name the range A1 on Sheet2 Data (just to be different) - Insert, Name, Define. In cell A1 of Sheet1 enter the text Data. Now add the following macro to the Sheet1 object: A] Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True If Not Intersect(Target, [A1]) Is Nothing Then Application.Goto Reference:=Target.Value End If End Sub In this case you are moved to the named range when you double click the name in cell A1. You can use many other events to trigger the macro. B] Sub myMove() Application.Goto Reference:=Sheet1.[A1].Value End Sub This macro goes into a regular VBA module. Assign a shortcut key to this macro and it can be executed fro any location within the workbook. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Alan Sexter" wrote: Simon Lloyd, I just talked with my friend from Chicago, and we would both prefer VBA. Thanks for responding - I look forward to your upcoming response! Alan Sexter "Simon Lloyd" wrote: Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell
Hi Simon,
I am the former co-worker of Al with the question. I reviewed your answers. The 1st answer that is not using VBA maybe be misunderstood. I want to use the contents of a cell i.e, 200904 to be used to find the worksheet name 200904 to be selected. Then, copy this '200904' sheet and rename this copied worksheet using another cell's contents, i.e., 200905. Thanks, Susan "Simon Lloyd" wrote: Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell
This message should be directed to Shane. Susan needs to use VBA in order for
this function (or set of tasks) to be run in a macro. Thanks! Alan Sexter "Susan Schmid" wrote: Hi Simon, I am the former co-worker of Al with the question. I reviewed your answers. The 1st answer that is not using VBA maybe be misunderstood. I want to use the contents of a cell i.e, 200904 to be used to find the worksheet name 200904 to be selected. Then, copy this '200904' sheet and rename this copied worksheet using another cell's contents, i.e., 200905. Thanks, Susan "Simon Lloyd" wrote: Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell Con
This code goes in the worksheet code module that you want to click the cells in, i have commented the code so you understand it, it can probably be done a lot smarter but this works and you can understand it: Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Sh As Worksheet 'don't show changes until operation complete Application.ScreenUpdating = False 'if more than one cell selected exit sub If Target.Cells.Count 1 Then Exit Sub 'if we are not clicking in column 1 exit sub If Target.Column < 1 Then Exit Sub 'On Error GoTo Nxt 'to leave the copy in same workbook remove ' before "After" Sheets(Target.Value).Copy After:=Sheets(Sheets.Count) 'check if sheet exists For Each Sh In Sheets If Sh.Name = ActiveSheet.Range("A1").Value Then 'don't show alerts for deletion Application.DisplayAlerts = False ActiveSheet.Delete 'turn alerts back on Application.DisplayAlerts = True GoTo Nxt1 End If Next Sh 'rename the copied sheet to the contents of A1 of the new sheet ActiveSheet.Name = ActiveSheet.Range("A1").Value GoTo SubEnd Nxt1: MsgBox "Sheet already exists, it will not be created!" GoTo SubEnd Nxt: MsgBox "No sheet found of that name" SubEnd: 'show changes Application.ScreenUpdating = False 'back to original sheet Me.Activate End Sub -------------------- *How to Save a Worksheet Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab* for the Worksheet the macro will run on. 3. *Left Click* on *View Code* in the pop up menu. 4. *Paste* the macro code using *CTRL+V* 5. Make any custom changes to the macro if needed at this time. 6. *Save* the macro in your Workbook using *CTRL+S* Susan Schmid;381016 Wrote: Hi Simon, I am the former co-worker of Al with the question. I reviewed your answers. The 1st answer that is not using VBA maybe be misunderstood. I want to use the contents of a cell i.e, 200904 to be used to find the worksheet name 200904 to be selected. Then, copy this '200904' sheet and rename this copied worksheet using another cell's contents, i.e., 200905. Thanks, Susan "Simon Lloyd" wrote: Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Cell Contents to Select Worksheet with Same Name as Cell
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: This code goes in the worksheet code module that you want to click the cells in, i have commented the code so you understand it, it can probably be done a lot smarter but this works and you can understand it: Code: -------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Sh As Worksheet 'don't show changes until operation complete Application.ScreenUpdating = False 'if more than one cell selected exit sub If Target.Cells.Count 1 Then Exit Sub 'if we are not clicking in column 1 exit sub If Target.Column < 1 Then Exit Sub 'On Error GoTo Nxt 'to leave the copy in same workbook remove ' before "After" Sheets(Target.Value).Copy After:=Sheets(Sheets.Count) 'check if sheet exists For Each Sh In Sheets If Sh.Name = ActiveSheet.Range("A1").Value Then 'don't show alerts for deletion Application.DisplayAlerts = False ActiveSheet.Delete 'turn alerts back on Application.DisplayAlerts = True GoTo Nxt1 End If Next Sh 'rename the copied sheet to the contents of A1 of the new sheet ActiveSheet.Name = ActiveSheet.Range("A1").Value GoTo SubEnd Nxt1: MsgBox "Sheet already exists, it will not be created!" GoTo SubEnd Nxt: MsgBox "No sheet found of that name" SubEnd: 'show changes Application.ScreenUpdating = False 'back to original sheet Me.Activate End Sub -------------------- *How to Save a Worksheet Event Macro* 1. *Copy* the macro using *CTRL+C* keys. 2. Open your Workbook and *Right Click* on the *Worksheet's Name Tab* for the Worksheet the macro will run on. 3. *Left Click* on *View Code* in the pop up menu. 4. *Paste* the macro code using *CTRL+V* 5. Make any custom changes to the macro if needed at this time. 6. *Save* the macro in your Workbook using *CTRL+S* Susan Schmid;381016 Wrote: Hi Simon, I am the former co-worker of Al with the question. I reviewed your answers. The 1st answer that is not using VBA maybe be misunderstood. I want to use the contents of a cell i.e, 200904 to be used to find the worksheet name 200904 to be selected. Then, copy this '200904' sheet and rename this copied worksheet using another cell's contents, i.e., 200905. Thanks, Susan "Simon Lloyd" wrote: Alan Sexter;379826 Wrote: I am a quantitative analyst who is the leader of our department's Excel users group (Multi-Channel Marketing department of Eastbay, Inc. in Wausau, Wisconsin). I have a friend who is a former co-worker in my department, but is now a senior credit analyst with a company in Chicago. If possible, this credit analyst would like to have Excel take the contents of a cell in the workbook, and use it to select a worksheet (in that same workbook) that has the same name as the contents or value of that cell. This seems to be a "twist" on the topic of automated worksheet names that has generated several postings. Any help will be greatly appreciated by both of us. Thanks for any help that can be provided!If it is going to be a static name then it can be a hyperlink, otherwise it would have to be done with VBA........which would you prefer? -- 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use of the Contents of a cell as a worksheet name in a function | Excel Worksheet Functions | |||
| I cannot select a single cell or pull down cell contents | Excel Worksheet Functions | |||
| How do I select & use contents of first non-blank cell in row? | Excel Worksheet Functions | |||
| Reflect cell contents into another worksheet | Excel Discussion (Misc queries) | |||
| Reflect cell contents into another worksheet | Excel Discussion (Misc queries) | |||
Linear Mode
