What you are doing with that line of code is putting the same range address
in eight different cells. Did you want that or did you want the data in the
used range to be saved to those cells?

"Paul3rd" wrote:

Hello,
I'm using code to save a Range in the BeforeSave event of a closed workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheet2.Range("B3:I3") = Sheet1.UsedRange.Address
End Sub

I'm then using code in a module in another workbook to retrieve that Range:

Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String
'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet2.Range("B3:I3") = "= 'C:\" & "[CTest.xls]Sheet1'!RC"

'Pass the area Address to a String.
AreaAddress = Sheet2.Range("B3:I3").Address

With Sheet2.Range(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[CTest.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[CTest.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With

End Sub

The Range is pulled into Sheet2 ("B3:I3"), I'd like to change the
destination in Sheet2 to ("B8:I8") and I'm having trouble doing that.
Thanks in advance for any help.
Paul