+ Reply to Thread
Results 1 to 3 of 3

Moving competed rows from sheet1 to sheet2

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    columbus, ohio
    MS-Off Ver
    2003
    Posts
    5

    Moving competed rows from sheet1 to sheet2

    I can move completed rows (date populated "M" column) however I have more rows that are not completed moving with them. I have reviewed my code and can not find my problemWC DLV & BLD DATES.xls



    many thanks

  2. #2
    Valued Forum Contributor
    Join Date
    03-21-2013
    Location
    cyberia
    MS-Off Ver
    Excel 2007
    Posts
    457

    Re: Moving competed rows from sheet1 to sheet2

    could you post your code?

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    columbus, ohio
    MS-Off Ver
    2003
    Posts
    5

    Re: Moving competed rows from sheet1 to sheet2



    Sub auto_open()
    Worksheets(1).Select
    Worksheets(1).Range("A3:M500").Select
    Selection.Sort Key1:=Range("M1"), Order1:=xlAscending, Key2:=Range("A1") _
    , Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Worksheets(2).Select
    Worksheets(2).Range("A3:M500").Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
    , Order2:=xlAscending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Worksheets(2).Range("A3").Select
    Worksheets(1).Select
    Call purge_completed
    Worksheets(1).Select
    End Sub

    Sub purge_completed()
    On Error GoTo abort
    Application.DisplayAlerts = False
    Dim FilterCriteria
    Dim newrange As Range
    Range("A3:M500").Select
    'Apply Autofilter
    Selection.AutoFilter
    'FilterCriteria = "<>"
    Selection.AutoFilter field:=10, Criteria1:="<>"
    'Worksheets(1).AutoFilter.Range.Offset(1).Select
    Set newrange = Worksheets(1).AutoFilter.Range.Offset(1, 0).Resize(Worksheets(1).AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    newrange.Select
    'Selection.SpecialCells(xlCellTypeVisible).Select
    'Rows(1).Hidden = True
    'MsgBox (Selection.Rows.Count)
    If (Selection.Rows.Count = 1) And (Range("M1").Value = "PD DATE") Then GoTo abort
    'Copy the cells
    Selection.Copy
    Worksheets(2).Select
    Worksheets(2).Range("A3").Select
    Dim i As Integer
    For i = 3 To 500
    If ActiveCell.Value = Empty Then
    GoTo found_empty_cell
    Else
    Worksheets(2).Range("A" & i).Select
    End If
    Next i
    found_empty_cell:
    ActiveSheet.Paste
    'Clear the clipboard contents
    Application.CutCopyMode = False
    'Go back to the original file
    Worksheets(1).Select
    Selection.Delete
    'Clear the autofilter
    abort:
    Selection.AutoFilter field:=1
    'Take the Autofilter off
    Selection.AutoFilter
    Range("A3").Select
    GoTo endofsub
    'errorcatch:
    'MsgBox ("No completed items to move.")
    endofsub:
    End Sub

+ 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. Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  2. [SOLVED] Moving data from sheet1 to sheet2 and deleting sheet1 data
    By rdodd in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-24-2013, 06:38 PM
  3. [SOLVED] Extract moving information from Sheet1 and put it on Sheet2
    By dgfl in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-23-2013, 12:46 AM
  4. [SOLVED] macro to copy the formulas in Sheet2 to as many rows as rows available in Sheet1
    By flakedew in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-21-2012, 04:52 AM
  5. Moving selected rows from Sheet1 to Sheet2
    By guerreiropequeno in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-04-2011, 03:57 AM

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