+ Reply to Thread
Results 1 to 6 of 6

Moving competed rows from sheet1 to sheet2 with vba

  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 with vba

    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 problem

    spread sheet:
    code looks like this

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Moving competed rows from sheet1 to sheet2 with vba

    your code works well for me, but eliminate auto_open and put a button for macro starting
    If solved remember to mark Thread as solved

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Moving competed rows from sheet1 to sheet2 with vba

    Hi Nathang,

    You have no records marked completed??
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

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

    Re: Moving competed rows from sheet1 to sheet2 with vba

    Quote Originally Posted by xladept View Post
    Hi Nathang,

    You have no records marked completed??

    xladept,

    I readjusted the table so that macro runs when you open. sheet two will have some rows where "m" is not completed

    thanks for your reply
    Attached Files Attached Files
    Last edited by nathang1; 07-28-2014 at 04:54 PM.

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

    Re: Moving competed rows from sheet1 to sheet2 with vba

    patel45

    I have attached the spread sheet before the macro running so you can see the rows that move do not have a completed date in column m

    thank for your reply

  6. #6
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Moving competed rows from sheet1 to sheet2 with vba

    You did not delete auto_open

+ 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. Moving competed rows from sheet1 to sheet2
    By nathang1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-27-2014, 09:20 AM
  2. Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  3. [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
  4. [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
  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

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