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
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