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? |
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? |
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? |
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 |
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. |
All times are GMT +1. The time now is 07:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com