Trouble changing Range destination
 

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

Trouble changing Range destination
 

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

Trouble changing Range destination
 

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

Trouble changing Range destination
 

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

Trouble changing Range destination
 

Using command buttons on Sheet2 I'd like the user to decide which row on
Sheet2 the data range is copied to:
PButton2 Click()
Range("B8:I8").PasteSpecial xlspecialvalues
PButton3 Click()
Range("B9:I9").PasteSpecial xlspecialvalues
or something like that.
Paul
"JLGWhiz" wrote:

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