vba help - split data and save workbook

[Mallesh23]

Hi Team,

I want to split data into two set and save those two workbook into a thisworkbook.path.
File name - a) Data between 01/01/2020 - 30/05/2020
FileName - b) Data - 01/06/2020 onward...

Criteria to split data are Receipt date between
a) 01/01/2020 - 30/05/2020
b) 01/06/2020 - onwards.

how to achieve this task.


Below is a data

Book29
	A	B	C	D
1	Receipt Date	xxx	xxx	xxx
2	01/01/2020	xxx	xxx	xxx
3	01/02/2020	xxx	xxx	xxx
4	10/03/2020	xxx	xxx	xxx
5	15/04/2020	xxx	xxx	xxx
6	06/07/2020	xxx	xxx	xxx
7	20/08/2020	xxx	xxx	xxx
8	06/09/2020	xxx	xxx	xxx
9	16/07/2020	xxx	xxx	xxx
10	02/07/2020	xxx	xxx	xxx
11	09/09/2020	xxx	xxx	xxx
Sheet1

Thanks
mg

 

[Mallesh23]

Hi Team,

Below is my attempted code, however filtering is not working.

But task is filter data between two dates,...... if there is a data found save in New workbook.
Needs to generate two output files from Single input files.

Sub Split_Data()

Dim myFileName As String

Dim wbk As Workbook
Set wbk = Workbooks.Open("D:\Testing sample data\Data.xlsx")

Condition1
Range("a1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
        ">=01/01/2020", Operator:=xlAnd, Criteria2:="<=30/05/2020"


'If greater than 1
Range("a1").CurrentRegion.SpecialCells(12).Copy
Workbooks.Add
Range("a1").PasteSpecial xlPasteAll
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Data_Jan_May.xlsx"

'Condition -2
Range("a1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
        ">=01/06/2020"

'if greater than 1
Range("a1").CurrentRegion.SpecialCells(12).Copy
Workbooks.Add
Range("a1").PasteSpecial xlPasteAll
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\June Onward.xlsx"



End Sub

Thanks
mg
mg