Filter Table And Then Copy The Results Onto Another Worksheet
Jan 17, 2007
now i have filtered data in a table, i want to use the results by printing off a table showing just these and the appropriate rows from other tables on worksheets. these all have a specific ID which is how they are traceable to each other. like a related field in an access database. two tables/worksheets are like so:
table1:
Reg No | Rank | Name | Initials | Troop | etc
table 2:
Reg No | JCLM1 | JCLM2 | SCLM1 | SCLM2 | etc
If i were to filter the second table by JCLM1 = YES then i want to create a printable list of all those but also to include the related records from table 1. is this possible?
View 2 Replies
ADVERTISEMENT
Apr 5, 2013
I got the following code from Use AutoFilter to filter and copy the results to a existing worksheet and would like to incorporate this into my VBA project. The problem however is that this code were written to perform on one workbook and this is where my problem is. My project is between two different workbooks and cannot seem to get this code modified to do what it is supposed to do between these two workbooks. Everything I have tried so far failed. In short what this code would do is to check the existing data on the one sheet (the source) and extract only the data which is meeting my set criteria, and copy this data to the destination sheet. This is what I would like to do between two workbooks. With this the sample code as provided by Ron de Bruin. The sample workbook could be accessed trough the following link [URL]..... With this the code for matching and copying on one workbook.
Code:
Option Explicit
'>>
'This example will copy the filter results below the existing data on the destination sheet.
'Note the sheet "RecordsOfTheNetherlands" must exist in your workbook.
'This example will not copy the header row each time so when you manual add the worksheet
'"RecordsOfTheNetherlands" to your workbook you must add the headers yourself on the first row.
[Code] ............
View 1 Replies
View Related
Jul 24, 2008
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies
View Related
Aug 29, 2007
I recorded a macro to perform an Advanced Filter. I then adapted the range to & LastRow. My question is, can this now be adapted to remove the Select so the sheets are not selected when this is run.
Sheets("CIT Results").Select
Sheets("Open Calls").Range("A1:I" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Open Calls").Range("N5:V8"), CopyToRange:=Range("Q50"), Unique:=False
Moderators, can you please edit the Thread title. It should be "Advance Filter From and To Non Active Sheet"
View 6 Replies
View Related
May 20, 2014
I have a worksheet that contains 3 columns, A, B, C, that I need to run through auto-filter and copy the results from a cell, F2, into another sheet each time the filter criteria changes.
Although the worksheet will contain over 11,000 rows (the attached sample file is trimmed down to around 1000 rows),
Col A will only have 8 different possible criteria for autofilter: 1,2,3,4,5,6,9,10
Col B has around 70 criteria, and Col C has around 700 criteria.
The number of rows in the sheet and consequently the auto-filter criteria will likely change each time (but will usually hover around these quantities).
As an example, here is how I would envision this working for Col C:
1. Starting on the 1st Sheet (named "FW15"), I auto-filter Col C on criteria/value 1
2. I copy the resulting value from Cell F2 of sheet FW15 and paste it into the first empty cell of Col C in Sheet 2 (named "CopiedResults")
3. I return to my first sheet, FW15, turn off the enabled filter for criteria/value, and turn on the next autofilter Criteria/Value of 2
4. Repeat Step 2
.
.
.
Keep looping through Col C to make sure that all auto-filter values have been applied, and all resulting values contained in Cell F2 are copied over to the second sheet.
Likewise, I would need to run through the auto-filter criteria in Col A and Col B, and copy their resulting values (from cell F2) into Sheet2 Col A and Col B.
Attached workbook : autofiltercriteria3.xlsx
View 2 Replies
View Related
Jan 29, 2008
The error above comes up every time I copy filtered data to a new worksheet. It does its work but the said error comes up.
Sub AUTOFILTER_withouthead()
' AUTOFILTER_for_drop Macro
' Macro recorded 1/27/2008 by DD
Dim ws As Worksheet, wd As Variant
Set ws = Worksheets((Worksheets("Destination"). Cells(1, 6).Value))
Set wd = Worksheets("Destination").Range("A1:F65000") ...
View 3 Replies
View Related
Jun 7, 2013
I need the macro to filter a table using the name of active worksheet as criteria. The code that I am writing is as below, but it doesn't seem work:
ActiveSheet.Range("$A$1:$AE$421").AutoFilter Field:=19, Criteria1:="Activesheet.Name"
If I hard quote the name of the sheet then, the macro, unchecks all the criteria in the filter and does not show and row in the table.
View 1 Replies
View Related
Sep 24, 2009
I use the Macro below to search in a datafile and copy the results into another worksheet. Only problem, when the macro finds a result he should copy and start over again, starting from the row below, but somehow he start 2 rows lower, so I'm missing results. (when he should copy row 2,3,4,5,6 - the result is only 2,4,6) I tried changing
WerkRij = WerkRij + 1 into Werkrij = WerkRij
StartRij = Rij + 1 into StartRij = StartRij
but then he keep searching and copying the same cells
View 2 Replies
View Related
Jan 15, 2013
How to solve table with filter which needs to be copy to other tab.
Example: One tab has table with filter. Issue is with another tab which needs to be copy but issue is that another tab will further investigate with additional columns. Can be solved this issue as first tab will filter and change all rows order and another tab should read content with added columns.
View 4 Replies
View Related
Nov 30, 2009
The data to be filtered is in several sheets, and once filtered is to be copied to a destination sheet (in this case "Temp"). The criteria for advanced filter is on an altogether different sheet (in this case "Reports"). The macro is actually simplified for the purpose of the question, and I want to re-use the code several times, hence the use of variable "filterRng". When I run it, I get the subject error at the bolded line in the code below. I'm thinking that the Advanced Filter doesn't like a variable as a range reference, as it runs perfectly well if the commented out line below the problem line is used instead.
Sub Test()
Dim i As Integer
Dim rngData As Range
Dim filterRng As Range
Set filterRng = Sheets("Reports").Range("A121:K124")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
View 4 Replies
View Related
Mar 14, 2008
I have a pivot table that I need to copy only certain regions from the Report Filter on a frequent basis. For example, in the Report Filter, I have 18 total regions, but I only need to submit a pivot table for only 6 regions on a weekly basis. Currently, I'm filtering the 6 regions individually from the "data dump" and making 6 pivot tables as separate workbooks...and then emailing them out. I need to make the underlying detail available for each location that is getting their "report". I have the code for emailing a single worksheet from a workbook so, at least, I have that little tidbit taken care of.
View 2 Replies
View Related
Apr 6, 2008
i have three sheets in this workbook. i want to filter the the pupils with less than 20 marks in total at the end and copy it to the page named filter, from which i will go to the sheet1 which gives a remedial plan for ONLY those pupils who come in the filter page. this is all good but the problem is that when i update the Cont page, it wont update in the filter page.
View 6 Replies
View Related
Jul 29, 2009
I am building a workbook. The data we are tracking is all in one xml file, which i have mapped to 3 different worksheets(customers, invoices, inventory). On the customers and inventory sheet I have an interface for creating a new customer/product/invoice. I used the macro recorder to make the macro's to do these three things, but could use help on a couple of other functions as I don't actually know VBA. I need to be able to automate editing of existing records by having a button to press on the each sheet that will open an input box that asks the user which invoice number, customer number or product number he would like to edit, then copy that record to the interface for editing, then another macro to replace the existing data with the newly edited data. It would also be wonderful if when creating or editing an invoice it could create a copy of the interface worksheet and rename it as the invoice number. The first row of each datasheet is blank, and each of the interfaces have formula's for importing the information copied to row 1 from the data tables. the second row of each datasheet contains formulas for importing data from the interface. Any help would be terrific...I know you guys are excel gods and I will forever be in your debt if you can help me out....thanks in advance, I'll be studying my butt off until I figure this out
View 11 Replies
View Related
Mar 14, 2012
I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem
[Code]....
View 4 Replies
View Related
Feb 11, 2008
I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
View 3 Replies
View Related
Jun 2, 2008
can excel do this (see the attachment pls). if possible can someone show me how to do that. i am new in excel vba.
View 9 Replies
View Related
Jun 22, 2006
I would like to be able to select several non-sequential rows in a worksheet called "Data" (using a check box or just entering a value in Column A) and then be able to press a Command button to copy the selected rows to another worksheet called "Estimate" at the bottom of a table, and delete the designators in Column A (i.e. deletes the value, or unchecks the boxes) so I can repeat the process again if needed.
View 7 Replies
View Related
Apr 16, 2014
I would like to change the greater than number to the value in cell 'I11' in sheet 'Linear Programming Data' and the less than number to the value in cell 'I12' in sheet 'Linear Programming Data'. The code I am using was done by recording a macro since I don't know how to code in vba.
Code:
Sub Results2()
' Results2 Macro
' copy table filter power by greater than and less than
Sheets("Finalizing Results 2").Select
Cells.Select
[Code] .......
View 2 Replies
View Related
Dec 18, 2008
Having performed a subtotals function on a large worksheet, I want to simply copy the results of the "totals" rows to another worksheet but without all the hidden rows which are used for the calculation of the totals.
View 3 Replies
View Related
Dec 27, 2012
I need to copy certain values of multiple pivot tables of different worksheets into a new worksheet.
To be more clear I attached an example worksheet:
In "Sheet1_pivot table" and "Sheet2_pivot table" I have two pivot table with data divided by Area, Year, quarter and month.
In "Table" i have a summary table where, depending on the values of cell B2, B3 and B4, the summary table has different values copied from the 2 pivot tables.
I was thinking of a code like this but i have problems getting the values from the pivot tables
VB:
Sub else_if()
If Range("B2") = "North" And Range("B3") = "October" Then
ElseIf Range("B2") = "South" And Range("B3") = "October" Then
Else
.
.
.
.
Else
MsgBox ("Area not present")
End If
End Sub
View 2 Replies
View Related
Feb 19, 2009
I have a very large spreadsheet (almost 9000 rows). I have filters on all columns, however when I click to see all the results for the column under the filter, just to view the contents of the row, some are not there. Does a filter have a limitted amount of rows it can hold.
View 2 Replies
View Related
Jan 17, 2013
I have spreadsheet that tracks flights between cities. The spreadsheet has become quite large, so I need a few simple tools or tricks to search it efficiently.
To keep things simple: the spreadsheet has "Departs" and "Arrives" columns, tracking the cities on either end of a flight. (See below)
FlightDepartsTimeArrivesTime
1001New York2:00Boston3:00
1002New York2:30Philadelphia3:15
1003Boston2:45Philadelphia4:00
1004Washington DC3:00New York4:00
1005Philadelphia3:30Boston5:00
1006Boston4:00Washington DC5:30
If my boss says, "give me a list of all flights going through New York," I have to manually filter for "New York" in the "Departs" column, copy that to another spreadsheet, then filter again for "New York" in the "Arrives" column, and manually glue the results together. In my little toy example, that's not a big deal, but when there are up to a couple hundred flights (and just as many cities), this gets tedious and error-prone.
Is there some handy way of filtering for "New York" in both the "Departs" and "Arrives" columns? Or some other way of achieving the same thing? ("Find All" isn't useful unless I can export the full rows into a spreadsheet.)
View 6 Replies
View Related
Dec 4, 2013
I have a UserForm with a TextBox and a ListBox
I'd like when I type a last name (for example) and press a button, it filter the results and these results should appear in the Listbox
I have a code that does the same but with numbers (ID, Tlfno number, or other)
View 1 Replies
View Related
Jun 21, 2006
I have a basic spreadsheet with a column of names of people (Matt, John, Mike), and specific sales numbers for each. I would like to have a cell give an average of the sales numbers based on how I filter for either Matt, John, or Mike individually.
For example: If I filter to see only Matt, I would like to see the average for Matt only, and when I filter to another name such as John, I would like to see the average number change to see only John's.
View 7 Replies
View Related
Oct 23, 2007
I've tried my best to describe my dilema. I import data in an SQL query from a mainframe. This data shows a job number, site address, job number and a length. Multiple lengths on the same site appear as separate entries. I use a pivot table to calculate a total length and also formulas to determine whether a site is in an East or West area. I then need to then be able to copy the 'East' jobs to an 'East' worksheet and the 'West' jobs to the 'West' worksheet. But, I only want a site to appear once with the total length.
View 2 Replies
View Related
Jun 27, 2014
I have a workbook with multiple sheets , final sheet has a data of all sheets , in the first sheet i want to select what results to be shown in that final sheet ,, specially the week and the LDM
View 12 Replies
View Related
Feb 9, 2014
I want to filter my results using a combo box on my spreadsheet. I don't mind how this is done, even if it just hides all of the information which isn't relevant. I've done it with a VLookup but I want to be able to edit it easily without the formula popping up. So if you click Barlcays, only barclays appears. I'd also like a button to reset if possible. I've attached an example of how I sort of want it to work.
View 3 Replies
View Related
Feb 25, 2009
how to return data to a second sheet if data selected via a filter.
i have attached spreadsheet i am working on and looking to make this as easy as possible
on sheet "data" cell f2 im looking to return all goals scored by hull
on sheet "data" cell f3 im looking to return all goals conceded by hull
on sheet "data" cell f5 im looking to return all goals scored by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f6 im looking to return all goals conceded by hull at home this i will need to do by selecting hull in "home team" filter hull
on sheet "data" cell f8 im looking to return hulls last 6 games as over and under which in this example will return the last 6 rows 27-32 and will read 4 under 2 over
on sheet "data" cell f9 im looking to return hulls last 6 games at home as over and under which in this example will return the last 6 rows 22/23/26/27/29/32 and will read 3 under 3 over
View 10 Replies
View Related
May 14, 2012
I want to create a searchbox in Excel which will locate text in a massive amount of data, for example, if a user types into the box....
"123"
I want the search box to filter the spreadsheet using the autofilter from cell B3, thus filtering out all results that are NOT "123".
Currently I have a button to press which brings up the CTRL + F screen, but that isn't exactly what is required in this instance.
View 6 Replies
View Related
Aug 4, 2006
how to make a formula to only display the results shown when using the filter..........
View 2 Replies
View Related
