transfer data with resize

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
574
Office Version
  1. 365
Platform
  1. Windows
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.


Code:
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
Code:
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.
Code:
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
Code:
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.
 
Last edited:
Upvote 0
Thank for the awesome detailed response. The adjustments to the macro work great. I appreciate the feedback and the education. You are the best!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top