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!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top