+ Reply to Thread
Results 1 to 16 of 16

Simple: Paste range to location determined by IF statement

  1. #1
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Simple: Paste range to location determined by IF statement

    Hello,

    I'm trying to do something simple but I am not managing to. Please help if you can.

    The following code tries to the copy 3 cells (template1!M3:O3) to a the next available row in a second sheet (ref!). The rows where the 3 cells should be pasted vary depending on the content of cell template1!O3 (As an example there are 5 options of text in cell O3: AA, BB, CC, DD, EE, DD).

    For some reason my code keeps pasting the 3 copied cells in row "W", diregarding the IF stetements or the content of cell O3.

    Does anyone see what's wrong? Code below:


    Please Login or Register  to view this content.

    Thanks!

    Goeff.

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Simple: Paste range to location determined by IF statement

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Simple: Paste range to location determined by IF statement

    Working fine for me..

    Can you please upload a sample file to check..

    In the mean while..
    in top of the code Page.. test by writing...

    Option Compare Text

    may be O3 have Cc instead of CC
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    Will upload shortly, thanks for having a look!

    I added "Option Compare Text" above sub line but no difference...

    Geoff.

  5. #5
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Simple: Paste range to location determined by IF statement

    The reason for putting the copy statement in all ifs is if all the conditions are false, the copy command will stay on unless you have application.cutcopy to zero.

  6. #6
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    Hi again Debraj Roy,

    I created a sample file, copied the code I posted here and as I ran the code on it, I found that suddenly all but one of the IF's worked. I'm not sure why now they mostly worked this time.

    Then I was able to figure the problem with the one that didn't work (below), it was that the paste destination overlapped with one of the pasted cell of the previous IF statement.

    Please Login or Register  to view this content.
    I feel a bit silly now. Thanks again for having a look and good day/night to you!

    Geoff.

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Simple: Paste range to location determined by IF statement

    Before you guessing why the code did not work, but now suddenly works, you need to use the right sheet reference. This will happen again if you are working with active sheet.

  8. #8
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    AB33, Thanks for the suggestion but is this code not correctly referencing every sheet?

  9. #9
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    OH, you mean this part: "If Range("O3")"....

  10. #10
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    How should that look? Like the following instead?

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Simple: Paste range to location determined by IF statement

    You have over 1 sheet on your workbook. The code will pick-up the sheet what ever the cursor happen to be.
    Your code properly referenced (Below). I could have done with 2 with statements, but I do not want to complicate things for you.

    Please Login or Register  to view this content.

  12. #12
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Simple: Paste range to location determined by IF statement

    Not fully verified with all condition..
    but you can reduce it if its is in a pattern..

    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    It's not complicating things for me. I am trying to learn and I recognize that this is good advice. Thanks for taking the time.

    Is the following (code) what you meant by "two statements"?

    Please Login or Register  to view this content.

  14. #14
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Simple: Paste range to location determined by IF statement

    Very good!
    But can complicate more ("With" with out the set), but will doze off for now and see you in your next adventure.

  15. #15
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    Thanks for that code Debraj. Is it only shorter code or is it more effiecient as well, or just shorter?

    In the following line:
    Please Login or Register  to view this content.
    - Why is it "T" in (Rows.Count, "T")?
    and
    - How can C be used in this way? I am not familiar with "Application.Match"
    - Also, in (C * 3), is it*3 because there are 3 cells that are being copied?

    If you do not have time to get into the details, not to worry. I do appreciate the same task can be achieved in many ways!

  16. #16
    Forum Contributor
    Join Date
    04-30-2014
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    113

    Re: Simple: Paste range to location determined by IF statement

    Sounds good AB33, thanks joining in on this one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy and Paste w/ Paste Destination determined by Cell Value
    By jeremy9er7 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2014, 10:42 AM
  2. paste static range into dynamic location with search
    By plague5050 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2014, 09:12 PM
  3. [SOLVED] VBA Macro to copy a range of cells and paste to a new location
    By JuneTwentyFourteen in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-20-2014, 10:38 AM
  4. Loop through range names for location of dynamic paste
    By AlvaroSiza in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-17-2011, 11:01 AM
  5. Replies: 1
    Last Post: 11-06-2010, 11:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1