Solved

Copy result of an Autofilter to another Sheet.

Posted on 2014-02-05
17
923 Views
Last Modified: 2014-02-12
Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:="#N/A"

How do I copy the results of this autofilter to Sheet2?

When I go to select the first cell and ctrl+shift+down to select the range it can be different each time.   A22 one time  A10 another..  How do I tell it to just select the visible cells in column A, and copy them to another sheet?

If the filter does not produce any results? skip copying this to the other sheet.  (there are just headers in the filter, or do not count row 1.

I need to run another procedure on the results of this filter...  any ideas?  -R-
0
Comment
Question by:RWayneH
  • 9
  • 8
17 Comments
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
RWayneH,

You can use SpecialCells to only look at the visible cells like:
   Columns("A").SpecialCells(xlCellTypeVisible)

But that will be every cell that isn't hidden in A. If you want to only copy the used cells, use the Intersect method with the ActiveSheet.UsedRange:
   Intersect(ActiveSheet.UsedRange, Columns("A").SpecialCells(xlCellTypeVisible))

If there are no visible cells in that range it will throw up an error so you can set it to a range variable without error protection and check to see if it is nothing afterwards:
    Dim RG As Range
    On Error Resume Next
    Set RG = Intersect(ActiveSheet.UsedRange, Columns("A").SpecialCells(xlCellTypeVisible))
    On Error GoTo 0
    If Not RG Is Nothing Then
        RG.Copy Destination:=Range("Wherever you want your visible cells pasted")
    End If

Open in new window

0
 

Author Comment

by:RWayneH
Comment Utility
When you say "wherever you want your visible cells pasted"  can you give a sample location?  Like Sheet2 cell A2, so I can see how it is formatted? -R-
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Sure thing, I left it open because there are lots of ways to proceed from it. For your example, use:
RG.Copy Destination:=Worksheets("Sheet2").Range("A2")

Open in new window

I'd be happy to give you more sample code, just let me know what you're looking to do.
0
 

Author Comment

by:RWayneH
Comment Utility
RG.Copy Destination:=Range("NewMatlsNotInList").Cells(2, 1)

is failing?  can you give an example of a sample location to paste too?
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Sure.. is "NewMatlsNotInList" a named range, or a worksheet name? If a worksheet name, you can use the same notation as my last example
RG.Copy Destination:=Worksheets("NewMatlsNotInList").Range("A2")

Open in new window

or if you want to use .Cells
RG.Copy Destination:=Worksheets("NewMatlsNotInList").Cells(2, 1)

Open in new window

0
 

Author Comment

by:RWayneH
Comment Utility
Ok.. trying new target location ...    sorry I forgot that I already posted back on the failure.

After I run the procedure on the list, I then need to copy those rows to the bottom of a list on another sheet.  I guess this would need to be included in the on error resume...

The first location that I am copying them to is a holding place, while I run the other procedure.  I can not run this other procedure on a filtered list.  It is wanting to run on every cell instead of just the visible ones.   This other procedure is an On Selection type loop, so my approach was to copy them out, preform the procedure and then copying them again to the the target location.

Perhaps there is another way to approach this?  Have you processed a On Selection from a filtered list?

On Selection procedure is pulling value from SAP.  This is executed on the first list copied out.

For Each C In Selection

If C.Value = "" Then Exit For  'no End If with the Then statement

session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm03"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = C.Value
session.findById("wnd[0]/tbar[1]/btn[5]").press
If InStr(1, session.findById("wnd[0]/sbar").Text, "Entry is") Then GoTo 100
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02").Select

C.Offset(0, 2).Value = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2002/ctxtMARA-WRKST").Text

Next C

100

session.findById("wnd[0]/tbar[0]/btn[3]").press
session.findById("wnd[0]/tbar[0]/btn[3]").press
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Your selection, if you're not using the SpecialCells method to only select visible cells, contains every cell between so I can see why your For Each C In Selection works on all of them.

You can definitely use the For Each on the visible cells:
For Each C In RG.Cells

Open in new window

In place of your "goto 100" just put Exit For, it will do the same thing and gets rid of clunkly GoTo statements.

Putting it all together:
Dim RG As Range, C As Range
    
    Rows("1:1").AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:="#N/A"
 
    On Error Resume Next
    Set RG = Intersect(ActiveSheet.UsedRange, Columns("A").SpecialCells(xlCellTypeVisible))
    On Error GoTo 0
    If Not RG Is Nothing Then
        For Each C In RG.Cells
            If C.Value = "" Then Exit For  'no End If with the Then statement
            
            session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm03"
            session.findById("wnd[0]").sendVKey 0
            session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = C.Value
            session.findById("wnd[0]/tbar[1]/btn[5]").press
            If InStr(1, session.findById("wnd[0]/sbar").Text, "Entry is") Then Exit For
            session.findById("wnd[1]/tbar[0]/btn[0]").press
            session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02").Select
            
            C.Offset(0, 2).Value = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2002/ctxtMARA-WRKST").Text
        
        Next
        session.findById("wnd[0]/tbar[0]/btn[3]").press
        session.findById("wnd[0]/tbar[0]/btn[3]").press
    End If

Open in new window

0
 

Author Comment

by:RWayneH
Comment Utility
Thanks.. let me do some testing on this... -R-
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:RWayneH
Comment Utility
SAP is selecting the header of the list as its first to check... how do I tell it to disregard the headers?  I guess the header is a visible row..  forgot all about that.  -R-
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Easy enough. In your Intersect statement, add Rows("2:"&Rows.Count)

Set RG = Intersect(ActiveSheet.UsedRange, Columns("A").SpecialCells(xlCellTypeVisible), Rows("2:" & Rows.Count))

Similarly you could look in range("A2:A" & rows.count) instead of columns("A") for the same result.
0
 

Author Comment

by:RWayneH
Comment Utility
This testing worked awesome!!  If I could trouble you for the last part?  I need to copy the rows (if there was any) after below procedure runs, into another sheet that has a list in it.  I need to add whatever rows that are visible (less headers) to the bottom of the list that is into a sheet tab called: NewMatlsWithNeededValuesThatWereNot PartOfSAPExtracts.  I understand this was not part of the question, but it fits into this Sub.

The final code that is working.
Dim RG As Range, C As Range
    
    Rows("1:1").AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:="#N/A"
'Acts on only SpecialCells (visible) from autofilter, starting less header
    On Error Resume Next
    Set RG = Intersect(ActiveSheet.UsedRange, Columns("A").SpecialCells(xlCellTypeVisible), Rows("2:" & Rows.Count))
    On Error GoTo 0
    If Not RG Is Nothing Then
        For Each C In RG.Cells
            If C.Value = "" Then Exit For  'until is empty or use goto 100 so blanks are allowed.
            
            session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm03"
            session.findById("wnd[0]").sendVKey 0 'Enter
            session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = C.Value  'Matl number
            session.findById("wnd[0]/tbar[1]/btn[5]").press 'green check mark
            If InStr(1, session.findById("wnd[0]/sbar").Text, "Entry is") Then Exit For
            session.findById("wnd[1]/tbar[0]/btn[0]").press  'Enter
            session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02").Select 'BasicData2 tab
            'BasicData2 tab, = tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/
            'Section in the tab = subSUB1:SAPLMGD1:1002/
            'Field and table = txtMAKT-MAKTX").Text 'Matl Description or
            'Field and table = ctxtMARA-WRKST").Text 'Basic Matl
            C.Offset(0, 1).Value = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").Text 'Matl Description
            C.Offset(0, 2).Value = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2002/ctxtMARA-WRKST").Text 'Basic Matl
            
        Next
        
        session.findById("wnd[0]/tbar[0]/btn[3]").press
        session.findById("wnd[0]/tbar[0]/btn[3]").press
    End If

Open in new window

0
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
Comment Utility
I understand this was not part of the question, but it fits into this Sub.
I'm always happy to keep helping if I can, regardless of the scope of the original question. It makes it harder sometimes for future searchers to find what they want (or you, when you revisit this) but helps solve the "now" problem quicker.

Give the following a try:
    Dim RG As Range, C As Range, WS As Worksheet
    
    Rows("1:1").AutoFilter
    ActiveSheet.UsedRange.AutoFilter Field:=2, Criteria1:="#N/A"
'Acts on only SpecialCells (visible) from autofilter, starting less header
    On Error Resume Next
    Set RG = Intersect(ActiveSheet.UsedRange, Columns("A").SpecialCells(xlCellTypeVisible), Rows("2:" & Rows.Count))
    On Error GoTo 0
    If Not RG Is Nothing Then
        For Each C In RG.Cells
            If C.Value = "" Then Exit For  'until is empty or use goto 100 so blanks are allowed.
            
            session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm03"
            session.findById("wnd[0]").sendVKey 0 'Enter
            session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = C.Value  'Matl number
            session.findById("wnd[0]/tbar[1]/btn[5]").press 'green check mark
            If InStr(1, session.findById("wnd[0]/sbar").Text, "Entry is") Then Exit For
            session.findById("wnd[1]/tbar[0]/btn[0]").press  'Enter
            session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02").Select 'BasicData2 tab
            'BasicData2 tab, = tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/
            'Section in the tab = subSUB1:SAPLMGD1:1002/
            'Field and table = txtMAKT-MAKTX").Text 'Matl Description or
            'Field and table = ctxtMARA-WRKST").Text 'Basic Matl
            C.Offset(0, 1).Value = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").Text 'Matl Description
            C.Offset(0, 2).Value = session.findById("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB2:SAPLMGD1:2002/ctxtMARA-WRKST").Text 'Basic Matl
            
        Next
        
        session.findById("wnd[0]/tbar[0]/btn[3]").press
        session.findById("wnd[0]/tbar[0]/btn[3]").press
        
        Set WS = Worksheets("NewMatlsWithNeededValuesThatWereNot PartOfSAPExtracts")
        Set RG = Intersect(ActiveSheet.UsedRange, Rows("2:" & Rows.Count).SpecialCells(xlCellTypeVisible)).EntireRow
        RG.Copy Destination:=WS.Cells(WS.UsedRange.Row + WS.UsedRange.Rows.Count, 1)
        
    End If

Open in new window

I added the block before the final End If that sets the visible rows to the RG range variable, then copies that below the last used row in your worksheet. I added a worksheet variable ("WS") simply because your sheet had a long name and I wanted to make it look easier to read and follow.
0
 

Author Comment

by:RWayneH
Comment Utility
I did not get a chance to fully test this yesterday, but will today.  I had a question on line 34.  Can you explain this?  I see the part that is taking RG and copying it, but the destination part.  Is it assuming that the destination is a filtered area?  The destination is not filtered and it is just a list of rows and columns in a sheet.  If this searches the list and can determine the bottom and paste those rows in, then we are good.  However it looks as it is assuming an autofilter is applied with the use of UsedRange...  This is more for my understanding...  thanks. -R-
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
Absolutely, I'm always happy to help teach someone who wants to learn. Here's the line you're referring to (in case later the line numbers go away)
RG.Copy Destination:=WS.Cells(WS.UsedRange.Row + WS.UsedRange.Rows.Count, 1)

The destination part:
WS.Cells(WS.UsedRange.Row + WS.UsedRange.Rows.Count, 1)

Realistically, this is just using row/column notation, like:
WS.Cells(R, C)
if C=1 and R=WS.UsedRange.Row + WS.UsedRange.Rows.Count

UsedRange doesn't have to be used only with AutoFilter, it is just a range object that refers to only the actual used area within a worksheet.

"WS.UsedRange.Row" lists the first used row number on the worksheet.
"WS.UsedRange.Rows.Count" lists the number of used rows on the worksheet.

So if you had data in A3:D17,   WS.UsedRange.Row would be 3, and WS.UsedRange.Rows.Count would be 15
So if R=WS.UsedRange.Row + WS.UsedRange.Rows.Count then R=3+15=18

So WS.Cells(WS.UsedRange.Row + WS.UsedRange.Rows.Count, 1) means the same as WS.Cells(18, 1), or A18 (the first unused row in the sheet).

Make sense?

EDIT: bolded code parts, as code tag made it more difficult to read
0
 

Author Comment

by:RWayneH
Comment Utility
I actually followed that.. and yes makes sense.  The testing has gone well and I would like to do some more on Monday.  Thanks. for the explaination, it hopes to understand when I need to use it again.  Plan on closing question on Monday.  -R-
0
 

Author Closing Comment

by:RWayneH
Comment Utility
EXCELlent!!!  testing worked out great... Thanks for the help. -R-
0
 
LVL 35

Expert Comment

by:mvidas
Comment Utility
You can always feel free to post back here if you need anything in the future or have a quick question or something. You could email me too (address in profile) but I check every EE notification so either way works. Glad everything works well :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This collection of functions covers all the normal rounding methods of just about any numeric value.
No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now