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
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