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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,854
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,886
Messages
5,410,996
Members
403,336
Latest member
amreeves87

This Week's Hot Topics

Top