Inserting a Visible Autofiltered Row into Another Sheet (Excluding Header)

[dreen]

I am trying to AutoFilter (in column A of SHEET 1) the Active Cell in SHEET 2. Then I have an IF Statement that counts the number of Visible Rows, and if it is more than 1 (to exclude the header) then I would like to insert a new row into SHEET 3 and cut and paste the values of the Auto filtered Row in SHEET 1 into the new row in SHEET 3.

Then I clear the Auto Filter in SHEET 1, and insert a new row into SHEET 1 and cut and paste the values of the Active Cell's Row from SHEET 2 into the new row in SHEET 1. IF there are no results from the Auto Filter in SHEET 1, then the ELSE STATEMENT clears the Auto Filter in SHEET 1, inserts a new row into SHEET 1 and cut and pastes the values of the Active Cell's Row from SHEET 2 into the new row in SHEET 1.

Currently, I can't seem to get my code to work if the Auto Filter results in SHEET 2 are in any rows > Row 2. Here is my current code, I have commented to help with navigation:

Sub Autofilter_Macro()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet

Set sh1 = Sheet1
Set sh2 = Sheet2
Set sh3 = Sheet3

Dim rng As Range
Dim AC As Integer
AC = ActiveCell.Row

sh1.AutoFilterMode = False 'Clears any AutoFilters from Sheet1

sh1.Range("A:A").AutoFilter Field:=1, Criteria1:=ActiveCell.Value 'AutoFilters SHEET 1 column "A" based off the ActiveCell Row in SHEET 2

Set rng = sh1.UsedRange.SpecialCells(xlCellTypeVisible) 'Sets rng to visible cells

    If (rng.Rows.Count > 1) Then 'Counts the # of visible rows

        sh3.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 'Inserts an empty row (with the same format as the one below it) into row 2 of SHEET 3

        sh3.Range("A2:CK2").Value = rng.Offset(rowOffSet:=1).Value 'Sets the new empty row's values in SHEET 3 = the values of the Autofiltered row in SHEET 1

        sh1.ShowallData 'Clears any Autofilters from SHEET 1

        sh1.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 'Inserts an empty row (with the same format as the one below it) into row 2 of SHEET 1

        sh1.Range("A2:CK2").Value = sh2.Range(Cells(AC, 1), Cells(AC, 89)).Value 'Sets the new empty row's values in SHEET 1 = the values of the ActiveCell row in SHEET 2

        MsgBox "Replaced Main Database" 'MsgBox indicating what has executed

    Else

        sh1.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 'Inserts an empty row (with the same format as the one below it) into row 2 of SHEET 1

        sh1.Range("A2:CK2").Value = sh2.Range(Cells(AC, 1), Cells(AC, 89)).Value 'Sets the new empty row's values in SHEET 1 = the values of the ActiveCell row in SHEET 2
         MsgBox "New Entry into Main Database"

    End If

sh1.ShowallData 'Clears any Auotfilters from SHEET 1

End Sub

I am cross referencing my post on another Forum as well:

How to Insert a Visible Autofiltered Row into Another Sheet (Excluding Header)

I am trying to AutoFilter (in column A of SHEET 1) the Active Cell in SHEET 2. Then I have an IF Statement that counts the number of Visible Rows, and if it is more than 1 (to exclude the header) t...

stackoverflow.com

 

[dreen]

@dreen
In future please supply links to ALL sites where you have asked a question, not just one of them.
Thanks

Not a problem