Activate a Workbook

[Bishop]

I have this code:

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*.xlsm"
If wb.Name Like "*C&A PF*.xlsm" Then
???.Activate
End If
Next

How do I make THAT workbook the active workbook?

 

[Joel]

There is no need to activate the sheets or the workbook. Just use wb instead
of the workbook name


For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*.xlsm"
If wb.Name Like "*C&A PF*.xlsm" Then
with wb
.sheets("Sheet1").Range("A1") = "ABC"
end wb
End If
Next

 

[Bishop]

I see what you're doing when I try that I still get another error. Here's my
code:

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*.xlsm"
If wb.Name Like "*C&A PF*.xlsm" Then
With wb
Set CDLastRow = .Sheets("Catalyst Dump").Range("A" &
Rows.Count) _
& .End(xlUp).Row
.Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 18
End With
End If
Next

I've tried:
Set CDLastRow = but this gives Object Required error
CDLastRow = but this gives RT Error: '438' Object doesn't support property
or method
CDLastRow = .Worksheets still 438

I can't figure it out.

 

[Dave Peterson]

Check your other post.

I see what you're doing when I try that I still get another error. Here's my
code:

For Each wb In Workbooks
'Test to see if wb's name is like "*C&A PF*.xlsm"
If wb.Name Like "*C&A PF*.xlsm" Then
With wb
Set CDLastRow = .Sheets("Catalyst Dump").Range("A" &
Rows.Count) _
& .End(xlUp).Row
.Worksheets("Catalyst Dump").Columns("D").ColumnWidth = 18
End With
End If
Next

I've tried:
Set CDLastRow = but this gives Object Required error
CDLastRow = but this gives RT Error: '438' Object doesn't support property
or method
CDLastRow = .Worksheets still 438

I can't figure it out.

 

[Joel]

You are using the aphersand and SET incorrectly

Set CDLastRow = .Sheets("Catalyst Dump").Range("A" &
Rows.Count) _
& .End(xlUp).Row

1) The line should be

CDLastRow = .Sheets("Catalyst Dump").Range("A" & Rows.Count).End(xlUp).Row

2) The & is used to combine two strings like this

a = "Boys"
b = "and"
c = "Girls"

d = a & b & c 'this is "Boys and Girls"

3) The underscore is a line continuation character

You can't do this

& .End(xlUp).Row

The amphsand is wrong

4) You don't neet to have the wrod set since you are returning the ".ROW"
which is a number and not an object.

instead of this
CDLastRow = .Sheets("Catalyst Dump").Range("A" & Rows.Count).End(xlUp).Row

you can do this

set CDLastRow = .Sheets("Catalyst Dump").Range("A" & Rows.Count).End(xlUp)

CDLastRow is the actual last cell (an object) in the above. My original
code is returning a number.