-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Yongle
Well-known Member
- Joined
- Mar 11, 2015
- Messages
- 6,977
- Office Version
- 365
- Platform
- Windows
Familiarise yourself with Advanced Filter - I think it can do everything you need
https://www.contextures.com/xladvfilter01.html
https://www.contextures.com/xladvfilter01.html
Upvote
0
chadlaw32
Board Regular
- Joined
- Jul 29, 2014
- Messages
- 84
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
That is not doing what i need are there any other ideas for how I can get this done. To give more detail I am making a sheet so that people can put in a date or a date range and the sheet will pull call stats for that time period.
Upvote
0
Yongle
Well-known Member
- Joined
- Mar 11, 2015
- Messages
- 6,977
- Office Version
- 365
- Platform
- Windows
This works and gives you somewhere to start
- modify to suit your needs
This was the original sheet
- From date is selected in cell X1
- To date is selected in cell Z1
Excel 2016 (Windows) 32 bit
This is what was created by the macro
Excel 2016 (Windows) 32 bit
Run from the sheet containing data - data assumed to start in A1
- modify to suit your needs
This was the original sheet
- From date is selected in cell X1
- To date is selected in cell Z1
Excel 2016 (Windows) 32 bit
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DATE | NAME | C | d | e | f | g | h | i | j | k | l | m | From | 15/03/2019 | To | 15/03/2019 | |||||||||
2 | 22/02/2019 | name01 | c01 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
3 | 28/02/2019 | name02 | c02 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
4 | 13/03/2019 | name03 | c03 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
5 | 05/03/2019 | name04 | c04 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
6 | 21/02/2019 | name05 | c05 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
7 | 17/03/2019 | name06 | c06 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
8 | 15/03/2019 | name07 | c07 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
9 | 10/03/2019 | name08 | c08 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
10 | 14/02/2019 | name09 | c09 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
11 | 11/03/2019 | name10 | c10 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
12 | 15/02/2019 | name11 | c11 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
13 | 22/02/2019 | name12 | c12 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
14 | 03/03/2019 | name13 | c13 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
15 | 07/03/2019 | name14 | c14 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
16 | 05/03/2019 | name15 | c15 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
17 | 10/02/2019 | name16 | c16 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
18 | 15/03/2019 | name17 | c17 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | |||||||||||||
19 | 15/03/2019 | name18 | c18 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Sheet: 11 |
This is what was created by the macro
Excel 2016 (Windows) 32 bit
A | B | C | D | E | F | G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DATE | NAME | C | d | e | f | g | h | i | j | k | l | m |
2 | 15/03/2019 | name07 | c07 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
3 | 15/03/2019 | name17 | c17 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
4 | 15/03/2019 | name18 | c18 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
5 | 15/03/2019 | name88 | c88 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
6 | 15/03/2019 | name129 | c129 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
7 | 15/03/2019 | name137 | c137 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
8 | 15/03/2019 | name138 | c138 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
9 | 15/03/2019 | name149 | c149 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
10 | 15/03/2019 | name166 | c166 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Sheet: Sheet23 |
Run from the sheet containing data - data assumed to start in A1
Code:
Sub FilterByDate()
Dim ws As Worksheet
With ActiveSheet
Dim c1 As Long: c1 = .Range("X1").Value 'from date
Dim c2 As Long: c2 = .Range("Z1").Value 'to date
On Error Resume Next: .ShowAllData: On Error GoTo 0
'filter the data
.Range("A1").AutoFilter
.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=">=" & c1, Operator:=xlAnd, Criteria2:="<=" & c2
.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
'paste data to new sheet
Set ws = Sheets.Add
ws.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Paste
End With
End Sub
Upvote
0
