VBA To change Active Workbook

[markf5998]

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!

 

[Asator]

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>

 

[CWatts]

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.

Sub Change()
    Workbooks("potemplate.xls").Activate
End Sub

 

[markf5998]

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

 

[mathiaspeace]

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?