VBA - Filter, Copy and Paste, Rename Worksheet to Specified Name - 7 Worksheets

[Mikeos145]

Hi,

I'm fairly new to VBA and up until this point have been finding similar VBA use cases online and editing them to my needs however i've encountered something where I have been unsuccessful in this manner.

I have a sheet of data, called 'Detail'.

I need to make a copy of this sheet 7 times with 7 different names for each sheet new sheet and each sheet to be filtered to a different value within a column.

I have managed to make a replica and name the sheets using the VBA below. However I don't know how to get each sheet to be filtered.

The column I wish to filter on is headed 'Client Rep' with the heading sitting in row 5. It is normally column S, but can move around.

Please could someone help and if so could you explain the stages as this will be of great help to my learning.

Thanks in advance

Sub CopyDetail() 'Copy Source sheet "Detail" and rename multiple copies different names
Dim i As Long
Dim Detail As Worksheet
Application.ScreenUpdating = False

Set Detail = ActiveSheet

Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Donna"
Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Emma"
Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Enid"
Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Jack"
Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Mark"
Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Nigel"
Detail.Copy After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Simon"
Application.ScreenUpdating = True

End Sub

 

[svendiamond]

You always need to copy this sheet exactly seven times, with those exact names? And are those the names you wish to filter under the Client Rep column?

 

[Mikeos145]

Yes 7 times and rename to those names. The names in the client rep columns are slightly different as it's their full names, but I should be able to adjust the names in the macro to fit this if you can show me what the macro should be.

Thanks

 

[svendiamond]

This code could be shorter if there were a list of the seven names in your workbook that we could loop through. If not, could just try something like this:

Sub copyThing()

'copy Detail sheet seven times and filter Client Rep column
'Donna, Emma, Enid, Jack, Mark, Nigel, Simon


Dim clienRepColumn As Long
Dim foundRepColumn As Range
Dim copySheet As Integer


'this is just to find what column has the Client Rep header
Set foundRepColumn = Sheets("Detail").Rows(5).Find("Client Rep")
    clienRepColumn = foundRepColumn.Column


'this is to copy, rename, and autofilter
Sheets("Detail").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Donna"
ActiveSheet.Rows(5).AutoFilter _
    Field:=clienRepColumn, _
    Criteria1:="Donna"

'same thing with the next name, just copy this another five times with the other names
Sheets("Detail").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Emma"
ActiveSheet.Rows(5).AutoFilter _
    Field:=clienRepColumn, _
    Criteria1:="Emma"


End Sub