Problem with Paste method

  • 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


    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • 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
  • 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")
  • 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


    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

  • 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

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

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

  • 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

    Thanks for your help

    JimmyB


    Some plans fail on execution

    All plans fail without

    8)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!