+ Reply to Thread
Results 1 to 5 of 5

VBA cut from sheet 1 with multiple criteria onsheet 2,paste to next blank row sheet3

Hybrid View

  1. #1
    Registered User
    Join Date
    01-06-2012
    Location
    clearwater, Fl
    MS-Off Ver
    Excel 2007
    Posts
    4

    VBA cut from sheet 1 with multiple criteria onsheet 2,paste to next blank row sheet3

    I have been trying to solve this for days (boss is not to happy)

    I have multiple sheets in a workbook, I currently use a multiple criteria look up from 1 sheet to delete entire rows from another sheet.

    now i need to do much the same except i have to cut and paste.

    Sheet 1 sheet 2 sheet 3
    a b a a b

    2 4700 4700 2 4700
    1 22 4701 3 4798
    5 5001 4702
    3 4798 4703 delete above from sheet 1
    7 2205 4704
    4705
    etc to 4799


    here is the code im trying to use, it just wont copy paste and then delete!


    Sub cutpaste()
        Dim rng As Range
        Dim cell As Range
        Dim CriteriaRng As Range
        Dim CalcMode As Long
        Dim My_Range As Range
     
        Set My_Range = Worksheets("monday hillsborough 1300").Range("A10:h" & lastrow(Worksheets("monday hillsborough 1300")))
        My_Range.Parent.Select
     
     
     
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
        With Sheets("qa-qos")
            Set CriteriaRng = .Range("a1", .Cells(Rows.Count, "a").End(xlUp))
        End With
        'Loop through the cells in the Criteria range
        For Each cell In CriteriaRng
            With Sheets("Monday Hillsborough 1300")
                'Firstly, remove the AutoFilter
                .AutoFilterMode = False
                'Apply the filter
                .Range("c1:c" & .Rows.Count).AutoFilter Field:=3, Criteria1:=cell.Value
     
    My_Range.Parent.AutoFilter.Range.Copy
    With Sheets("monday qa-qos")
    AutoFilter.Range.Paste
     
                Application.CutCopyMode = False
                .Select
            End With
     
                With .AutoFilter.Range
                    Set rng = Nothing
                    On Error Resume Next
                    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                              .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                    If Not rng Is Nothing Then rng.EntireRow.Delete
                End With
                'Remove the AutoFilter
                .AutoFilterMode = False
            End With
        Next cell
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With


    End Subit seems so simple but im not an expert. Please help i am way past a deadline.

    Thank you in advance!!
    Kevinsnewmatrix

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA cut from sheet 1 with multiple criteria onsheet 2,paste to next blank row she

    Hi,

    Why can't you simply use Data Filter Advanced (with a criteria range) to filter your data and then if you want to delete it use the SpecialCells(xlCellTypeVisible) syntax to delete the rows, or if copying just copy the filtered rows to the relevant sheet. This can be achieved with a couple of lines of code, e.g (untested)

    Range("MyRange").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("MyCopyLocation"), CriteriaRange:=Range("MyCriteria")
    Range("MyRange").CurrentRegion.Offset(1,0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-06-2012
    Location
    clearwater, Fl
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA cut from sheet 1 with multiple criteria onsheet 2,paste to next blank row she

    that sounds great...need to delete, but where would i put it?? and will it loop to get all the cells in the criteriarange? (a1:a)

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA cut from sheet 1 with multiple criteria onsheet 2,paste to next blank row she

    Hi,

    Where would you put what? You're deleting cells so presumably don't need to put them anywhere. If you're copying the rows somewhere then use a Copy and PasteSpecial syntax.

    You'll need to build a unique list of criteria values, then just wrap the code I gave you inside a For..Next loop which first moves down the list of values and copies each one to the relevant criteria cell.

    Regards

  5. #5
    Registered User
    Join Date
    01-06-2012
    Location
    clearwater, Fl
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA cut from sheet 1 with multiple criteria onsheet 2,paste to next blank row she

    ok i see...sheesh Im a novice still so......Thanks..Ill let you know how it goes!!!!!

    Thank you!!

+ 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