I have a data that is 10000 records which is the Orders Data
Steps I perform now
1. Go to Orders Data :- Set some standard filters on the sheet. Copy the data along (with HEADERS) and paste in say Sheet1.
2. Go to Orders Data :- Set some standard filters again on the sheet. Copy the data (without the Headers) and paste it on Sheet2.
3. Add another Sheet3
4. copy the data from Sheet1 with Headers and Paste.
4. Copy the data from Sheet2 and append it below Sheet1 last row where it ended.
How can I automate this using VBA
I have managed it do a bit of it..Don't know where Im going wrong
On Error Resume Next 'proceed in case of error (if sheets doesn't excist) Application.DisplayAlerts = False 'give no alert Sheets("sheet1").Delete 'delete those sheets Sheets("sheet2").Delete Sheets("sheet3").Delete Application.DisplayAlerts = True On Error GoTo 0
With Sheets("Orders") '<---------give here the name of the sheet, i think it's Orders .Range("A1").AutoFilter With .Range("$A$1:$U$9995") .AutoFilter Field:=8, Criteria1:="Home Office" '1st filter .SpecialCells(xlVisible).Copy 'the visible cells only
Sheets.Add After:=ActiveSheet 'copy a first time to sheet1 With ActiveSheet .Paste .Name = "SHEET1" End With
Sheets.Add After:=ActiveSheet 'copy a 2nd time to sheet3 With ActiveSheet .Paste .Name = "SHEET3" End With
Sheets.Add After:=ActiveSheet 'copy a 1st time to sheet2 With ActiveSheet .Paste .Name = "SHEET2" End With
.Offset(1).SpecialCells(xlVisible).Copy 'copy a 2nd time and append in sheet3 With Sheets("sheet3") 'to a 3rd sheet With .Range("A" & Rows.Count).End(xlUp).Offset(1) 'next free A-cell .Paste End With End With