Excel VBA switching between workbooks

[wigarth]

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

 

[Eric Golf]

Hi,

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

e.g. Thisworkbook.Activate

Hope this helps,

Eric

 

[wigarth]

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!

 

[Eric Golf]

Thanks for the feedback.

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

Eric.