Cant Get Constant in String to work

[howard]

I have a constant which I have tried to incorporate in my VBA code, but get subscript out of range

 Const Z As String = "BR1"


nb.Sheets(1).Copy After:=Workbooks("& Z & Sales for northern region.xlsm").Sheets(1)

It would be appreciated if someone could correct my code

 

[Jon von der Heyden]

What is the name of the workbook that you want to open?

 

[howard]

BR1 Sales for Northern region.xlsm

 

[Jon von der Heyden]

Try:

nb.Sheets(1).Copy After:=Workbooks(Z & " Sales for northern region.xlsm").Sheets(1)

 

[howard]

Thanks for the help