Worksheet Names

[helpexcel]

Hi - I've been reading a bunch of posts on this and haven't found any that work for me. I have a workbook with a number of sheets. What i would like to happen is what ever value is put in A5 on sheet one will be used to create the worksheet names. So if A5="Red" the worksheets will be named "Red Balloons" "Red Cars" "Red Food". I plan an expanding this code for values in A10 and A20. I'm assuming the code would have to go into each worksheet individually, with a private sub?

 

[helpexcel]

So another thing i didn't consider, How do i reference dynamically names sheets in my code?

Dim wks as worksheet

Set wks = thisworkbook.sheets("??????")

 

[Logit]

.
You can refer to a worksheet by two methods. The Sheet Number and the Sheet Tab Name

If you look in the VBE on the left side, the sheets are listed like this :

Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)

The tab name that you see when viewing the actual sheet is located inside the parenthesis (Sheet1).

If you were to name Sheet1 as ... say ... My Sheet One ... in the VBE it would look like this : Sheet1(My Sheet One)

To refer to this sheet dynamically (in case the name of the tab changes, you want to use the first part before the parenthesis.
The only way this portion of the sheet name will change is if a programmer goes into the VBE and edits the sheet name.

Use the Sheet reference number, not the tab name for dynamically changing sheet names.

Option Explicit


Sub whatcell()
    Sheet1.Range("A1").Value = "BB"
End Sub

If you code also refers to only the sheet that is showing .. and only when it is showing .. you can refer to it as
ActiveSheet.

Sub whatcell2()
    ActiveSheet.Range("A1").Value = "BB"
End Sub

 

[helpexcel]

I follow this, but i don't know how to use the sheet menus to define a sheet in a workbook? I Tried this Set wks = thisworkbook.sheet1 but it didn't work.

 

[Logit]

.

 Sub sht()
 Dim wks As Worksheet
 
 Set wks = Sheets("Sheet2")
 
 wks.Range("B2").Value = "Hi"
 
 End Sub

 

[helpexcel]

Set wks = Sheets("Sheet2") This only works if i have a sheet named "Sheet2" I was wondering how i reference a sheet that has a dynamic name?

 

[Logit]

Option Explicit


Sub sht()
 Dim wks As Worksheet
 
 Set wks = Sheet2
 
 wks.Range("B2").Value = "Hi"
 
 End Sub

 

[helpexcel]

got it! thanks!!

what if i have 2 workbooks open. workbook 1 has the names of the sheets in workbook 2. How would i go about defining that. I tried this, but failed.

Dim wkb as workbook
Dim wks1, wks2 as worksheet
Dim rng as Range

Set wkb = Workbooks("Workbook2")
Set wks1 = Sheet1
Set rng = wks1.Range("A1") 'Sheet Name in workbook2
Set wks2 = wkb.rng

wks2.Range("A1:B10").copy wks1.Range("D1")