Hello,
When attempting to get a macro I'm creating (Excel 2010) to copy a formula from a cell in one workbook to a cell in another workbook, it behaves. However when that formula contains a reference to another worksheet, the pasted formula will contain an added reference to the worksheet in the other workbook, as in:
='\\server\Shared\\[workbook.xlsm]EUROPE MASTER'!AB86 - INCORRECT
='EUROPE MASTER'!AB86 - CORRECT
CODE:
Sub openAndCopy()
Dim wbCopy As Workbook
Dim wsCopy As Worksheet
Dim rngCopy As Range
Dim wbPaste As Workbook
Dim wsPaste As Worksheet
Dim rngPaste As Range
Set wbCopy = Workbooks.Open("\\server\Shared\\[workbook.xlsm]EUROPE MASTER'!AB86")
Set wsCopy = wbCopy.Worksheets("Pricer")
Set rngCopy = wsCopy.Range("a:ar").EntireColumn
Set wbPaste = Workbooks("AB new pricer.xlsm")
Set wsPaste = wbPaste.Worksheets("Pricer") 'paste to different sheet?
Set rngPaste = wsPaste.Range("a1") 'change this if needed
Application.DisplayAlerts = False
rngCopy.Copy
rngPaste.PasteSpecial
wbCopy.Close
End Sub
Any help appreciated, thanks in advance.
When attempting to get a macro I'm creating (Excel 2010) to copy a formula from a cell in one workbook to a cell in another workbook, it behaves. However when that formula contains a reference to another worksheet, the pasted formula will contain an added reference to the worksheet in the other workbook, as in:
='\\server\Shared\\[workbook.xlsm]EUROPE MASTER'!AB86 - INCORRECT
='EUROPE MASTER'!AB86 - CORRECT
CODE:
Sub openAndCopy()
Dim wbCopy As Workbook
Dim wsCopy As Worksheet
Dim rngCopy As Range
Dim wbPaste As Workbook
Dim wsPaste As Worksheet
Dim rngPaste As Range
Set wbCopy = Workbooks.Open("\\server\Shared\\[workbook.xlsm]EUROPE MASTER'!AB86")
Set wsCopy = wbCopy.Worksheets("Pricer")
Set rngCopy = wsCopy.Range("a:ar").EntireColumn
Set wbPaste = Workbooks("AB new pricer.xlsm")
Set wsPaste = wbPaste.Worksheets("Pricer") 'paste to different sheet?
Set rngPaste = wsPaste.Range("a1") 'change this if needed
Application.DisplayAlerts = False
rngCopy.Copy
rngPaste.PasteSpecial
wbCopy.Close
End Sub
Any help appreciated, thanks in advance.