macro: copy numbers to bottom of filtered table

[halocore]

Hi. I'm a bit of a novice at VB macros, so I was hoping to get some thought on a problem I'm having with one.

Basically, I want a macro that copies a table of numbers (the quantity of which will frequently change) to the bottom of a list on a second sheet (who's bottom will also frequently change). Essentially, updating the second "master" list with the numbers from the first "working" list. This is the formula I have been using:

Sub Issue_Copy()
'
' New_Issue_Copy Macro
'
' Sheets("Enter New Numbers").Unprotect Password:="PASS"
    Sheets("Enter New Numbers").Select
    Range("I1:I" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, 1).Copy
    With Worksheets("Master List")
    .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
' Sheets("Enter New Issues").Protect Password:="PASS"
End Sub

This formula works great. However, we have introduced filtering into the "master" table. This has caused a problem where the bottom rows of the master table will sometimes get filtered out. Running the macro in these conditions causes the new numbers to be copied over starting in whatever is the row after the last visible row, overwriting anything that is already in there. (Example: last visible row from filtering will be 50 and first blank row will actually be 54, but the macro copies the new numbers starting at 51, overtop of existing, just hidden, numbers).

What I'm hoping someone can help me with is introduce an element into the current macro that either is able to better tell where the actually first blank row of the table is (even if previous ones are hidden from visibility from the filter) or a line that will remove all filter / set all filters to "Select all" before copying, and then restore them to how they were after copying.

I'll be happy to explain fuller if it's any help. Thanks in advance for any help you can offer!

 

[VoG]

Try

Sub Issue_Copy()
'
' New_Issue_Copy Macro
'
' Sheets("Enter New Numbers").Unprotect Password:="PASS"
    Sheets("Enter New Numbers").Select
    Range("I1:I" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeFormulas, 1).Copy
    With Worksheets("Master List")
        LR = .Columns("C").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
        .Range("C" & LR + 1).PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
' Sheets("Enter New Issues").Protect Password:="PASS"
End Sub

 

[halocore]

Hmm, unfortunately it seems to produce the same result . Items still copy to whatever row is after the last visable row, not the actually first empty row. Thanks for the suggestion though! Perhapes this means I should shift my focus on a macro for changing the filters, rather than changing the copy perameters?