Looking for help to copy sheet name into a cell

[Shannon K]

I'm creating a workbook for colleagues that are not Excel savvy and I'm trying to work within my comfort level (without Macros). I'm trying to copy data from an Input sheet to other sheets. One of the things I would like is to be able to rename 8 of the next 16 sheets by typing a new name in a particular cell on the Input sheet. Is there a simple way for a novice to do this?
Also, I need to copy the name sheets #2 - #10 into a table. Is that possible?

 

[pboltonchina]

Have you tried using 'Paste Special', 'Paste Link'? If you select the cell on the Master Sheet and 'Copy' then on all the other sheets you want to update from that cell select 'Paste Special', 'Paste Link', then whenever you update the Master sheet cell, the linked cells also update.

 

[Shannon K]

Thank you for your help. I've never used the Paste Link before. Yes, that solves some of my headache. Do you know of a way to link the name of a sheet (the tab at the bottom) with a cell on the Master Sheet?

 

[lenze]

To name a sheet the value of a Cell, you will need a macro (A WorkSheet_Change Event). You may be able to place the sheet names in your table with a formula. (Search for Sheet Name in Cell-Numerous post on that), But I would suggest a marco for that too.

lenze

 

[Shannon K]

Thank you for your help. I have soooo much to learn!

 

[lenze]

If you want the code to rename a sheet the value of a cell when that cell is changed, post back and we can give you the code. Just be sure and indicate how many and which sheets you want this to apply to.

lenze

 

[Shannon K]

Yes, that is exactly what I would like to do - rename sheets when cells in the Master Sheet change. The entire workbook is 15 sheets: one is the Master or Input sheet; the next 6 are formatted data tables (with information drawn from the Master); the next 6 are charts to illustrate the data tables; and the rest are summary tables. Ideally, we would like the 6 formatted data table sheets to be renamed with the label given in the Master sheet. I hope that I am explaining this properly.

 

[lenze]

OK Shannon: A few basics. In VBA, sheets can be referred to in 3 ways.
The Sheet Name (that which appears on the Tab)
The Index Number (The position of the Sheet in the workbook)
The Code Name (The name assigned to the Sheet in the VBE)
We will want to use the code name. So look in the Project Explorer in VBE and determine the code names for each of the 6 Data Sheets. They will be like Sheet1, Sheet2 etc and may NOT correspond to the name on the Sheet Tab. It's possible, but unusual, for Sheet1 (Code name) to have Sheet2 on the Tab. Now Select your Master Sheet and identify the cells that correspond to each Data sheet. Rightclick the Sheet Tab and choose "View Code". Enter something like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
On Error Resume Next
Select Case Target.Address
    Case "$A$1" 'Address of cell for Sheet2's(Code Name) Name
         Sheet2.Name = Target
    Case "$A$2" 'Address of cell for Sheet3's(Code Name) Name
         Sheet3.Name = Target
    'etc
    Case Else:
End Select
End Sub

HTH

lenze

 

[Shannon K]

Thank you so much. You are a scholar and a gentleman.