Solved

Copy result of an Autofilter to another Sheet.

Posted on 2014-02-05
17
937 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
ID: 39836271
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
ID: 39836386
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
ID: 39836406
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
ID: 39836441
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
ID: 39836457
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
ID: 39836477
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
ID: 39836506
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
ID: 39836564
Thanks.. let me do some testing on this... -R-
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:RWayneH
ID: 39836594
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
ID: 39836599
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
ID: 39836904
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
ID: 39836936
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
ID: 39838449
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
ID: 39840135
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
ID: 39844077
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
ID: 39854473
EXCELlent!!!  testing worked out great... Thanks for the help. -R-
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39854483
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Searching good PDF to OCR 7 37
Update As Well As Add 6 39
Incorporate VBA Code to work with Original Workbook 23 35
Excel sheet question 12 28
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 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

18 Experts available now in Live!

Get 1:1 Help Now