Problem with Paste method

JimmyB · Sep 11th 2020

Hi. I'm having a problem with pasting a range of cells from one sheet to another. I'm trying every variation that I can think of and nothing works. The whole sub works perfectly except the paste at the very end of the sub. Here's the code. Appreciate any help You can give me....Paste Method of Worksheet Class Failed.

Thanks so much

JimmyB

Code

Sub RecipeMoveToEdit()

Dim Strt
Dim TopLft
Dim BotRt
Dim LftLstT
Dim LftLstB
Dim Gree
Dim GrLin
Dim SndTo
Dim SndRng

        Sheets("Recipies Data").Activate
        Set Strt = ActiveCell
        Set TopLft = Strt.Offset(-12, 0)
MsgBox TopLft.Address
        Set BotRt = Strt.Offset(0, 11)
MsgBox BotRt.Address
        Set LftLstT = TopLft.Offset(0, -1)

        Set LftLstB = Strt.Offset(0, -1)

        Set Gree = Strt.Offset(1, 0)
        Set SndRng = Range(TopLft.Address, BotRt.Address)
        Range(LftLstT, LftLstB).ClearContents
        
        GrLin = Gree.Row
        ActiveSheet.Rows(GrLin).Delete Shift:=xlUp
        
        Range(TopLft.Address, BotRt.Address).Cut
   
        Sheets("VALUES for MEALS").Activate
        Set SndTo = ThisWorkbook.Sheets("VALUES for MEALS").Range("N4")
        Range(SndTo.Address).Select
MsgBox SndTo.Address
        
    With Range(SndTo.Address)
        ActiveSheet.Paste
    End With

End Sub

Display More

Logit · Sep 11th 2020

Code

'Set SndTo = ThisWorkbook.Sheets("VALUES for MEALS").Range("N4")
'Range(SndTo.Address).Select

MsgBox "N4"   'SndTo.Address
 
 'With Range(SndTo.Address)
Sheets("VALUES for MEALS").Activate
Range("N4").Paste
 'End With
End Sub

jolivanes · Sep 11th 2020

If you had to explain, without showing code that does not do what you want, in a concise manner what you want to achieve, what would you say?

Don't leave anything out.

**royUK** · Sep 11th 2020

I'm sure you must have read or been told that activating and selecting ranges and sheets is not only unnecessary, but inefficient.

None of your variables have been given a type and that makes understanding your code harder and again I think less efficient.

How can you be sure that the correct cell is the activecell?

Code

Sheets("Recipies Data").Activate
Set Strt = ActiveCell

I'm not going to even try and understand your code, explain fully what it is supposed to do.

Basically, copying can be as simple as

Code

Sheet1.Range("A1").Copy Sheet2.Range("A1")

JimmyB · Sep 17th 2020

Hi Logit

It didn't work. Sure seems to me that it should have.

I get the error: Object doesn't support this property or method

JimmyB · Sep 17th 2020

Hi Roy.. Sorry for the late response. I've been sick for the last 2 wks. Feel ok now.

I've always had problems assigning to the variables. It seems that whenever I do that, whatever I had that worked, comes up with an error. I'll find a good training file to help me with that and improve. As far as Selecting goes, i guess I misunderstood. Someone did tell me to drop my "Select" and use "Activate" instead. In the future I'll try to go around it.

Anyway. I'm including the code that I'm having trouble with "Pasting". There are Rem statements throughout explaining everything that's going on.

I really appreciate your help.

Thanks JimmyB

Code

Sub RecipeMoveToEdit()

Dim Strt
Dim TopLft
Dim BotRt
Dim LftLstT
Dim LftLstB
Dim Gree
Dim GrLin
Dim SndTo
Dim SndRng

        Sheets("Recipies Data").Activate ' A block of data with alpha & numeric content has been previously placed on this 'Recipies Data' Sheet
        

        Set Strt = ActiveCell  ' User is instructed to choose a specific cell to trigger the move back into the original sheet
                               '     thus making that chosen cell as the Active Cell (Strt)
        Set TopLft = Strt.Offset(-12, 0)  ' With the active cell we are setting the top left cell of the block by (Offset)
MsgBox TopLft.Address
        Set BotRt = Strt.Offset(0, 11)    ' With the active cell again we are setting the bottom right cell (offset)
MsgBox BotRt.Address

        
'This block is external from the problem block that I'm trying to cut and paste.  There is no problem with this process
        Set LftLstT = TopLft.Offset(0, -1)  ' This sets the 1st cell of a list that is necessary on this sheet but not needed if the block _
                                                is returned to the original sheet.  It is used only so the filter will pick up _
                                                all of the rows in the list
        Set LftLstB = Strt.Offset(0, -1)   'This sets up the last cell of the above list
        Set Gree = Strt.Offset(1, 0)
        Set SndRng = Range(TopLft.Address, BotRt.Address)
        Range(LftLstT, LftLstB).ClearContents
        GrLin = Gree.Row
        ActiveSheet.Rows(GrLin).Delete Shift:=xlUp
        
        ' This block works perfectly
 'End of block
 
 
        Range(TopLft.Address, BotRt.Address).Cut  'This identifies the block that needs to be cut from 'Recipies Data' sheeet _
                                                    and pasted in 'Values for Meals' _
                                                    The block pf cells that this pics, is exactly what it should be _
                                                    Also It's on the clipboard _
                                                    But it won't paste _
                                                    Error: "Object doesn't support property or method"

    With ThisWorkbook.Sheets("VALUES for MEALS").Range("N4")
        .Paste
    End With
End Sub

Display More

JimmyB · Sep 17th 2020

Will check back in the morning...Thanks again

**royUK** · Sep 17th 2020

You don't need to activate either in most cases

Declaring Variables in Excel VBA

JimmyB · Sep 17th 2020

Thanks Roy. If you get a chance can you please look at the code that I sent you?

jolivanes · Sep 18th 2020

JimmyB

I was in the understanding that this is an open forum, not a private email site..

**royUK** · Sep 18th 2020

He hasn't sent me any code!!!

JimmyB · Sep 18th 2020

Hi Jolivanes Hi Roy

I have no idea why you sent me either of the notes (Thread #2 & Thread #10) or why Roy answered you saying "He hasn't sent me any code!!!" (Roy, Thread # 4 advised me to be more specific...so in Thread #6 I explained everything regarding my problems and attached the code with Rem statements explaining each line.

Really guys I totally appreciate your help and have no intention of creating excess drama, but I am at a loss for words. You guys have been a lot of help and again...I appreciate it, but don't know what you want from me. I'm trying my best.

Thanks JimmyB

davesexcel · Sep 19th 2020

Your cut and paste should be in one line.

Code

    Range(TopLft.Address, BotRt.Address).Cut ThisWorkbook.Sheets("VALUES for MEALS").Range("N4")

**royUK** · Sep 19th 2020

Jolivanes seems to be under the impression that you have sent me your code privately

davesexcel · Sep 19th 2020

miscommunication in post#9

davesexcel · Sep 19th 2020

I believe cut does not have a pastespecial option just a destination.

**royUK** · Sep 19th 2020

This is my suggestion, I would explicitly declare a range for Strt

Code

Sub RecipeMoveToEdit()


Dim Strt As Range, TopLft As Range, BotRt As Range, LftLstT As Range, LftLstB As Range, Gree As Range, SndTo As Range, SndRng As Range
Dim GrLin As Long


With Sheets("Recipies Data") ' A block of data with alpha & numeric content has been previously placed on this 'Recipies Data' Sheet
''/// you use Activecell but how do you know this is correct
Set Strt = ActiveCell ' User is instructed to choose a specific cell to trigger the move back into the original sheet
' thus making that chosen cell as the Active Cell (Strt)<- this is nonsense the activecell will always be the activecell
Set TopLft = Strt.Offset(-12, 0) ' With the active cell we are setting the top left cell of the block by (Offset)
'MsgBox TopLft.Address
Set BotRt = Strt.Offset(0, 11) ' With the active cell again we are setting the bottom right cell (offset)


'This block is external from the problem block that I'm trying to cut and paste. There is no problem with this process
Set LftLstT = TopLft.Offset(0, -1) ' This sets the 1st cell of a list that is necessary on this sheet but not needed if the block _
is returned to the original sheet. It is used only so the filter will pick up _
all of the rows in the list
Set LftLstB = Strt.Offset(0, -1) 'This sets up the last cell of the above list
Set Gree = Strt.Offset(1, 0)
Set SndRng = .Range(TopLft.Address, BotRt.Address)
Range(LftLstT, LftLstB).ClearContents
GrLin = Gree.Row
.Rows(GrLin).Delete Shift:=xlUp
' This block works perfectly
'End of block
.Range(TopLft.Address, BotRt.Address).Cut ThisWorkbook.Sheets("VALUES for MEALS").Range("N4")
ThisWorkbook.Sheets("VALUES for MEALS").Range("N4").Value = ThisWorkbook.Sheets("VALUES for MEALS").Range("N4").Value
End With
'This identifies the block that needs to be cut from 'Recipies Data' sheeet _
and pasted in 'Values for Meals' _
The block pf cells that this pics, is exactly what it should be _
Also It's on the clipboard _
But it won't paste _
Error: "Object doesn't support property or method"






End Sub

Display More

jolivanes · Sep 19th 2020

royUK

Yes, I was. The way it was worded gave me that impression.

JimmyB was asked twice, in Post $3 and Post #4, to explain what he wants to achieve but never did.

The code that was supplied in Post #6 needed the asked for explanation IMHO

JimmyB · Sep 19th 2020

Thanks all. I'll respond and tryout tonight when I am available

Thanks JimmyB

JimmyB · Sep 20th 2020

Hi Jolivanes. Thread #3 was confusing & I didn't know exactly how to respond. Thought you were just making a statement of sorts...now (I guess) I get it. When I advised Roy that I sent him my code, I probably should not have worded as such. instead of "I sent it to you", maybe I could have lengthened it to "I placed the code on the thread..."or something to that effect, so as to make it clearer for you. Sa-sorry if this inconvenienced you in any way. will try to be more careful in the future.

Hi Dave thanks for the offer of information. I input the line of code as you recommended and with a bit of tweaking I think that it would have worked...but there was still some issues. Since I also had a recommendation from Roy, I thought I'd give his code a try...no offence please. He has the same idea with the cut and paste on the same line as you suggested, but with a little twist.

Hey Roy, you pulled through for me AGAIN. I uploaded your code and it worked right off the bat. The only thing that I had to do was unprotect my target sheet, for it to paste. (so unprotected at beginning of sub then protecting at the end). That's fine but I wonder why. I have the code in the 'Workbook Open' event, to allow all macros able to perform on protected sheets and it works perfectly everywhere else. Hmmm...don't know, but I'm happy with the help I received from Dave, Logit and Yourself. In the future, per your recommendation, I will be declaring all of my variables and will do so on the subs of this existing program that I've finally completed thanks to the help I've received from people like you guys at Ozgrid.

Thanks again JimmyB

Problem with Paste method

Participate now!

Share