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.

Date	Name	start date	end date	Location
03/06/2019	CCC CC	03/06/2019	03/06/2019	Japan
03/06/2019	CCC CC	03/06/2019	03/06/2019	US
03/06/2019	CCC CC	03/06/2019	03/06/2019	UK

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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