Copy cells de[endin on 2 criteria

[tigersden]

Hi,
Need help pointing me in the right direction.
I need to copy certain columns from a sheet to a summary sheet depending on 2 criteria, month & order value. The month will be selected from a combo box & the order value from a group of option buttons.
So user selects August & orders greater than 1000. I need to find all the orders from 01/08 to 31/08 with their value >= 1000, copy 5 columns where the criteria is met in that row.
What is the best & easiest way of doing this??
I was thinking maybe of using an advanced filter then copy all the filtered results to a new sheet, but dont where to start.
Thanks in advnce
Tim

 

[HalfAce]

Hello Tim,
A few questions for you.
Are you looking for a way to do this: (manually? a VBA solution?)
Are these controls in a userform, or planted directly on the sheet?
Are the items in the combo box full dates or just the month names?
You said you want to copy 5 columns...
What column are the dates in?
What column are the order values in?
Which 5 columns do you want to copy?
Where is the destination for the copied values/rows?

 

[tigersden]

Hi HalfAce,
Answers to your questions are:
1) A VBA solution fired when command button clicked.
2) The controls are on a sheet(Summary)
3) The items in combo box are month numbers
4) The dates are in a sheet called ImportedData in a dynamic named range called OrderDate which refernces column Y.
5) The order values are in the same sheet called ImportedData in a dynamic named range called TotOrderValue which references column O.
6) The 5 columns to copy are on the ImportedData sheet they are
in dynamic named ranges,

• OrderDets = Column A *Unique Ref
  Dept = Column F
  Desc = Column G
  SuppName = Column N
  TotorderValue = Column O

7) The detination is a sheet called Report row A has headers so data starts at row 2 column A.

I hope this is enough info & thanks for helping.
Tim

 

[HalfAce]

OK, had to make a few guesses & assumptions in order to do any testing.
Here's what I used.
On Sheet "Summary":
(1) I used an ActiveX ComboBox.
(2) I planted 4 ActiveX OptionButtons. For their captions I used 1000, 1500, 2000 & 2500, respectively.

In a standard module, I put this.

Public GetMnth As Date
Public CritChosn

In the sheet code module for Summary sheet, I used:

Private Sub ComboBox1_Change()
GetMnth = ComboBox1.Text & "/01/" & Year(Date)
End Sub

Private Sub OptionButton1_Click()
CritChosn = OptionButton1.Caption
End Sub

Private Sub OptionButton2_Click()
CritChosn = OptionButton2.Caption
End Sub

Private Sub OptionButton3_Click()
CritChosn = OptionButton3.Caption
End Sub

Private Sub OptionButton4_Click()
CritChosn = OptionButton4.Caption
End Sub

Then I just assigned this code to a Forms button:

Sub FilterAndCopyDemo()
Sheets("Report").Cells.Clear
With Sheets("ImportedData")
    With .Columns("A:IV")
        If Month(GetMnth) = 12 Then
            .AutoFilter Field:=25, Criteria1:=">=" & Month(GetMnth) & "/1/" & Year(Date), _
                                   Criteria2:="<" & Month(GetMnth) & "/31/" & Year(Date)
            .AutoFilter Field:=15, Criteria1:=">" & CritChosn
        Else
            .AutoFilter Field:=25, Criteria1:=">=" & Month(GetMnth) & "/1/" & Year(Date), _
                                   Criteria2:="<" & Month(GetMnth) + 1 & "/1/" & Year(Date)
            .AutoFilter Field:=15, Criteria1:=">" & CritChosn
        End If
    End With
    .Columns("Y").SpecialCells(xlVisible).Copy _
        Sheets("Report").Cells(Rows.Count, 1).End(xlUp)(2, 1)
    .Columns("F").SpecialCells(xlVisible).Copy _
        Sheets("Report").Cells(Rows.Count, 2).End(xlUp)(2, 1)
    .Columns("G").SpecialCells(xlVisible).Copy _
        Sheets("Report").Cells(Rows.Count, 3).End(xlUp)(2, 1)
    .Columns("N").SpecialCells(xlVisible).Copy _
        Sheets("Report").Cells(Rows.Count, 4).End(xlUp)(2, 1)
    .Columns("O").SpecialCells(xlVisible).Copy _
        Sheets("Report").Cells(Rows.Count, 5).End(xlUp)(2, 1)
     .AutoFilterMode = False
End With
End Sub

Now, when I use the combobox & select a number, the public variable "GetMnth" gets assigned the value of the selection. This determines which month was selected.

When I choose one of the optionbuttons, the public variable "CritChosn" is defined as the caption of the optionbutton I selected.
These two variables are used as the filtering criteria.

When I click the Forms button, sheet "ImportedData" gets filtered on column "Y" by the month that "GetMnth" refers to.
It then gets filtered again on column "O" by greater than the value of "CritChosn"

Then it copies the filtered results of column(s):
Y to col. A of Report sheet,
F to col. B of Report sheet,
G to col. C of Report sheet,
N to col. D of Report sheet,
O to col. E of Report sheet.

Even if this doesn't exactly match your layout/setup, it should be close, and should at least be good for some ideas.

Hope it helps.

 

[tigersden]

Hi HalfAce,

Thanks for your help I will try this out. I managed to get something working but it is not as efficient as you code. I copied the entire range of dates for the month then deleted the columns that I did not need.

Thank you.
Tim