Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Copying Filtered Data
I am running a Macro which filters data into 4 separate workbooks. I then need to copy the filtered data into the one workbook but when I do this it is copying the full 65,000 rows. Is there something I can record in a Macro that will only copy down to the last cell with a value in it. The number of rows is going to change each time the macro runs so I can't select the filtered data by itself. thanks -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=381239 |
#2
|
|||
|
|||
Does the problem exist in the Filtered range or does it exist in your code that
does the copying? If you're filtering the whole worksheet, then this won't work. But if you're copying rows (from 2 to the bottom of the worksheet--not the bottom of the filtered range), it should work ok. Option Explicit Sub testme() Dim fWks As Worksheet Dim tWks As Worksheet Dim rng As Range Dim rngF As Range Dim destCell As Range Set fWks = Worksheets("sheet1") Set tWks = Worksheets("sheet2") With tWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With With fWks Set rng = .AutoFilter.Range With rng If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then MsgBox "No detail rows shown!" Else Set rngF = .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0) rngF.Copy _ Destination:=destCell End If End With End With End Sub This actually copies the details--it doesn't include the autofilter header row. Shirley Munro wrote: I am running a Macro which filters data into 4 separate workbooks. I then need to copy the filtered data into the one workbook but when I do this it is copying the full 65,000 rows. Is there something I can record in a Macro that will only copy down to the last cell with a value in it. The number of rows is going to change each time the macro runs so I can't select the filtered data by itself. thanks -- Shirley Munro ------------------------------------------------------------------------ Shirley Munro's Profile: http://www.excelforum.com/member.php...nfo&userid=836 View this thread: http://www.excelforum.com/showthread...hreadid=381239 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Adding more source data to existing scatter plot | Charts and Charting in Excel | |||
Conditional data validation (using a filtered range?) | Excel Worksheet Functions | |||
Formula for copying data for every 8th cell | Setting up and Configuration of Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |