Filter Based On 2 Inputs Or Option And Copy The Date To Next Sheet One After The Other
Sep 25, 2008
I have some data in sheet1 with 10 columns and 5000 rows.
I want to filter the data with 2 criterios.
When I go to 4th column and Click custom filter, I will give one criteria and select "or" and give another criteria. SO I will get the result in sheet1, I need to copy the data and paste the same in sheet 2 with the header.
The problem is, I need to filter more than 20 times giving the criteria and copy the result and paste in sheet 2 one after the other.
So i need a macro to solve this time consuming work.
I just paste the two criteria either in a text box or some cells and run the macro. the macro has to filter the data in sheet1 based on my input.( that is criteria1 or criteria 2) and the result should be pasted in sheet2 with the headers.
Again I delete the values in my input cell, and paste the new values, and run the macro, that result should be pasted after the first result, with the header. (would be great if that is pasted leaving one row above, that is if the first result is pasted in sheet 2 till 10th Row, then the send result should be pasted in 12th row and so on..
the similar kind of question with some changes, I posted in the below link with
http://www.excelforum.com/excel-prog...in-sheet2.html
View 9 Replies
May 8, 2009
This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:
With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.
I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.
View 7 Replies
View Related
Aug 13, 2012
Using macro's on Excel 2010. What I'm trying to do is create a macro that will copy a range of cells from one sheet to another depending on a option being selected from a drop down box. I've tried to use formulas but without success.
sheet 1 contains a list of approx 20 people with rows containing sales figures per week.
Is it possible to have a macro that will copy the rows to sheet2 depending on the dropdown? The drop down has already been setup with people's names
E.g.
if sheet 1, cell a1 (with data validation setup) dropdown contains "mr smith", copy sheet2 row A1:A9 to sheet 1 cell a2
or
if sheet 1, cell a1 dropdown contains "mr cooper", copy sheet2 row B1:B9 to sheet1 cell a2
etc... for each name in the dropdown
The idea is so that i select a dropdown and it copies the sales figures that match the dropdown name, if i then select another dropdown, the corresponding figures are copied to the same place.
View 3 Replies
View Related
Jul 27, 2006
Is there a way to easily filter the data according to the products' "index %" of a given month and then copying all data (including name, price, index%, and index value for the whole year) of those deviating from 100,0% to a new sheet?
I tried this myself with auto filters but the layout presented some problems. The sheet is created automatically by another programme, so the layout is what it is.
I really hope someone might have a solution for this since I need to sort through these sheets, with thousands of products, every month by hand!
View 9 Replies
View Related
Jan 25, 2008
i am trying to autofilter sheet1 based on the values from sheet2. i have coded and able to read the value from the other sheet but it reads value only for one row but not all rows... i guess i need to set the loop and i have no idea hw to set the loop. here is my code:
Dim datash As Worksheet
Dim CritSh As Worksheet
With ActiveWorkbook
Set datash = .Sheets("version") ' sheet to be filtered
Set CritSh = .Sheets("table") ' sheet from which the value is taken
End With
'DataSh.ShowAllData
datash. Cells.AutoFilter field:=1, Criteria1:="=" & CritSh.Range("A2").Value
datash.Cells.AutoFilter field:=2, Criteria1:="=" & CritSh.Range("B2").Value
datash.Cells.AutoFilter field:=3, Criteria1:="=" & CritSh.Range("C2").Value
..........................
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
Jun 7, 2008
I want to filter the data: [the data I have In "Sheet1]"
Sub Makro5()
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="5"
Selection.AutoFilter Field:=2, Criteria1:="6"
Selection.AutoFilter Field:=3, Criteria1:="7"
Selection.AutoFilter Field:=4, Criteria1:="99"
End Sub
and now I want to copy from "Sheet1" to "Sheet2" but only Field:=4, where criteria1:="99"; In "Sheet2" I want to paste my filtered data to:
Range("B1,D1,F1,H1,J1,L1,N1,P1,R1,T1,V1,X1,Z1," & _
"AB1,AD1,AF1,AH1,AJ1,AL1,AN1,AP1,AR1,AT1,AV1,AX1,AZ1," & _
"BB1,BD1,BF1,BH1,BJ1,BL1,BN1,BP1,BR1,BT1,BV1,BX1,BZ1," & _
"CB1,CD1,CF1,CH1,CJ1,CL1,CN1,CP1,CR1,CT1,CV1,CX1,CZ1," & _
"DB1,DD1,DF1,DH1,DJ1,DL1,DN1,DP1,DR1,DT1,DV1,DX1,DZ1," & _
"EB1,ED1,EF1,EH1,EJ1,EL1,EN1,EP1,ER1,ET1,EV1,EX1,EZ1," & _
"FB1,FD1,FF1,FH1,FJ1,FL1,FN1,FP1,FR1,FT1,FV1,FX1,FZ1," & _
"GB1,GD1,GF1,GH1,GJ1,GL1,GN1,GP1,GR1,GT1,GV1,GX1,GZ1," & _
"HB1,HD1,HF1,HH1,HJ1,HL1,HN1,HP1,HR1,HT1,HV1,HX1,HZ1," & _
"IB1,ID1,IF1,IH1,IJ1,IL1,IN1,IP1,IR1,IT1,IV1")
View 2 Replies
View Related
Jan 27, 2014
Trying to look up values based on three inputs:
Attached is the file:
Month Input, Column Input and Network Input are changeable based on user input, I am trying to get the desired output based on selection from A20, B20, C20 into cells G20, G21, G22 from row number 2 and corresponding values in H20, H21, H22 from the corresponding row based on the selection.
I have populated an example in the attached file.
View 14 Replies
View Related
Feb 27, 2014
I have 3 tables as per the attached samples (there is more but this is a sample for now).
In the table below these I have what I am trying to do. There is the manual input and the auto output based on the inputs.
e.g. if I input 12 in size, ball in type & A01B in piping class I hope to get the following output 20, 35, 51, 67, 83, 99 in the respective columns.
The formula needs to work across all three tables at the same time.
View 13 Replies
View Related
