Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2016

    Smile Unanswered: VBA to move rows in worksheet with certain cell criteria to a new worksheet

    Background: I was recently given a tracking spreadsheet that I need to edit using some VBA functionality. Unfortunately, I have limited experience with VBA and your help would be GREATLY appreciated.

    Question: How do I create functionality so that when I mark Column "Z" in my Working File spreadsheet as "Closed", it will automatically be moved to a new spreadsheet entitled "Closed" in the workbook?

    Attachment: I have attached an example file of the various columns that I have within this workbook.

    I tried to use the following code but it keeps giving me an error:
    Sub FilterToSheets()
    Dim SourceSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim SheetNames As Variant
    Dim i As Long
    Dim LR As Long

    Set SourceSheet = Sheets("Completed List")
    SheetNames = Array("Open", "Closed", "Closure Pending")
    Const FilterColumn = 3

    With SourceSheet
    LR = .Range("A" & .Rows.Count).End(xlUp).Row

    For i = 0 To UBound(SheetNames)
    Set TargetSheet = Worksheets(SheetNames(i))

    With .Range("A1:AC" & LR)
    .AutoFilter Field:=FilterColumn, Criteria1:=SheetNames(i)
    .Offset(0, 0).Copy TargetSheet.Range("A1")
    End With
    Next i

    End With

    End Sub
    Attached Files Attached Files
    Last edited by marzenie101; 01-14-16 at 11:22.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts