+ Reply to Thread
Results 1 to 17 of 17

Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

  1. #1
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Hi, I'm currently working on a macro which would allow me to cut & paste filtered data from one sheet to another, then come back to the original sheet and get rid of the blank rows that are left. Originally, I was able to get around the problem of cutting unwanted data by sorting several columns and then it somehow worked but now the order of data is more random and from the research I've done, it looks like I'll have to use 'special cells'. However, I can't get it work properly and the macro now crashes on the 'Cut' line.


    I'd greatly appreciate if you could please help me out with this.


    Code:
    Please Login or Register  to view this content.
    I know there are most likely better, more efficient ways to go about this than what I've done above so by all means feel free to change/remove code.
    Thanks in advance!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Hi and welcome.

    It will be easier to help you if you can attach the workbook so we can see the issue in context.

    BSB

  3. #3
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Hi BSB, appreciate the timely response. Unfortunately, I won't be able to do so due to sensitive data... Sorry about that. However, I can make something that would mirror it more or less if you think that'd help.
    Basically, I just want to to filter data in Sheet 1 based on two criteria in column 10, then filter with regex in column 12, cut only the visible cells, paste it at the bottom of the table in Sheet 2 (size of records always varies), go back to Sheet 1 and remove empty rows.

    Hope that helps!

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    I meant a desensitized version of it rather than the real deal, but at 6am when I responded my brain must have forgotten to type that bit!

    Attach something with the same data layout as your original but using dummy (but representative) data and I'll put something together for you.

    BSB

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks


    Hi !

    You must at least check if something to cut exists as SpecialCells crashes when there is nothing.

    As Copy & Delete methods work only with visible cells so SpecialCells is useless …

  6. #6
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Hi BSB, sorry, I should've thought about that too!
    Please see attached.

    As mentioned, cut from Sheet 2, Column J, rows which have (('Filter - X' OR 'Filter - Y') AND 'Copy Me' in Column L), leave the rest, paste at bottom of Sheet 1 (size always varies) and delete blanks from Sheet 2.

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Rather than filter, copy then delete, you could loop through the rows like this:
    Please Login or Register  to view this content.
    BSB

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    Just using a filter at once as a beginner starter :

    PHP Code: 
    Sub Demo1()
             
    Application.ScreenUpdating False
        With Sheet2
    .UsedRange
            
    .AutoFilter 12"Copy Me"
            
    .Offset(1).Copy Sheet1.Cells(Rows.Count1).End(xlUp)(2)
            .
    Offset(1).EntireRow.Delete
            
    .AutoFilter 12
        End With
             Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    @Marc L - Whilst your code will work on the sample data provided, there is a chance there could be instances where column L contains "Copy Me" but column J does not contain "Filter - X" or "Filter - Y". The OP specifically stated both should be taken account of.

    BSB

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks


    Yes I always work only with the attachment supplied but « as a beginner starter » the OP can easily add another filter,
    easy to use the Macro Recorder or even manually just reading the VBA help …

  11. #11
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Try this !

    BSB, thank you very much for that! It also makes it much simpler too...
    That said, what's happening now is that everything works fine until I try to run it with the 'And' part in the loop.
    I'm simply trying to use regex like this:
    Please Login or Register  to view this content.
    It can't be "Copy Me" exactly as each record contains different data and there is only this 'copy me' part which is present in all of the ones which should be copied. E.g. xxxx Copy Me xxxxx OR test123 Copy Me test321. The macro doesn't even start when I try to run it with this 'And' part.

    Marc L, Appreciate your input but this filter is not going to work in this case. It also needs to include Column 10 filtering as well as make sure that no hidden cells are being copied.

    Thanks for your help.

  12. #12
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks


    At beginner level, you can easily mod it !

  13. #13
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Administrative Note:

    Welcome to the forum.

    We would love to help you with your query; however, it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    HTH
    Regards, Jeff

  14. #14
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Quote Originally Posted by jeffreybrown View Post
    Administrative Note:

    Welcome to the forum.

    We would love to help you with your query; however, it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
    Hello Jeff, I understand and will make sure to do so in the future. Thank you.
    Here's the link: mrexcel(dot)com/forum/excel-questions/1098943-cut-paste-special-cells-sheet-1-sheet-2-then-delete-blanks(dot)html (unable to post proper link due to limited posts)

    //EDIT
    This has now been solved. Please see the solution below.
    However, if possible, I'd appreciate if I could still get a reply as to why the above loop is not working because I'm curious why it's not.

    Please Login or Register  to view this content.
    Last edited by Frenzyy; 05-26-2019 at 09:27 AM.

  15. #15
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Quote Originally Posted by Frenzyy View Post
    However, if possible, I'd appreciate if I could still get a reply as to why the above loop is not working because I'm curious why it's not.
    It's to do with the wildcard characters. If you use the Instr function the loop will work.
    Please Login or Register  to view this content.
    BSB

  16. #16
    Registered User
    Join Date
    05-26-2019
    Location
    UK
    MS-Off Ver
    2016
    Posts
    50

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    Interesting, thank you for taking the time to explain & help with this.

  17. #17
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Cut & Paste Special Cells from Sheet 1 to Sheet 2, Then Delete Blanks

    No problem. Happy to help.

    BSB

+ 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. Data Copy from one sheet and paste special in another sheet
    By rajeev.raj in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-17-2019, 01:19 PM
  2. [SOLVED] Paste Special from One Sheet to Another Where the Sheet Name is Defined in a Cell
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-10-2019, 10:42 AM
  3. Macro to special paste values and transpose them to varying cells on another sheet
    By Jenkins87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2016, 02:41 PM
  4. Macro to special paste values and transpose them to varying cells on another sheet
    By Jenkins87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2016, 12:47 PM
  5. [SOLVED] Copy and paste random cells values from a range to another sheet column with no blanks
    By macquhele in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-17-2015, 06:05 PM
  6. Replies: 0
    Last Post: 05-24-2010, 05:17 PM
  7. Replies: 1
    Last Post: 07-26-2006, 06:25 PM

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