VBA to go to previous workbook

Akakumori

New Member
Joined
Jun 10, 2017
Messages
22
Hello,
I have little knowledge of VBA and I usually do my macros by recording and cleaning the code slightly.

I want to know if I am working with two different workbooks I can move between them with my macro without referring to their names because everytime I use the macro the names of the workbooks will be different. So each time macro won't work.

Thanks
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,609
Before running the macro, make sure the workbook you want to set as wb1 is the active workbook.
Change the workbook refs in your code to wb1/wb2 instead the hard coded names.
Code:
Sub YourMacro()
Dim wb1 As Workbook, x$, wb2Name$, wb2 As Workbook
'Set active workbook as wb1
Set wb1 = ActiveWorkbook
'Identify wb2
x = Application.GetOpenFilename
'Get name of selected file
wb2Name = Right(x, Len(x) - InStrRev(x, "\"))
'Check if selected file is open and, if not, open it
If Not bFileOpen(wb2Name) Then Workbooks.Open (x)
'Set selected file as wb2
Set wb2 = Workbooks(wb2Name)
'For testing - delete these 2 lines later
MsgBox "wb1 is :  " & wb1.Name
MsgBox "wb2 is :  " & wb2.Name


'Your code here :


End Sub


Function bFileOpen(wbname As String) As Boolean
On Error Resume Next
bFileOpen = Len(Workbooks(wbname).Name)
On Error GoTo 0
End Function
 

Akakumori

New Member
Joined
Jun 10, 2017
Messages
22
Hi footoo,
Thanks so much for your reply.
Not so sure how I should be replacing the workbooks refs in my code to wb1/wb2. If I have one function which is opening my workbook from certain location. Should I just change the name of the workbook itself?

Thanks
 

Forum statistics

Threads
1,077,619
Messages
5,335,275
Members
399,011
Latest member
rodmicpc

Some videos you may like

This Week's Hot Topics

Top