Thanks for your reply, I wasn't clear in that I want to change the range
destination with code in Sheet2. Not in the closed workbook that contains
Sheet1.


"JLGWhiz" wrote:

What does "having trouble" mean?

To change the destination, just change the cell address references

From: Sheet2.Range("B3:I3") = Sheet1.UsedRange.Address
To: Sheet2.Range("B8:I8") = Sheet1.UsedRange.Address
"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