1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

cut and paste filtered data

Discussion in 'Business Applications' started by spooky1, Jan 25, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    I was given this exelento code by one of the wizzes here a while back

    Sheets("newusd").Range("h41:j45").Copy
    x = Sheets("sendquote").Range("e26000").End(xlUp).Row
    Sheets("sendquote").Range("e" & x + 1).PasteSpecial xlValues
    Application.CutCopyMode = False

    But my need now is to cut and paste filtered data to new sheet then delete the filtered data from
    the original source. I tried a few things but my time is running out, I can hear my maker calling out
    my name!:eek:
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    Change this...
    Code:
    Sheets("newusd").Range("h41:j45").Copy
    .. to this ...
    Code:
    Sheets("newusd").Range("h41:j45").SpecialCells(xlCellTypeVisible).Copy
    Note that if there is no data in that filtered range (it's all filtered) this line will result in a produced error, so you should do some error trapping with it. Maybe something like...
    Code:
    On Error Resume Next
    Sheets("newusd").Range("h41:j45").SpecialCells(xlCellTypeVisible).Copy
    If Err.Number <> 0 Then
        MsgBox "There was no data to move!", vbExclamation, "ERROR!"
        Exit Sub
    End If
    On Error GoTo 0
    HTH
     
  3. spooky1

    spooky1 Thread Starter

    Joined:
    Jan 29, 2010
    Messages:
    126
    1: I can already do the Data sort and then filter to only show the records I need (all relevant data is now at top of list)

    2: MOVE, not copy the filtered data to another sheet
    3: Remove/delete filtered data in source sheet (original)
    4: reset the filter on source sheet and put all data that was not copied across at top of list

    a macro to do steps 2,3 & 4 in one hit would be nice

    I just realized I posted the wrong code earlier. This is the code that just copies already filtered data across to another sht.
    :rolleyes:
    Sheets("m3").AutoFilter.Range.Offset(1, 0).Copy
    x = Sheets("m-multi").Range("a" & Rows.Count).End(xlUp).Row
    Sheets("m-multi").Range("a" & x + 1).PasteSpecial xlValues
    Application.CutCopyMode = False
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yeah, I thought it was weird, the code you posted, I just figured you had other code and was looking for other syntax. Do you have a sample file you can post? You don't specify what range of data you're working with or anything about your data structure, just your code. Or we can be generic if you want. Just let us know.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - paste filtered data
  1. Bence
    Replies:
    5
    Views:
    333
  2. snoozee
    Replies:
    3
    Views:
    464
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/977016

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice