copy a formula from a cell in one workbook to a cell in another workbook

[awd_awd]

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.

 

[Andrew Poulsom]

Resize rngPaste so that it is the same size as rngCopy. Then replace:

rngCopy.Copy
rngPaste.PasteSpecial

with:

rngPaste.Formula = rngCopy.Formula

 

[awd_awd]

Hi Andrew,

Thanks for your help, unfortunately i now get error 2run-time error '7' out of memory


the debug highlights the new code i replaced as per your advice - rngPaste.Formula = rngCopy.Formula

thanks in advance

 

[Andrew Poulsom]

Try to avoid copying entire columns.

 

[awd_awd]

Quite strange, i have made the adjustments and still no joy

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")
Set wbPaste = Workbooks("AB new pricer.xlsm")
Set wsPaste = wbPaste.Worksheets("Pricer")
Set rngPaste = wsPaste.Range("a:ar")
Application.DisplayAlerts = False
rngPaste.Formula = rngCopy.Formula
wbCopy.Close
End Sub

debug highlights

rngPaste.Formula = rngCopy.Formula

 

[Andrew Poulsom]

This is still entire columns:

Set rngCopy = wsCopy.Range("a:ar")

Be more specific with the range that you want to copy.