Excel VBA switching between workbooks

wigarth

Board Regular
Joined
Apr 16, 2016
Messages
51
Office Version
  1. 365
Platform
  1. Windows
Hi!

This time I am really stuck.

I have two workbooks
Workbook1 (main file - named: h:\dekstop\somethingneweveryday.xlsm)
Workbook2 (data file - named: H:\desktop\blomst.xlsx)

I do my work in Workbook1. I use a macro in workbook1 to open and activate Workbook2 and do some stuff and collect stuff. All good so far
The problem occurs when I try to reactivate Workbook1... I get it to work perfectly when I specify a filename. But Woorkbook1 changes name everyday, so its kinda pointless to work around this.. I tried some various examples of "Activeworkbook" but when i open workbook2 it seems that the active workbook changes too?

Somehow I need to make excel "read and memorize" the activate woorkbook so i can activate it without specyfying the name of it in the macro

What do I need to change in the below code to fix this?

Sample code below: (note: I am in workbook1 when activating this macro)

Private Sub Get_Data()

Application.Run "hjelp"

Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("blomst.xlsx")
On Error GoTo 0
If WB Is Nothing Then
Set WB = Workbooks.Open("H:\desktop\blomst.xlsx") 'this filename is constant
End If

Windows("Ukeplan.xlsm").Activate ' This filename changes everyday

Range("L2").Select
ActiveCell.FormulaR1C1 = "='[blomst.xlsx]Rute 5 OSLO MØ kl. 13'!R5C4"
Range("L2").Select
ActiveCell.FormulaR1C1 = "='[blomst.xlsx]Rute 5 OSLO MØ kl. 13'!R[3]C[-8]"
Range("M2").Select
ActiveCell.FormulaR1C1 = "='[blomst.xlsx]Rute 5 OSLO MØ kl. 13'!R[3]C[-2]"
Range("N2").Select
ActiveCell.FormulaR1C1 = "='[blomst.xlsx]Rute 5 OSLO MØ kl. 13'!R[3]C[-2]"
Range("L2:N2").Select
Selection.AutoFill Destination:=Range("L2:N219"), Type:=xlFillDefault
Range("L2:N219").Select
ActiveWindow.SmallScroll Down:=-237
Range("A1").Select

Windows("blomst.xlsx").Activate
Cells.Replace What:="Butikk - Navn", Replacement:=""
Cells.Replace What:="esker", Replacement:=""
Cells.Replace What:="Butikkutstyr", Replacement:=""
Application.DisplayAlerts = False
ActiveWorkbook.Close
Application.DisplayAlerts = True
Windows("ukeplan.xlsm").Activate 'this filename changes everyday
Range("A1").Select
Application.Run "blomster_ferdig"
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi,

If the code is in Workbook1 then you can use Thisworkbook. to refer to it.

e.g. Thisworkbook.Activate

Hope this helps,

Eric
 
Upvote 0
Thank you so much!! that did the trick!

Visual basic is easy when you read it.... but to come up with the expressions is somehow more challenging.

So much struggle over this and yet so simple... Thanks for taking the time to help me solve this.

Best Reggards
Wig!
 
Upvote 0
Thanks for the feedback.

Know what you mean & fyi, MrExcel has been my main source of knowledge for a few years :-)

Eric.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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