+ Reply to Thread
Results 1 to 16 of 16

Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Hi,

    I posted this yesterday afternoon but it seems to have disappeared.

    I am having some trouble getting a copy paste to work properly in VBA, tried all yesterday afternoon with no luck.

    I have teh following code which works well but the paste code will paste it into the required sheet and overwrite everything in its path, i tdont want this, i want to insert the copied cells "shifting cells down" but you cant seem to do this when copying a filtered range made up of inconsistent row numbers ie 1-3 4-5 10-15 etc etc

    I had thought about doing a COUNT of the rows to be copied and a INSERT ROWS into the new sheets before pasting but seems long winded??

    Thanks in advance

    Please Login or Register  to view this content.

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

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    I had posted a rely this thread yesterday
    Changes these lines
    Please Login or Register  to view this content.
    INTO
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor adyteo's Avatar
    Join Date
    01-10-2013
    Location
    Calgary
    MS-Off Ver
    Excel 2010
    Posts
    540

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Try this for getting the number of rows in the range. Then you just need to insert that number of rows in the target worksheet.:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Thats great thanks for the reply (twice) really dont know why my original post has gone.....

    I would really like to specify a particular cell to paste into and shift all existing data below that cell down?

    The code you gave pastes my data below all existing data

    Is this possible?

    Thanks

  5. #5
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Thanks adyteo, i was just trying to build something like this that would count the rows in the Filter range then insert rows based on that count

    I have the count bit sorted which is the same as what you put but trying to work out the insert bit

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Quote Originally Posted by Simon.Ward View Post
    Thanks adyteo, i was just trying to build something like this that would count the rows in the Filter range then insert rows based on that count

    I have the count bit sorted which is the same as what you put but trying to work out the insert bit
    This will insert n blank rows starting at row 2. The VisRowsCount is the count bit you said you have.

    Sheets("Data VP").Rows(2).Resize(VisRowsCount).Insert

  7. #7
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    This is what i have so far:

    Please Login or Register  to view this content.
    However it doesn't appear to be inserting the correct amount of rows?

    It has inserted 25 rows and looking at my filtered data the first row of filtered data is on row 25 so it has counted the hidden rows up until my filter data starts??? random

  8. #8
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Actually just realised because its a non-contiguous area thats selected its counting the rows in teh first group which happens to be row 25 to 49 so giving the insert of 25 rows

  9. #9
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Ok so managed a work around because it seems there is NO WAY for excel to count the total number of rows in a filtered selection because there is multiple areas

    This meant i had to copy/paste the data into a new sheet which would paste it as one block, then count the rows and insert that count into the review sheet then back to the new sheet and copy the one block into the newly inserted cells then go back and delete the tab

    See what you think

    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 06-11-2013 at 09:44 AM.

  11. #11
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Great thanks

    One last question, i want to specify where to paste the data in and i have 4 lots of data to paste under one another BUT because i am inserting rows the position i want to paste in will be forever changing

    In your code you have set "r" as the Rows count variable so if i am right can i add this to a Cell Reference to bump it down, ie: for the second batch of data to be pasted in under the first:

    Please Login or Register  to view this content.
    The position if nothing were to be pasted in from batch one would be cell A25, so any rows inserted from the "r" count i want to add on the row number of 25?

    Thanks

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Quote Originally Posted by Simon.Ward View Post
    Great thanks

    One last question, i want to specify where to paste the data in and i have 4 lots of data to paste under one another BUT because i am inserting rows the position i want to paste in will be forever changing

    In your code you have set "r" as the Rows count variable so if i am right can i add this to a Cell Reference to bump it down, ie: for the second batch of data to be pasted in under the first:

    Please Login or Register  to view this content.
    The position if nothing were to be pasted in from batch one would be cell A25, so any rows inserted from the "r" count i want to add on the row number of 25?

    Thanks
    If you're doing four different filters to get the four lots of data, then r would change for each filter. You may have to use another variable to have a running total of the number of rows inserted (e.g.; s = s + r) if I understand you correctly.

    Alternatively, insert the bottom lot first, and work your way up e.g.; insert lot 4 first, lot 3 insert above, Lot 2 insert above and lot 1 insert above.

  13. #13
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Yeh i know what you mean, i started it with using multiple versions of "r" but started to get very confusing so i will start from the bottom and work up, that way i can overwrite "r" for each and wont matter

    Thanks for your help

  14. #14
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    One quick bug fix, i havent yet built in an IF statement for if the filter return zero results and i try to copy or count it will debug, i have the following code but it still fails on the Result= line and debugs

    Please Login or Register  to view this content.
    What can i used instead?

    Thanks

  15. #15
    Registered User
    Join Date
    02-11-2013
    Location
    Guernsey
    MS-Off Ver
    Excel 2010
    Posts
    80

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Came up with this:

    Please Login or Register  to view this content.

  16. #16
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Copy/Paste from a Filtered Range and Insert copied data NOT overwrite

    Quote Originally Posted by Simon.Ward View Post
    Came up with this:

    Please Login or Register  to view this content.
    You could test if the last visible row is > 1

    If .Range("A" & Rows.Count).End(xlUp).Row > 1 Then
    Last edited by AlphaFrog; 06-12-2013 at 11:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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