Split sheets by value

[Claire9079]

Hi

thanks for your time in advance.

I know in excel i can split worksheet into multiple worksheets based on column value.

However my report size is quite big and i only need one criteria, can i split into sheets based on values?

For example, i would need a macro to split the below data just based on Japan.


[TABLE="width: 364"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]Date [/TD]
[TD="align: left"]Name[/TD]
[TD="align: left"]start date[/TD]
[TD="align: left"]end date[/TD]
[TD="align: left"]Location[/TD]
[/TR]
[TR]
[TD="align: right"]03/06/2019[/TD]
[TD="align: left"] CCC CC[/TD]
[TD="align: right"]03/06/2019[/TD]
[TD="align: right"]03/06/2019[/TD]
[TD="align: left"]Japan[/TD]
[/TR]
[TR]
[TD="align: right"]03/06/2019 [/TD]
[TD="align: left"]CCC CC[/TD]
[TD="align: right"]03/06/2019[/TD]
[TD="align: right"]03/06/2019[/TD]
[TD="align: left"]US[/TD]
[/TR]
[TR]
[TD="align: right"]03/06/2019 [/TD]
[TD="align: left"]CCC CC[/TD]
[TD="align: right"]03/06/2019[/TD]
[TD="align: right"]03/06/2019[/TD]
[TD="align: left"]UK[/TD]
[/TR]
</tbody>[/TABLE]


thanks
Claire

 

[DanteAmor]

Try this

Sub Split_sheets_by_value()
    Dim sh As Worksheet
    Set sh = ActiveSheet
    sh.Range("A1").AutoFilter 5, "Japan"
    Sheets.Add
    sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    sh.ShowAllData
End Sub

 

[Claire9079]

It works!! Thanks so much DanteAmor

Try this

Sub Split_sheets_by_value()
    Dim sh As Worksheet
    Set sh = ActiveSheet
    sh.Range("A1").AutoFilter 5, "Japan"
    Sheets.Add
    sh.AutoFilter.Range.EntireRow.Copy Range("A1")
    sh.ShowAllData
End Sub

 

[DanteAmor]

Youre welcome and thanks for the feedback