• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vba-filter data and paste in another sheet

Nitesh Kumar

New Member
Hi all,


i want to filter data and paste in another sheet, sheet is created date criteria wise.
please see attachment for example and also see coding where is incorrect.

Code:
Dim a As String
Dim wkb As Worksheet

a = Range("H3")
Range("E1").AutoFilter , Field:=5, Criteria1:=Format(a, "dd/mm/yyyy"), visibledropdown:=True
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).name = Format(a, "dd/mm/yyyy")
Range("A1:E1" & Sheet1.Cells(Rows.Count, "E").End(xlUp).Row).Copy Sheets(Sheets.Count).Range("A1:E1")

Worksheets("sheet1").AutoFilterMode = False
 

Attachments

  • Policies sheets.xls
    116.5 KB · Views: 0
Nitesh

In the data you sent H3 is empty and so the filter works fine, it displays nothing
 
Try this code
Code:
Dim a As String
Dim wkb As Worksheet

'Filter
a = Range("B3").Text
Range("A1").AutoFilter , Field:=2, Criteria1:=a, visibledropdown:=True

'Add new worksheet
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = a

'Copy data
Range("A1:E1" & Sheet1.Cells(Rows.Count, "E").End(xlUp).Row).Copy Sheets(Sheets.Count).Range("A1:E1")
Sheets(Sheets.Count).Columns("A:E").EntireColumn.AutoFit

'Cleanup
Worksheets("Master").AutoFilterMode = False

A few points

1. Your Master Woprksheet has a space at the end of the name "Master "
2. The dates are text, not real dates
3. The dates are in column B which is Criteria 2

See attached file:
 

Attachments

  • Copy of Policies sheets.xlsm
    43.8 KB · Views: 4
Thanks for help,

i need one more help, can u please assist me how to select date through calendar.

pls see the attachment
 

Attachments

  • Policies sheets1.xls
    181.5 KB · Views: 1
Back
Top