Worksheet array using codenames

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397
Hi,

I'm struggling to get this working using codenames rather than the sheets name. I have various workbooks which I'm going to be cycling through, and whilst the codenames are all identical in each workbook, the sheetnames are all named different as they have various dates on them..

When I open a book I want to perform some formatting and formula inserts etc in to each sheet .. In the past when sheetnames were identical I'd of just used

Code:
Dim sh as worksheet
For Each Sh In Worksheets(Array("Sheet2", "Sheet3", "Sheet7", "Sheet8"))
Next sh

I need a way of turning this into using codenames. I've tried

Code:
For Each Sh In Worksheets(Array("Sheet2.name", "Sheet3.name", "Sheet7.name", _
"Sheet8.name"))

and

Code:
For Each Sh In Worksheets(Array(2, 3, 7, 8)

as some examples seem to suggest, but it doesn't work and I'm not sure what the correct way of handling this should be. Any help would be greatly appreciated.

Thanks
Batfink!
 
Last edited:

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
Code:
Dim Sh as worksheet

For Each Sh In Array(Sheet2, Sheet3, Sheet7, Sheet8)
    Rem do stuff
Next Sh
 

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397
Hi,

Thanks for the reply. Its giving me an error "object required" ..

I have this code right after I open the workbook (my vb opens workbooks one by one from within a target folder and then performs the code on the newly opened workbook) using this ..

Code:
Workbooks.Open myDir & fn

Could this be causing the problem? A lack of workbook focus or something? Any ideas?

Thanks
Batfink!

---------
Its erroring out here -

Code:
For Each Sh In Array(Sheet2, Sheet3, Sheet7, Sheet8)
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,879
Hi

I would use an array of worksheets:

Code:
Function Test()
Dim Wshs(1 To 3) As Worksheet
Dim wsh
 
Set Wshs(1) = Sheet1
Set Wshs(2) = Sheet2
Set Wshs(3) = Sheet5

For Each wsh In Wshs
    MsgBox wsh.Name
Next wsh
End Function
 

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397

ADVERTISEMENT

Hi, I have the same object required problem - I think its trying to assign these sheets from the workbook executing the code rather than the work book I have opened as it errors here

Code:
Set Wshs(2) = Sheet3

and I don't have a Sheet3 in my source workbook..

Code:
myDir = "C:\mypath\"
fn = Dir(myDir & "*.xlsm")

Workbooks.Open myDir & fn

Dim Wshs(1 To 4) As Worksheet
Dim wsh
 
Set Wshs(1) = Sheet2
Set Wshs(2) = Sheet3
Set Wshs(3) = Sheet7
Set Wshs(4) = Sheet8

For Each wsh In Wshs

.......

Heres what I have, I don't understand how I can move focus between the source workbook executing the sub, and the workbook I open with

Code:
Workbooks.Open myDir & fn

Cheers
Batfink!
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
I did not quite follow your last, but from post#1...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> exa4()<br><SPAN style="color:#00007F">Dim</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Sh <SPAN style="color:#00007F">In</SPAN> Worksheets(Array(Sheet2.Name, Sheet3.Name, Sheet1.Name))<br>        MsgBox Sh.Range("A1").Value<br>    <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

You just need to ditch the quote marks. By exa, Sheet2.Name will return the string of the name. "Sheet2.name" is being read as the string currently, so since there is no sheet w/this tab name, it fails. Does that make sense?

Mark
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

...Heres what I have, I don't understand how I can move focus between the source workbook executing the sub, and the workbook I open with

Code:
Workbooks.Open myDir & fn

Cheers
Batfink!

Ahh, maybe I am getting it. Is the problem that the user may change the tabnames in the wb that you are opening, so... you would like to use the codenames (which you don't believe will be changed) instead?
 

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397
Yes the tabs names are changed by a user as a wookbook is for a specific date, and the tabs are being named these dates by the users. The workbooks however are always built from a master so the codenames are always consistant. There are many of these workbooks.

Your suggestion also doesn't work I get a

Code:
Runtime 424 - object required

error on the line

Code:
For Each Sh In Worksheets(Array(Sheet2.Name, Sheet3.Name, Sheet1.Name))

?

------------
With regards to your last post, I'm just trying to understand the 424 object required error if your clear the array should work. What I found was if I only reference Sheet1 and Sheet2 then it works. If I add Sheet7 and Sheet8 then it fails.

What I'm trying to say is I've opened a new workbook via vba in this sub that I'm writing (and that was the code you quoted), I then perform this array statement but I think its applying it to the source workbook that the sub is written in, NOT the workbook I just opened in the previous part of the sub.
I come to this conclusion because my source book only has Sheet1 and Sheet2 so its giving an object required because those sheets don't exist where its looking. So how do I make it apply this to the newly opened workbook to make the mods. I've tried

Code:
Workbook(fn).Activate

to focus on the opened workbook but it doesn't like that.
 
Last edited:

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
397
The full code ..

Code:
Private Sub button_click()

Dim myDir As String, fn As String, ws As Worksheet, Sh As Worksheet

myDir = "C:\mypath\"
fn = Dir(myDir & "*.xlsm")

'check for existing files

If fn = "" Then
MsgBox "Error target files do not exist in this location." _
& vbNewLine & vbNewLine & "Currently set directory is - " & myDir & "", 48, _
"Unable To Update": Exit Sub
End If

'begin workbook loop

Do While fn <> ""

'enable / disable application events and open workbook

Application.ScreenUpdating = False
Application.EnableEvents = False

Workbooks.Open myDir & fn

For Each Sh In Worksheets(Array(Sheet2.Name, Sheet3.Name, Sheet7.Name, Sheet8.Name))

Sh.Activate
Sh.Columns("AA:AA").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sh.Columns("AD:AD").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Sh.Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Sh.Range("AA4").Value = "blah"
Sh.Range("AD4").Value = "blahblah"
Sh.Range("AG4").Value = "blahblahblah"

Next Sh

ActiveWorkbook.Save
Workbooks(fn).Close False
fn = Dir

Application.ScreenUpdating = True
Application.Enableevents = True

Loop

MsgBox "Updated Succesfully"

End Sub

I have lots more updates to the books but haven't written it yet, but that should give you an indication of what I'm doing..

Thanks
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Yes the tabs names are changed by a user as a wookbook is for a specific date, and the tabs are being named these dates by the users. The workbooks however are always built from a master so the codenames are always consistant. There are many of these workbooks.

Your suggestion also doesn't work I get a

Code:
Runtime 424 - object required

error on the line

Code:
For Each Sh In Worksheets(Array(Sheet2.Name, Sheet3.Name, Sheet1.Name))

?

------------
With regards to your last post, I'm just trying to understand the 424 object required error if your clear the array should work. What I found was if I only reference Sheet1 and Sheet2 then it works. If I add Sheet7 and Sheet8 then it fails.

What I'm trying to say is I've opened a new workbook via vba in this sub that I'm writing (and that was the code you quoted), I then perform this array statement but I think its applying it to the source workbook that the sub is written in, NOT the workbook I just opened in the previous part of the sub.
I come to this conclusion because my source book only has Sheet1 and Sheet2 so its giving an object required because those sheets don't exist where its looking. So how do I make it apply this to the newly opened workbook to make the mods. I've tried...

Code:
Workbook(fn).Activate

to focus on the opened workbook but it doesn't like that.

Sorry about the suggested code, it was before I 'clued in' to the fact that you are wanting to run it against another wb. You are correct in that using the codename as an object name will apply to the sheet(s) in ThisWorkbook (ie - the wb wherein the code resides).

I didn't get to study the full code yet, but how about we use the other wb's sheets' codenames as strings instead?

Using PGC's example as to the collection:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> exa()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> wksheets(1 <SPAN style="color:#00007F">To</SPAN> 3)<br><SPAN style="color:#00007F">Dim</SPAN> wks <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>    <SPAN style="color:#00007F">Set</SPAN> wb = Workbooks.Open(ThisWorkbook.Path & "\OpenMe.xls")<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wks <SPAN style="color:#00007F">In</SPAN> wb.Worksheets<br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> wks.CodeName<br>        <SPAN style="color:#00007F">Case</SPAN> "shtSheet1"<br>            <SPAN style="color:#00007F">Set</SPAN> wksheets(1) = wb.Worksheets(wks.Name)<br>        <SPAN style="color:#00007F">Case</SPAN> "shtSheet2"<br>            <SPAN style="color:#00007F">Set</SPAN> wksheets(2) = wb.Worksheets(wks.Name)<br>        <SPAN style="color:#00007F">Case</SPAN> "shtSheet3"<br>            <SPAN style="color:#00007F">Set</SPAN> wksheets(3) = wb.Worksheets(wks.Name)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    MsgBox wksheets(2).Range("A1").Value<br>    wksheets(3).Parent.Close <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Would that help?

Mark
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top