+ Reply to Thread
Results 1 to 3 of 3

Cut and paste to new worksheet based on filter

  1. #1
    Registered User
    Join Date
    06-23-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    17

    Cut and paste to new worksheet based on filter

    Hi all

    I need to cut an entire row and paste into another workbook (named "Bonus trans cut") based on two criteria in the original workbook (named GL import). The criteria is in (column A and is based on nominal codes "50019" and "52019".)

    I then want to delete the whole rows and leave the filters on in GL import as it's a working sheet.

    I've got the following so far but it seems to be falling over and whatever I do it just won't work!


    Sub Bonus_Trans_Click()
    Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range
    Set sh1 = Sheets("GL Import")
    lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    Set sh2 = Sheets("Bonus trans cut")
    Set rng = sh1.Range("A1:AO" & lr)
    rng.AutoFilter Field:=1, Criteria1:="50019", Operator:=xlOr, Criteria2:="52019"
    rng.Offset(1, 1).Resize(rng.Rows.Count - 1, 41).SpecialCells(xlCellTypeVisible).Copy sh2.Cells(Rows.Count, 1)
    rng.Offset(1, 1).Resize(rng.Rows.Count - 1, 41).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    sh1.AutoFilterMode = True
    End Sub

    Thank you!

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

    Re: Cut and paste to new worksheet based on filter

    Try this.

    Before running this code, check the very last row at the bottom of your Bonus trans cut sheet. It may have values in it as your old code was writing to the last row on the sheet.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    06-23-2017
    Location
    UK
    MS-Off Ver
    2013
    Posts
    17
    Quote Originally Posted by AlphaFrog View Post
    Try this.

    Before running this code, check the very last row at the bottom of your Bonus trans cut sheet. It may have values in it as your old code was writing to the last row on the sheet.

    Please Login or Register  to view this content.
    Spot on! Thank you!

+ 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. [SOLVED] VBA to filter data, copy and paste into new worksheet
    By ngan202 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-14-2016, 08:12 PM
  2. VBA Filter, Copy, Paste onto New Worksheet
    By gssc141 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-10-2015, 10:35 AM
  3. [SOLVED] Macro which will filter and paste data to new worksheet
    By pauldaddyadams in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-18-2014, 09:29 AM
  4. [SOLVED] Need help with a macro to filter data and paste it in the corresponding worksheet
    By odoualex in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-17-2013, 02:35 PM
  5. One filter/ Master filter multiple pivot tables based on worksheet...
    By jlworden in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-10-2013, 11:16 AM
  6. vba Copy Filter item and paste at other worksheet
    By gingank in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2012, 06:21 AM
  7. Replies: 5
    Last Post: 03-18-2006, 08:45 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