Can't execute code in Break mode

[mrexcelrc]

Hi, I have this VBA macro below which basically just opens a file as defined and go to specific cells then copy the vales and then go back to the main file and paste the values there. My problem is going back or reactivating the file that I opened so i can copy another cell value. As highlighted below, there is something wrong with that line of code because I get error" Can't execute code in break mode". Can you Excel masters please help in fixing this line of code? Thank you so much!


Sub UpdateVaR()

Dim myVal As Date
myVal = Sheets("Dashboard").Range("AsofDate")
datestring = Application.WorksheetFunction.Text(myVal, "yyyymmdd")
myfiletype = ".xlsx"

mypath1 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_inclAIV_"
mystring1 = mypath1 & datestring & myfiletype

mypath2 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_exclAIV_"
mystring2 = mypath2 & datestring & myfiletype



Workbooks.Open Filename:=mystring1
Range("C30:D30").Select
Selection.Copy
Workbooks("AFS_Daily_Risk_MtM_report.xlsm").Activate
Range("E46:F46").Select
Selection.PasteSpecial Paste:=xlPasteValues
Workbooks(mystring1).Activate
Range("C31").Select
Selection.Copy
Workbooks("AFS_Daily_Risk_MtM_report.xlsm").Activate
Range("E47").Select
Selection.PasteSpecial Paste:=xlPasteValues

 

[JoeMo]

You seem to be missing a path separator. Try:

mypath1 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_inclAIV_" & Application.PathSeparator

 

[Andrew Poulsom]

The Workbooks property expects only the name of the workbook not its entire path. You can assign the workbook to an object variable when you open it:

Set wb = Workbooks.Open(Filename:=mystring1)

then use:

wb.Activate

 

[mrexcelrc]

The Workbooks property expects only the name of the workbook not its entire path. You can assign the workbook to an object variable when you open it:

Set wb = Workbooks.Open(Filename:=mystring1)

then use:

wb.Activate

So I incorporated your suggestion as shown below but I still get the error on the highlighted line below. Can you please advise if there is something I'm missing here? Thank you.


Sub UpdateVaR()
Dim myVal As Date
myVal = Sheets("Dashboard").Range("AsofDate")
datestring = Application.WorksheetFunction.Text(myVal, "yyyymmdd")
myfiletype = ".xlsx"
mypath1 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_inclAIV_"
mystring1 = mypath1 & datestring & myfiletype
Set wb1 = Workbooks.Open(Filename:=mystring1)
mypath2 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_exclAIV_"
mystring2 = mypath2 & datestring & myfiletype
Set wb2 = Workbooks.Open(Filename:=mystring2)
Workbooks.Open Filename:=mystring1
Range("C30:D30").Select
Selection.Copy
Workbooks("AFS_Daily_Risk_MtM_report.xlsm").Activate
Range("E46:F46").Select
Selection.PasteSpecial Paste:=xlPasteValues
wb1.Activate
Range("C31").Select
Selection.Copy
Workbooks("AFS_Daily_Risk_MtM_report.xlsm").Activate
Range("E47").Select
Selection.PasteSpecial Paste:=xlPasteValues
wb1.Close

 

[mrexcelrc]

So I incorporated your suggestion as shown below but I still get the error on the highlighted line below. Can you please advise if there is something I'm missing here? Thank you.


Sub UpdateVaR()
Dim myVal As Date
myVal = Sheets("Dashboard").Range("AsofDate")
datestring = Application.WorksheetFunction.Text(myVal, "yyyymmdd")
myfiletype = ".xlsx"
mypath1 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_inclAIV_"
mystring1 = mypath1 & datestring & myfiletype
Set wb1 = Workbooks.Open(Filename:=mystring1)
mypath2 = "Q:\Summit\Professional\AFS_RISK_REPORT\reports\201312\AFS_RISK_REPORT_exclAIV_"
mystring2 = mypath2 & datestring & myfiletype
Set wb2 = Workbooks.Open(Filename:=mystring2)
Workbooks.Open Filename:=mystring1
Range("C30:D30").Select
Selection.Copy
Workbooks("AFS_Daily_Risk_MtM_report.xlsm").Activate
Range("E46:F46").Select
Selection.PasteSpecial Paste:=xlPasteValues
wb1.Activate
Range("C31").Select
Selection.Copy
Workbooks("AFS_Daily_Risk_MtM_report.xlsm").Activate
Range("E47").Select
Selection.PasteSpecial Paste:=xlPasteValues
wb1.Close

Actually got it to work. Thank you very much.