Cut rows in one sheet and append them to another sheet

[joemaine]

I would like to cut from Sheet1 (A1:G100) all rows which have "Archive" in column G. The results would be appended to the next empty row in Sheet4. The remaining rows in Sheet1 in Range A1:G100 would shift up without affecting the existing conditional formatting rules that are applied to that Range. The rows below the Range A1:G100 would not shift. This code would be called when a button is selected on Sheet1. (Instructions for the button would also be appreciated)

 

[Yongle]

Try this
Assumes that next empty row can be determined by column A in sheet4

Sub ArchiveRows()
    Dim sh As Worksheet, rng1 As Range, rng2 As Range, cel As Range, r As Long
    Set sh = Sheets("Sheet1")
    Set rng1 = sh.Range("A1:G100")
    Set cel = Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)
    
    With rng1
        .AutoFilter
        .AutoFilter Field:=7, Criteria1:="Archive"
        On Error Resume Next
            Set rng2 = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            If Err.Number > 0 Then GoTo errhandling
    End With
    With rng2
        r = .Rows.Count
        .Copy cel
        .EntireRow.Delete
    End With
    sh.Cells(r, 1).Activate
    sh.Rows(101 - r & ":" & 101).Insert
    
errhandling:
rng1.Parent.ShowAllData
End Sub

Code for Form Control button

Sub Button1_Click()
    Call ArchiveRows
End Sub

 

[joemaine]

This is GREAT and it gets me 90% of the way to where I would like to be. It adjusts the conditional formatting rules to compensate for extra rows inserted in Sheet1 after an "Archive" is run. What I need now is to adjust another macro to read the new inserted rows. My macro with A1:G:100 is now not inclusive. I currently use the following in the workbook:
-----
Sheets("Sheet1").Range("A1:G100").Copy
Sheets("Sheet2").Range("A1:G100").Offset(0). _
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
-----
How can I change the above to look at all cells in Sheet1 (to the last entry in column A - there will be some blank cells) copy them and paste them in Sheet2? ...I'm great at cut and paste, not so much with code.

 

[Yongle]

I am not sure which range needs copying

My macro with A1:G:100 is now not inclusive

What do you mean by this?
- it always includes 100 rows

How can I change the above to look at all cells in Sheet1

Is there someting below A100 that should be copy/pasted?
- is last entry in column A the last row?

 

[joemaine]

I am not sure which range needs copying


What do you mean by this?
- it always includes 100 rows


Is there someting below A100 that should be copy/pasted?
- is last entry in column A the last row?

After running the Archive the worksheet expands to include additional rows. So now the worksheet includes more than 100 rows. I would like to copy all rows in Sheet1 (that have an entry in column A, maintaining empty rows) and copy this to Sheet2 (this would update and overwrite anything on Sheet2)

 

[Yongle]

Is sheet2 an exact replica of Sheet1?

 

[joemaine]

Is sheet2 an exact replica of Sheet1?

It is at first. I have a macro that runs when Sheet2 is opened that copies the data from Sheet1 and a second macro runs that deletes all rows in Sheet2 that have "Complete" in column 4. I'm sure a single macro would be better (but it would also be above my skill)

 

[Yongle]

How about

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Application.DisplayAlerts = True
    Sheets("Sheet1").Copy After:=Sheets(1)
    Sheets(2).Name = "Sheet2"

 

[joemaine]

How about

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Sheet2").Delete
    Application.DisplayAlerts = True
    Sheets("Sheet1").Copy After:=Sheets(1)
    Sheets(2).Name = "Sheet2"

It does work. The only problem is that it also includes some formulas and data beyond column 7 in Sheet1 that I'd rather not have in Sheet2. It also copies the ArchiveRows button (in the 8th column) from Sheet1 which doesn't need to be on Sheet2.

 

[Yongle]

How about ...

Sheets("Sheet1").Range("A:G").Copy Sheets("sheet2").Range("A1")