Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Workbook
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Workbook
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Workbook
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" wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Workbook
Check your other post.
Bishop wrote: 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" wrote: 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" wrote: 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activate a Workbook
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activate WorkBook | Excel Programming | |||
Activate Workbook | Excel Programming | |||
Workbook.Activate / Window.Activate problem | Excel Programming | |||
Activate Different workbook? | Excel Programming | |||
Activate Workbook | Excel Programming |