vba help - split data and save workbook

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
812
Office Version
  1. 2010
Platform
  1. Windows
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
ABCD
1Receipt Datexxxxxxxxx
201/01/2020xxxxxxxxx
301/02/2020xxxxxxxxx
410/03/2020xxxxxxxxx
515/04/2020xxxxxxxxx
606/07/2020xxxxxxxxx
720/08/2020xxxxxxxxx
806/09/2020xxxxxxxxx
916/07/2020xxxxxxxxx
1002/07/2020xxxxxxxxx
1109/09/2020xxxxxxxxx
Sheet1


Thanks
mg
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
812
Office Version
  1. 2010
Platform
  1. Windows
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.



VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,206
Messages
5,570,899
Members
412,346
Latest member
JGordon11
Top