Copy & Paste with set conditions and count of rows

**dnyhof** · 03-02-2011, 09:01 PM

Hi all,

I have found a few threads which show how to cut/copy & paste with set conditions or filters - this i can manage.
I am having a real hair puller of a prob with (See attached example attached)
Sheet 'ALLDATA' I want to copy last 200 rows of data meeting criteria;
- Col DIA = 12
- Col EG = EG2
Then paste into sheet 'EG2'.

The prob is only selecting the last 200 rows (or up to 200 max in some cases there is less) results.

Thanks in advance for any help

**MarvinP** · 03-02-2011, 09:39 PM

Hi dnyhof and welcome to the forum,

Find the attached with an advanced filter. I put a column called Row Number next to your data and performed two Advanced filters. One for EG1 and one for EG2 both with row number greater than 200.

It is a little ambiguous if the rows to chose from are greater than 200 or you want a maximum of 200 answer rows.

See what I did. Hope this helps.

**dnyhof** · 03-03-2011, 12:07 AM

Thanks for reply MarvinP, but not exactly what i was after.
I needed the last 200 results meeting the particular criteria's.

I should have been a bit clearer in original question, i was after a code to do this.

I have managed to achieve what i am after using macro recorder while filtering & sorting manually, its a little crude but the result is ok. I have pasted it for anyone to fine tune if interested.

Sub testmacro()
'
' testmacro Macro
' Macro recorded 3/03/2011 by XXXXX
'

' Filtering criteria
    Sheets("ALLDATA").Select
    Selection.AutoFilter Field:=6, Criteria1:="EG2"
    Selection.AutoFilter Field:=3, Criteria1:="12"

' Data sorting and copying
    Columns("A:O").Select
    Selection.SORT Key1:=Range("D2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("C39:O490").Select
    Selection.Copy
    
' Paste to relevant sheet
    Sheets("EG2").Select
    Range("P19").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.SORT Key1:=Range("Q19"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
' Reset data sheet to original state
    Sheets("ALLDATA").Select
    Range("P1").Select
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=6
    Columns("A:O").Select
    Selection.SORT Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("P1").Select
End Sub

**dnyhof** · 03-03-2011, 12:14 AM

ARGGHH!
No good.. as the code - Range("C39:O490").Select - is not selecting the last 200 results meeting criteria but just my filtered results. As i add more data the range will change when filtered so looks like i am back to the drawing board.

**MarvinP** · 03-03-2011, 12:54 AM

I didn't see a date field to know what you meant by Last. Is your data in some kind of row order?

So you want the rows that meet the criteria but the bottom 200 that do it? If there is no sort then the Bottom or Last 200 will be on the Bottom of the list..??

If you use the Advanced Filter and then find the bottom row of the filtered data it would be a snap to get rid of all the top rows using VBA.

**dnyhof** · 04-12-2011, 12:39 AM

There are thousands of entries (Lines) of data, the 'last 200' is when filtered with required criteria the bottom 200 lines. The prob with advanced filter is I have diff criteria for each copy & paste to each sheet. I have succeeded in my quest but when running my report which runs 50 seperate macros with diff criteria, so constant resorting, filtering, copying & pasting it takes around 4.5mins to complete. Still better than 2hrs manually.

FYI Code i used:

Sub SU10_250()
'
' Macro by 
'
Sheets("ALLDATA").Select
    Selection.AutoFilter Field:=4, Criteria1:="SU"
    Selection.AutoFilter Field:=1, Criteria1:="10"
    Range("A1:M20000").Select
    Selection.Copy

Sheets("SU 10_250").Select
    Range("P230").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
           
Dim Cell As Range
Dim Rng As Range
Dim RngEnd As Range
  
    Set Rng = Range("W230:W10000")
    Set RngEnd = Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd))
    
      For Each Cell In Rng
        If Left(Cell, 1) > "400" Then Cell = ""
      Next Cell
      
      On Error Resume Next
      Rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete


Range("P230:AB10000").Select
    Selection.SORT Key1:=Range("Q231"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("P230:AB430").Select
    Selection.Copy
    Range("P19").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.SORT Key1:=Range("Q19"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    
    Range("P230:AB10000").Select
    Selection.ClearContents
    Range("G4").Select
    
    Sheets("ALLDATA").Select
    Selection.AutoFilter Field:=4
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.AutoFilter Field:=1
    Range("A1").Select
End Sub

**MarvinP** · 04-12-2011, 11:29 AM

Hi,

This problem seems to have changed since I last saw it. I thought you wanted to return all rows bigger than the 200th row of the original data. Now it looks like you want to return the last 200 rows of a filtered set.

Here is my plan.
1, Do an Advanced Filter to the right of your data on the AllData sheet.
2. Put your Advanced Filtered data starting in Col O.
3. Find the last row of filtered data using LastRow of column O
4. Use this LastRow - 200 to see where to start your Copy from
5. Paste this whole thing to wherever you want.

I've made two short subroutines in the attached to help you get started. Your macro above uses different data than was in the original post so I'm using what your first had in the attached.