VBA To change Active Workbook

markf5998

Board Regular
Joined
Jan 13, 2011
Messages
102
You would think this would be an easy one to find through a google search, but it is surprisingly difficult!

Can someone please tell me how to change the active workbook using VBA code? I'm currently using:

workbooks("potemplate.xls").activate

But that is apparently wrong...

POTemplate.xls is open, if it makes a difference.

Thanks for any suggestions!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Are you sure its not working? From the help file:

This example activates Book4.xls. If Book4.xls has multiple windows, the example activates the first window, Book4.xls:1.

<CODE>Workbooks("BOOK4.XLS").Activate</CODE></PRE>
And yes, it'll die on you if you don't have the workbook open.</PRE>
 
Upvote 0
If your VBA code is remaining lower case instead of converting things to upper case, then that is indicitive of a larger problem than syntax.

Are you just pasting that into VBA or are you wrapping putting it in a sub like? Just making sure we're not assuming anything.

Code:
Sub Change()
    Workbooks("potemplate.xls").Activate
End Sub
 
Upvote 0
That part seems to be working now. I checked to see if uppercase/lowecase made a difference in this situation and it doesn't, so that seems like a good thing!

The code I'm using looks through a folder, and for each file found, should repeat the ProcessData sub.

So far it gets to the part where it copies data from "POTemplate.xls", changes back to the original workbook, but then the macro stops. No error, but also it doesn't paste or loop back to the next file in the folder...Any ideas?

Thanks again for your help!


Sub OpenAndProcess()
Dim vaFileName As Variant
Const MyDir As String = "[my file path]"
'the location of the workbooks
With Application.FileSearch
.NewSearch
.LookIn = MyDir
'the directory to search in
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
'workbooks found
Application.ScreenUpdating = False
For Each vaFileName In .FoundFiles
'loop through each found workbook
ProcessData vaFileName
'pass workbook fullname to copy routine
Next
Else
MsgBox "There were no Excel files found."
End If
Application.ScreenUpdating = True
End With
End Sub

Sub ProcessData(ByVal Fname As String)
Dim PasteTo As Workbook

Workbooks.Open Fname
'open the target workbook
Set PasteTo = ThisWorkbook

'Unhide, Unprotect, and Select the "Data Map" worksheet
Worksheets("Data Map").Visible = True
Sheets("Data Map").Select
ActiveSheet.Unprotect Password:="[Password]"
'Select IP5:IT55 from POTemplate.xls and paste to "ThisWorkbook"
Workbooks("POtemplate.xls").Activate
Sheets("Data Map").Select
Range("ip5:it55").Select
Selection.Copy
PasteTo.Activate
Range("ip5:it55").Select
Paste = xlpaste


'Close the workbook and save changes
ActiveWorkbook.Close savechanges:=True

End Sub
 
Upvote 0
I am having the exact same issue: data is successfully copied from an open workbook & the second workbook opens, but then the script simply stops. It should be as simple as using Workbooks("filename.xls").Activate, yes?
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top