Worksheet array using codenames

[Thebatfink]

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

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

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

and

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!

 

[mikerickson]

Dim Sh as worksheet

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

 

[Thebatfink]

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 ..

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 -

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

 

[pgc01]

Hi

I would use an array of worksheets:

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]

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

Set Wshs(2) = Sheet3

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

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

Workbooks.Open myDir & fn

Cheers
Batfink!

 

[GTO]

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]

...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

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]

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

Runtime 424 - object required

error on the line

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

Workbook(fn).Activate

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

 

[Thebatfink]

The full 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]

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

Runtime 424 - object required

error on the line

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...

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