+ Reply to Thread
Results 1 to 21 of 21

Paste in First available Row

  1. #1
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Paste in First available Row

    Hello,

    Does anybody know how to alter this coding to have it paste in the first available row starting from row 2 on the "Cases Found" sheet. Right now its pasting over the current format and its causing me to lose alot of information. I believe the section I need altering done to is this part:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Thanks for your help.

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

    Re: Paste in First available Row

    It does copy in to row 2, unless you have data below this row.

    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Paste in First available Row

    Should not be there:
    Please Login or Register  to view this content.
    ?
    Regards
    Miroslav R.

    (If You like my solutions, feel free to add reputation.)

  4. #4
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    Whats happening is I have around 40 files on Sheet 1 and for some reason when it pastes in sheet "Cases Found" it starts in row 2 and paste the data. It should then move onto row 3 and continue pasting the data that matches the criteria. For some reason its overlapping and repasting in row2 on most of the files. I'm just trying to see is there a counting loop or something that somebody knows how to put in to stop this?

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

    Re: Paste in First available Row

    This could happen if the paste in column A is blank ,i.e. when you paste the entire row, but column A is blank, the code assumes that the next blank in column A is that row, though, this is not the case.
    Last edited by AB33; 05-30-2014 at 09:34 AM.

  6. #6
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    Ok, makes sense but when I change the criteria to look for an empty cell in Column B, because Column B always has information in it. I get the following error:

    Run-Time error '1004':
    We can't paste because the Copy Area and paste Area aren't the same size.

    Please Login or Register  to view this content.

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

    Re: Paste in First available Row

    Yes, you can paste to column A ONLY if you copy an entire row. I was going to suggest, you should change the entire range to range A-F for example.

  8. #8
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    What would be the code for something like that?

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

    Re: Paste in First available Row

    Please Login or Register  to view this content.
    Go 3 rows to left(Which takes you to column A and resize to column J(10)

  10. #10
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    Where in the orginal code would that go. Because I placed it where this was:
    Please Login or Register  to view this content.
    and I'm getting an error. I appreciate the help in solving this problem for me.

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

    Re: Paste in First available Row

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    Unfortunately, thats still not working. I included a sample workbook to show exactly the kind of stuff I'm working with.

    Test.xlsm

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

    Re: Paste in First available Row

    There are a couple of errors. If you could add more samples to the attached, I could write you a new code.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    I added a few more examples. Hopefully this helps. Thanks again.

    Test (2).xlsm

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

    Re: Paste in First available Row

    Try the attached. If you delete a row, it affects the pattern, but if you clear it, it work. If this insist, you want to delete it, I will adjust the code.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    Thank You, I like the way it works. Unfortunately, the reason why I delete the empty rows is because I have many more macros after this process. This is just the very first macro that runs. Is there a way to keep this code and then have a separate macro to go back to sheet 1 and delete all the empty rows so this way it doesn't affect the pattern too much?

  17. #17
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Paste in First available Row

    Disregard.
    Looks like it has been solved.

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

    Re: Paste in First available Row

    Amended code.
    Attached Files Attached Files

  19. #19
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    Sorry for the delay. Thanks for the program. The only problem from the original is that it suppose to look for all variations of the wording it finds on Sheet1. So for Pin Man 654895 If on the second sheet I have in Column D Case 654895. It should recognize that as a match. I'm having this macro take a value found on sheet 1 and search 60k values on sheet 2 and unfortunately not everyone puts this in the same format. I reattached an updated file. The only one that should be left on Sheet 1 is Guy New 9568. Hopefully you can help me with this.

    Test.xlsm
    Attached Files Attached Files

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

    Re: Paste in First available Row

    You have not attached any new data.
    You could try changing this line.

    Please Login or Register  to view this content.
    But this partial match might give you some unwanted matchs, so the alternative would be a loop, but 65k with a loop would be a slow code.
    I need to see in a sample which ones are exact match and close match.

  21. #21
    Forum Contributor
    Join Date
    06-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Paste in First available Row

    That works though, because I have other macros to catch unwanted matches. Thanks so much for your help.

+ 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 formula and paste for new data added and autofill.....and paste special values
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-13-2014, 07:40 AM
  2. Replies: 7
    Last Post: 02-04-2014, 06:42 PM
  3. Trap Catch Differentiate Disable Paste Button from Paste Special Options
    By m3atball in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-23-2013, 07:28 PM
  4. [SOLVED] Need to modify the Paste function of this VBA Macro from "Paste" to "Paste Special Values"
    By zicitron in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 03:44 AM
  5. [SOLVED] how can I paste text using paste special, without clicking paste button?
    By Exxcel Noob in forum Excel General
    Replies: 6
    Last Post: 05-14-2012, 08:21 PM

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