Tab Names in Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a macro set up that selects each worksheet and transfers data to each
one. The macro selects the worksheet by name, so if I change a worksheet
name, the macro will not run.

I don't know much about arrays or dim'ing but I know this can be done.

What I would really, really like is a macro to name all worksheets from a
list on one worksheet. Then I would like to run another macro to transfer
the data to each sheet no matter what the sheet tab is named.

I hope I made this clear enough!

Thanks,
Cheri
 
If you're really updating all the worksheets in a workbook, you can loop through
them without knowing their names.

dim Wks as worksheet
for each wks in activeworkbook.worksheets
'code to do the work against wks here
next wks
 
Several ways to go about this.

In your code don't address the sheets by name or position.

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'do your tranfer stuff
Next ws

If you did want to rename all the sheets(not necessary if code above used) this
macro can do it.

Sub NameWS()
'name sheets from a unique list in Column A on first sheet
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub


Gord Dibben MS Excel MVP
 
Okay, adding on what I was doing since this did not work for me. My macro
selects the "Stats" worksheet and copies a range. It then selects a number
of tabs (can be different amounts depending on data copied). So, I may
select tabs 3-10 one time and 3-16 tabs another time depending on how many
tabs are in the particular workbook. (Tabs 1 and 2 would never be selected).
This way, I can copy the data to all subsequent sheets at one time. Is this
possible, or do I have to do each tab separately, using your code?

I appreciate your help!!!
 
Thanks Dave! I am wondering how I would do this without selecting tabs 1 and
2 as these would never be included in the transfer (since the data is
transfering from one of them)

I appreciate you help! Thanks!!
 
Geesh...I put my reply the wrong place :o)

Okay, adding on what I was doing since this did not work for me. My macro
selects the "Stats" worksheet and copies a range. It then selects a number
of tabs (can be different amounts depending on data copied). So, I may
select tabs 3-10 one time and 3-16 tabs another time depending on how many
tabs are in the particular workbook. (Tabs 1 and 2 would never be selected).
This way, I can copy the data to all subsequent sheets at one time. Is this
possible, or do I have to do each tab separately, using your code?

I appreciate your help!!!
 
If you pre-select the sheets you need using CTRL + click you could change the
code to

Dim ws a s Worksheet
For Each ws In ActiveWindow.SelectedSheets
'transfer stuff
Next ws

Otherwise you would have to build an array of sheets based on some criteria.


Gord
 
Okay, that is fine. I will just use the code you gave me and let it paste to
each sheet, one at a time. Now, how should your code read so that sheets 1
and 2 are not selected?

Thanks again!
 
The code depends on what you worksheets you've grouped. So if you don't include
sheets 1 and 2 in that grouping, then you'll be fine.

If sheets 1 and 2 are always the left most worksheets, you can click on the
third tab and shiftclick on the rightmost.
 
You could change the worksheet codename for Sheet 1 and 2 and test for that.
To change the code name, do the following

F11 from the workbook
Ctrl R to show the VBAProject
Select the worksheet for SHeet1
Press F4 to get the properties window
You should see something that looks like (NAME) with Sheet1 or something
similar in it.
Replace SHeet1 with a worksheet code name.
Do the same for your Sheet 2

I'm going to assume you've called them "MySheet1" and "MySheet2" for this
example.

Dim WS as worksheet

for each ws in ThisWorkbook.worksheets
if not ws.codename = "mySheet1" and _
not ws.codename = "mySheet2" then
'perform the actions on your sheets.
end if
next ws

HTH,
Barb Reinhardt
 
If you know the worksheets that should be avoided (by name), you can use:

dim wks as worksheet
for each wks in activeworkbook.worksheets
select case lcase(wks.name)
case is = "sheet1", "sheet2"
'do nothing
case else
'your code here
end select
next wks

If you don't always know the name, but the sheets are always the two leftmost
sheets:

dim wCtr as long
for wctr = 3 to activeworkbook.worksheets.count
'code to do the work agains worksheets(wctr)
next wctr
 
ActiveWindow.SelectedSheets runs only on those you pre-selected. Just don't
select sheet1 and 2 or the "Stats" sheet.

You say the macro you currently have selects a variable number of sheets based
upon amount of data to be transferred and/or the number of sheets in a workbook.

How does your macro do that?

Post the code you have now.


Gord
 
Back
Top