transfer data with resize

[rjplante]

I am trying to move data from my active worksheet to a destination worksheet without using specific named ranges. When I run the code below, I get the following error: "Run-Time error '438': Object doesn't support this property or method." The line with my resize text is the line of text highlighted when I select debug from the pop-up window.

How to I fix this? I am guessing it has something to do with how I am using the Tx cell, TxSheet, and DestCell terms.

Sub CORRECTION()


Application.ScreenUpdating = False


Dim Lrow As Long
Dim MyRow As Long
Dim TxCell As String
Dim TxSheet As String
Dim DestCell As String


TxSheet = ActiveSheet.Name
TxCell = ActiveCell.Address


Sheets("Corrections").Activate
Sheets("Corrections").Calculate


[COLOR=#008000]'  Find the last row in column A[/COLOR]


    On Error Resume Next
    Lrow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    lookat:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    On Error GoTo 0


    MyRow = Lrow + 1


DestCell = Range("A" & MyRow)


Sheets("Corrections").DestCell.Resize(1, 2).Value = Sheets(TxSheet).Range(TxCell).Resize(1, 2).Value

Sheets(TxSheet).Activate


Application.ScreenUpdating = True


End Sub

Thanks for the help.

 

[JLGWhiz]

A couple of things.
1. You did not show your declarations so I do not know if you want DestCelll to be a range object or a value.
2. If you want it to be a range object it has to be declared as such, i.e.

Dim DestCell As Range.

3. If you delclare it as a range, then you would need to use a Set statement to initialize it, i.e.

Set DestCell = Range("A" & MyRow)

4. If you set it as a range then the parent sheet is included in the varaible attributes, so you do not use the Sheets("Corrections"). part of the resize statement, i.e

DestCell.Resize(1, 2).Value = Sheets(TxSheet).Range(TxCell).Resize(1, 2).Value

5. If 1 thru 4 above do not apply and you are using DestCell to capture the value of a range, then you cannot use it at all in the resize statement.

 

[rjplante]

Thank for the awesome detailed response. The adjustments to the macro work great. I appreciate the feedback and the education. You are the best!!

 

[JLGWhiz]

Thank for the awesome detailed response. The adjustments to the macro work great. I appreciate the feedback and the education. You are the best!!

happy to help,
Regards, JLG