Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range.Copy [Destination] should not use clipboard | Excel Discussion (Misc queries) | |||
Destination Range Names to be deleted. | Excel Programming | |||
Destination range | Excel Discussion (Misc queries) | |||
Selection.Autofill Destination:=Range(ActiveCell.Value) | Excel Programming | |||
Range.Copy (Destination) | Excel Programming |