macro: copy numbers to bottom of filtered table

halocore

New Member
Joined
Jan 28, 2011
Messages
5
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:
Code:
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!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

New Member
Joined
Jan 28, 2011
Messages
5
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,709
Messages
5,524,433
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top