Thanks Thanks:  0
Likes Likes:  0
Page 1 of 7 123 ... LastLast
Results 1 to 10 of 66

Thread: Macro to Move Cells to Archive Sheet

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Macro to Move Cells to Archive Sheet

    Hi there, longtime user firsttime poster. Looking for some help as I am a non-expert with macros. Here's what I'm trying to do:

    We have to submit things to a certain regulatory body and we usually enter tasks in as soon as they come, do the submission, and then keep a record of that submission.

    So, I have a workbook with two sheets, one is "TO DO", the other is "ARCHIVE". Both sheets have the same columns and everything. I am looking for a macro that will automatically cut a (row) from the TO DO sheet and paste it in into the ARCHIVE sheet once it is done, then delete the cut row from the TO DO list so it stays topped up.

    The trigger for archiving is the columns M and N which are titled "Complete ?" and each has a validation drop down that says "YES". When both cells in columns M and N have the YES in them, I would like the macro to make the above mentioned actions.

    I ran a search on the forums and found something similar, but not quite what I was looking for.

    Any help?

  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    main data in sheet "TO DO" is from A1 with no blank rows or columns
    you have another sheet "ARCHIVES"

    try this macro
    Code:
    Sub test()
    Dim r As Range, filtr As Range
    With Worksheets("TO DO")
    Set r = .Range("A1").CurrentRegion
    r.AutoFilter field:=.Range("M1").Column, Criteria1:="yes"
    r.AutoFilter field:=.Range("N1").Column, Criteria1:="yes"
    Set filtr = r.SpecialCells(xlCellTypeVisible)
    'MsgBox filtr.Address
    Set filtr = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    
    'MsgBox filtr.Address
    filtr.Copy
    With Worksheets("ARCHIVES")
    .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
    End With
    filtr.EntireRow.Delete
    r.AutoFilter
    End With
    End Sub
    Last edited by venkat1926; May 21st, 2011 at 12:44 AM.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,421
    Post Thanks / Like
    Mentioned
    79 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Welcome to the MrExcel board!

    You can make this happen automatically. Test in a copy of your workbook.

    I have assumed that
    a) data starts in column A and
    b) that column A always has an entry in it by the time the two 'completed' columns are filled with 'YES'

    To implement ...

    1. Right click the 'TO DO' sheet name tab and choose "View Code".

    2. Copy and Paste the code below into the main right hand pane that opens at step 1.

    3. Close the Visual Basic window.

    4. Try making changes in the sheet (especially columns M:N)



    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Changed As Range
        
        Const YesCols As String = "M:N" '<- Your 'completed' columns
        
        Set Changed = Intersect(Target, Columns(YesCols))
        If Not Changed Is Nothing Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            With Intersect(ActiveSheet.UsedRange, Columns(YesCols))
                .AutoFilter Field:=1, Criteria1:="=YES"
                .AutoFilter Field:=2, Criteria1:="=YES"
                With .Offset(1).EntireRow
                    .Copy Destination:=Sheets("ARCHIVE") _
                        .Range("A" & Rows.Count).End(xlUp).Offset(1)
                    .Delete
                End With
                .AutoFilter
            End With
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub
    Last edited by Peter_SSs; May 21st, 2011 at 01:04 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    May 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Thanks guys this works like a charm! You're the best

  5. #5
    Board Regular
    Join Date
    Feb 2013
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Macro to Move Cells to Archive Sheet

    Quote Originally Posted by SpacemanSpif View Post
    Thanks guys this works like a charm! You're the best
    This is something like what i have been trying to do with a spreadsheet for sometime. I have exactly the same name for my spread sheets. TO DO & ARCHIVE. I want to send my data to the archive based on the info in one column which is row column J when this has been changed to yes.

    I am very new to macro's and this would save me so much time at my work, where i am currently pasting copying and deleting half of my day on an ever increasing work load. If someone could help me with this I would really appreciate it. My data start from A3 to J3 and has an max rows. Above those rows is headers and a tile.

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,421
    Post Thanks / Like
    Mentioned
    79 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Quote Originally Posted by Desmondo View Post
    This is something like what i have been trying to do with a spreadsheet for sometime. I have exactly the same name for my spread sheets. TO DO & ARCHIVE. I want to send my data to the archive based on the info in one column which is row column J when this has been changed to yes.

    I am very new to macro's and this would save me so much time at my work, where i am currently pasting copying and deleting half of my day on an ever increasing work load. If someone could help me with this I would really appreciate it. My data start from A3 to J3 and has an max rows. Above those rows is headers and a tile.
    Assuming your column J is not the result of a formula, you don't need many changes from the code posted above. Try this in a copy of your workbook. Implementation instructions in post #3.
    Assumptions in post #3 also need to be true - post back with details if they are not.


    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Changed As Range
        
        Const YesCol As String = "J" '<- Your 'completed' column
        
        Set Changed = Intersect(Target, Columns(YesCol))
        If Not Changed Is Nothing Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
                .AutoFilter Field:=1, Criteria1:="=YES"
                With .Offset(1).EntireRow
                    .Copy Destination:=Sheets("ARCHIVE") _
                        .Range("A" & Rows.Count).End(xlUp).Offset(1)
                    .Delete
                End With
                .AutoFilter
            End With
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub

    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  7. #7
    Board Regular
    Join Date
    Feb 2013
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Quote Originally Posted by Peter_SSs View Post
    Assuming your column J is not the result of a formula, you don't need many changes from the code posted above. Try this in a copy of your workbook. Implementation instructions in post #3.
    Assumptions in post #3 also need to be true - post back with details if they are not.


    Private Sub Worksheet_Change(ByVal Target As Range)
    ****Dim Changed As Range
    ****
    ****Const YesCol As String = "J" '<- Your 'completed' column
    ****
    ****Set Changed = Intersect(Target, Columns(YesCol))
    ****If Not Changed Is Nothing Then
    ********Application.EnableEvents = False
    ********Application.ScreenUpdating = False
    ********With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
    ************.AutoFilter Field:=1, Criteria1:="=YES"
    ************With .Offset(1).EntireRow
    ****************.Copy Destination:=Sheets("ARCHIVE") _
    ********************.Range("A" & Rows.Count).End(xlUp).Offset(1)
    ****************.Delete
    ************End With
    ************.AutoFilter
    ********End With
    ********Application.EnableEvents = True
    ********Application.ScreenUpdating = True
    ****End If
    End Sub

    Hi Peter,

    Thanks for your reply i have amended the sheet a little and it is now row I that contains the 'Yes' and this the final column of the book. I am doing this in excel 2003 as it's my work computers and they have not yet upgraded. I also have tidied up the sheet by hiding all teh unused columns if this makes a difference.

    I have entered the code but i am getting errors. The code i have entered is as follows-

    Sub Archive()
    Const YesCol As String = "J" '<- Your 'completed' column

    Set Changed = Intersect(Target, Columns(YesCol))
    If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
    .AutoFilter Field:=1, Criteria1:="=Yes"
    With .Offset(1).EntireRow
    .Copy Destination:=Sheets("ARCHIVE") _
    .Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Delete
    End With
    .AutoFilter
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End If
    End Sub

    i am getting errors however when i try to run it. Runtime error 424, object required?

    As i say i am completely new to this, but find it very interesting. Hope you can help me further.

  8. #8
    Board Regular
    Join Date
    Feb 2013
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Also would like to use this code but cannot get it to work (the automated code from post 5)

    Have data from row 8 and column A:N validation list in column O, not all cells have data but always data in Col A.

    validation entries are "LIVE" or "DEAD", archive sheet is "Dead Deals", same layout get a 1004 runt time error and it appears it doesnt like the following

    With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)


    Any help greatly appreciated.

    Kind Regards

  9. #9
    Board Regular
    Join Date
    Feb 2013
    Posts
    70
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Quote Originally Posted by Desmondo View Post
    Hi Peter,

    Thanks for your reply i have amended the sheet a little and it is now row I that contains the 'Yes' and this the final column of the book. I am doing this in excel 2003 as it's my work computers and they have not yet upgraded. I also have tidied up the sheet by hiding all teh unused columns if this makes a difference.

    I have entered the code but i am getting errors. The code i have entered is as follows-

    Sub Archive()
    Const YesCol As String = "J" '<- Your 'completed' column

    Set Changed = Intersect(Target, Columns(YesCol))
    If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
    .AutoFilter Field:=1, Criteria1:="=Yes"
    With .Offset(1).EntireRow
    .Copy Destination:=Sheets("ARCHIVE") _
    .Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Delete
    End With
    .AutoFilter
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End If
    End Sub

    i am getting errors however when i try to run it. Runtime error 424, object required?

    As i say i am completely new to this, but find it very interesting. Hope you can help me further.



    From my own pc with excel 2010 i get the following error

    Compile error constant expression required on this line of code Set Changed = Intersect(Target, Columns(YesCol))

    Maybe this helps

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,421
    Post Thanks / Like
    Mentioned
    79 Post(s)
    Tagged
    16 Thread(s)

    Default Re: Macro to Move Cells to Archive Sheet

    Quote Originally Posted by Desmondo View Post
    Hi Peter,

    Thanks for your reply i have amended the sheet a little and it is now row I that contains the 'Yes' and this the final column of the book. I am doing this in excel 2003 as it's my work computers and they have not yet upgraded. I also have tidied up the sheet by hiding all teh unused columns if this makes a difference.

    I have entered the code but i am getting errors. The code i have entered is as follows-

    Sub Archive()
    Const YesCol As String = "J" '<- Your 'completed' column

    Set Changed = Intersect(Target, Columns(YesCol))
    If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    With Intersect(ActiveSheet.UsedRange, Columns(YesCol)).Offset(1)
    .AutoFilter Field:=1, Criteria1:="=Yes"
    With .Offset(1).EntireRow
    .Copy Destination:=Sheets("ARCHIVE") _
    .Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Delete
    End With
    .AutoFilter
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End If
    End Sub

    i am getting errors however when i try to run it. Runtime error 424, object required?

    As i say i am completely new to this, but find it very interesting. Hope you can help me further.
    Desmondo

    My code is designed to move the rows from TO DO to ARCHIVE immediately the relevant column is changed to "yes". If you don't want that, that is you want to wait and manually trigger the cleanup of rows to ARCHIVE, then please post back to say so & I will suggest some alternative code.

    Assuming you want it to happen immediately (which is what was happening originally in this thread) then ..

    a) You must not change the first line from Private Sub Worksheet_Change(ByVal Target As Range)

    b) The code must be placed in the TO DO worksheet module. I'm not sure if you have done that or not but if you follow the implementation steps 1 & 2 I outlined in post #3 the code will be in the right place.

    c) If the "Yes" values are now in column I all you need to change is the red "J" that I have highlighted in your above to "I"

    Depending on your vba settings it may not stop the code from running, but I'm wondering why you also removed the Dim Changed As Range line from my code.
    Last edited by Peter_SSs; Feb 28th, 2013 at 05:10 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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