P
Paul3rd
Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet is:
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.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Cells(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:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.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
*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
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.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")
With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.
.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.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
I need some help to sort this out,
Thanks in advance
Paul
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet is:
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.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Cells(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:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.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
*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
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.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")
With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.
.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.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
I need some help to sort this out,
Thanks in advance
Paul