Move data from one sheet to mutlipe sheets based on sheet name

[DK27]

Hi,

Wondering if someone may be able to assist here. I have a set of data in a sheet sorted by client name (multiple lines per client) I also have an individual tab for each client name which i created using a pivot table and the show report filter pages feature. I would like to see if it is possibe to run a macro/formula that moves the data from the master sheet to the corresponding sheet for each client. I believe thier is around 400 clients, so 400 tabs/sheets. Any help will be greatly appreciated!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	management Org	Api Taskid	client Name	jobnumber	invoice Number	description	task	invoiced Date	mailing Address 2	mailing Address 1	management Org	invoice Description	customer On	invoice Total Ex
2	111 Customhouse Ltd	Jic6XyRQPFcsCg==	111 Customhouse Ltd	252072	431169	PM SERVICE: Auto Door and Roller Door - June 2022 (Auto x1 and Roller x1 - Mar Jun Sep Dec) 1.2.22 Sfp note added: Please sign-in on the "Asbestos Management contractors attendance sheet" Register is in the corridor off the foyer on the ground floor. Also a covid scan in doc. Code C3459. Can call Malcolm 021 053 2234 prop manager if any issues 11.12.18 Sfp note added: Swipe card required from workshop for the roller door.	iSoft House 111 - 113 Customhouse Quay	31/07/2022	Attention: Malcolm Shute	111 Customhouse Quay		PM SERVICE: Auto Door and Roller Door - June 2022	PM Service	XX
3	111 Customhouse Ltd	Jic6Sy1RTF8hCg==	111 Customhouse Ltd	246589	425731	PM SERVICE: Auto Door and Roller Door - March 2022 (Auto x1 and Roller x1 - Mar Jun Sep Dec) 11.12.18 Sfp note added: Swipe card required from workshop for the roller door. 1.2.22 Sfp note added: Please sign in on the "Asbestos Management contractors attendance sheet"	iSoft House 111 - 113 Customhouse Quay	27/04/2022	Attention: Malcolm Shute	111 Customhouse Quay		PM SERVICE: Auto Door and Roller Door - March 2022	PM Service	XX
4	111 Customhouse Ltd	Jic6VydQLFctCg==	111 Customhouse Ltd	258478	437661	PM SERVICE: Auto Door and Roller Door - September 2022 (Auto x1 and Roller x1 - Mar Jun Sep Dec) 1.2.22 Sfp note added: Please sign-in on the "Asbestos Management contractors attendance sheet" Register is in the corridor off the foyer on the ground floor. Also a covid scan in doc. Code C3459. Can call Malcolm 021 053 2234 prop manager if any issues 11.12.18 Sfp note added: Swipe card required from workshop for the roller door.	iSoft House 111 - 113 Customhouse Quay	30/11/2022	Attention: Malcolm Shute	111 Customhouse Quay		PM SERVICE: Auto Door and Roller Door - September 2022	PM Service	XX
5	120 The Terrace BC 86105	Jic6TyNQPFsjCg==	120 The Terrace BC 86105	243298	424878	PM SERVICE: Auto Door January 2022 (Auto x1 - Jan Apr Jul Oct and Access x3 - Apr Oct)	Quest Hotel	31/03/2022	Quest on Terrace	120 The Terrace		PM SERVICE: Auto Door January 2022	PM Service	XX
6	120 The Terrace BC 86105	Jic6RyJSXFMgCg==	120 The Terrace BC 86105	248498	427656	PM SERVICE: Auto and Manual Doors - April 2022 (Auto x1 - Jan Apr Jul Oct and Access x3 - Apr Oct)	Quest Hotel	31/05/2022	Quest on Terrace	120 The Terrace		PM SERVICE: Auto and Manual Doors - April 2022	PM Service	XX
7	120 The Terrace BC 86105	Jic6UyZQTFMhCg==	120 The Terrace BC 86105	261154	439154	PM SERVICE: Auto and Manual Doors - October 2022 (Auto x1 - Jan Apr Jul Oct and Access x3 - Apr Oct)	Quest Hotel	31/12/2022	Quest on Terrace	120 The Terrace		PM SERVICE: Auto and Manual Doors - October 2022	PM Service	XX
8	120 The Terrace BC 86105	Jic6XyxRPFMtCg==	120 The Terrace BC 86105	254369	432805	PM SERVICE: Auto Door - July 2022 (Auto x1 - Jan Apr Jul Oct and Access x3 - Apr Oct)	Quest Hotel	31/08/2022	Quest on Terrace	120 The Terrace		PM SERVICE: Auto Door - July 2022	PM Service	XX
9	30 Seconds Ltd	Jic6RyRQPFcjCg==	30 Seconds Ltd	247027	430167	PM SERVICE: Pallet Wrapper - March 2022 (x1 Pallet Wrapper Mar Sep) 11.12.20 Sfp note added: No service agreement in place programmed into system to track for Werner accounts charge up please	9 Garland Street Matamata	26/07/2022	9 Garland Street	PO Box 46		PM SERVICE: Pallet Wrapper - March 2022	PM Service	XX
10	385 Queen Street Limited	Jic6QyBSTFcsCg==	385 Queen Street Limited	250853	429938	PM SERVICE: Auto Door Roller Door and Manual Doors - April October 2022 (x1 Auto door Jan Apr Jul Oct x1 Roller Door and x25 manual doors April Oct) 21.4.22 Sfp note added: New contract received re-activated service	University of Otago House	21/07/2022	Private Bag 92142	Victoria West Street		PM SERVICE: Auto Door Roller Door and Manual Doors - April 2022	PM Service	XX
11	385 Queen Street Limited	Jic6Xy1QPEMmCg==	385 Queen Street Limited	254781	432809	PM SERVICE: Auto Door - July 2022 (x1 Auto door Jan Apr Jul Oct x1 Roller Door and x25 manual doors April Oct) 21.4.22 Sfp note added: New contract received re-activated service	University of Otago House	31/08/2022	Private Bag 92142	Victoria West Street		PM SERVICE: Auto Door - July 2022	PM Service	XX
12	385 Queen Street Limited	Jic6UyZQTEsiCg==	385 Queen Street Limited	261216	439195	PM SERVICE: Auto Door Roller Door and Manual Doors - October 2022 (x1 Auto door Jan Apr Jul Oct x1 Roller Door and x25 manual doors April Oct) 21.4.22 Sfp note added: New contract received re-activated service	University of Otago House	31/12/2022	Private Bag 92142	Victoria West Street		PM SERVICE: Auto Door Roller Door and Manual Doors - October 2022	PM Service	XX
13	525 Blenheim Road Ltd	Jic6VydSXE8mCg==	525 Blenheim Road Ltd	258832	437669	PM SERVICE: Roller Doors - September 2022 (x6 Roller Doors Mar Sep) 21.12.18 Sfp note added: Scissor Lift needed & allowed for in service 11.3.21 Please call and book a time to attend manager Dan 03 348 8422 7.4.21 Sfp note added: As per Mayra - client advised use PO on seervice - PO99000067	Tyre General	30/11/2022	PO Box 13-0060	Armagh		PM SERVICE: Roller Doors - September 2022	PO99000067	XX
14	525 Blenheim Road Ltd	Jic6RyRQLEsnCg==	525 Blenheim Road Ltd	246979	425451	PM SERVICE: Roller Doors - March 2022 (x6 Roller Doors Mar Sep) 21.12.18 Sfp note added: Scissor Lift needed & allowed for in service 11.3.21 Please call and book a time to attend manager Steve 03 348 8422 7.4.21 Sfp note added: As per Mayra - client advised use PO on seervice - PO99000067	Tyre General	14/04/2022	PO Box 13-0060	Armagh		PM SERVICE: Roller Doors - March 2022	PO99000067	XX
15	A & AM Muollo Family Trust	Jic6RyJSXFMhCg==	A & AM Muollo Family Trust	248497	428202	PM SERVICE: Auto Door and Roller Door - April 2022 (Auto x1 and Roller x1 - Jan Apr Jul Oct)	Promotus House	15/06/2022	58 Oriental Bay Parade	Wellington		PM SERVICE: Auto Door and Roller Door - April 2022	PM Service	XX
16	A & AM Muollo Family Trust	Jic6TyNRPEskCg==	A & AM Muollo Family Trust	243498	425119	PM SERVICE: Auto Door and Roller Door - January 2022 (Auto x1 and Roller x1 - Jan Apr Jul Oct)	Promotus House	31/03/2022	58 Oriental Bay Parade	Wellington		PM SERVICE: Auto Door and Roller Door - January 2022	PM Service	XX
17	A & AM Muollo Family Trust	Jic6Xy1QXEciCg==	A & AM Muollo Family Trust	254596	432833	PM SERVICE: Auto Door and Roller Door - July 2022 (Auto x1 and Roller x1 - Jan Apr Jul Oct) 25.3.22 Sfp note added: Luigi Muollo requested we send an email once service is completed to confirm service has been done to: t.amuollo@xtra.co.nz	Promotus House	31/08/2022	58 Oriental Bay Parade	Wellington		PM SERVICE: Auto Door and Roller Door - July 2022	PM Service	XX
18	A & AM Muollo Family Trust	Jic6UyZQTFMmCg==	A & AM Muollo Family Trust	261153	439158	PM SERVICE: Auto Door and Roller Door - October 2022 (Auto x1 and Roller x1 - Jan Apr Jul Oct) 25.3.22 Sfp note added: Luigi Muollo requested we send an email once service is completed to confirm service has been done to: t.amuollo@xtra.co.nz	Promotus House	31/12/2022	58 Oriental Bay Parade	Wellington		PM SERVICE: Auto Door and Roller Door - October 2022	PM Service	XX
19	A B Investments Limited	Jic6RyRQLEchCg==	A B Investments Limited	246971	427177	PM SERVICE: Roller Doors - March 2022 (x3 Roller doors Mar Jun Sep Dec x2 Rubbish Room Roller Doors Mar Sep) 11.3.21 sfp note added: Call Sheryle Wotton to book a time 027 669 7977 - Tuesday mornings are best 29.3.18 Sfp note added: Scissor lift needed for service (accounts - allowed for in costs) 13.3.19 Sfp note added: Building B roller door added to schedule door comes up Mon – Fri 8.30am so a service can be undertaken in the morning prior to opening. Swipe card needed for access please collect from Colliers as per Sheryle Wooton @ Colliers	The Crossing Christchurch	26/05/2022	T/A The Crossing	PO Box 1047 Linwood		PM SERVICE: Roller Doors - March 2022 RD01 - Entry Lane Car Park RD02 - Exit Lane From Car Park RD03 - Rubbish Room L/H RD04 - Rubbish Room R/H RD05 - Building B	PM Service	XX
20	A B Investments Limited	Jic6TyRQLDchCg==	A B Investments Limited	241428	423456	PM SERVICE: Roller Doors - December 2021 (x3 Roller doors Mar Jun Sep Dec x2 Rubbish Room Roller Doors Mar Sep) 11.3.21 sfp note added: Call Sheryle Wotton to book a time 027 669 7977 - Tuesday mornings are best 29.3.18 Sfp note added: Scissor lift needed for service (accounts - allowed for in costs) 13.3.19 Sfp note added: Building B roller door added to schedule door comes up Mon – Fri 8.30am so a service can be undertaken in the morning prior to opening. Swipe card needed for access please collect from Colliers as per Sheryle Wooton @ Colliers	The Crossing Christchurch	10/03/2022	T/A The Crossing	PO Box 1047 Linwood		PM SERVICE: 3 x Roller Doors - December 2021 RD01 - Entry Lane Car Park RD02 - Exit Lane From Car Park RD05 - Building B	PM Service	XX
21	A B Investments Limited	Jic6VydSXEctCg==	A B Investments Limited	258817	437673	PM SERVICE: Roller Doors - September 2022 (x3 Roller doors Mar Jun Sep Dec x2 Rubbish Room Roller Doors Mar Sep) 13.09.22 EB note added: 2x techs book with Sheryle - early am or late pm scissor lift req. 28.3.22 Sfp note added: Site want serivce done after hours need to re do contract to allow for after hours JUNE 22 11.3.21 sfp note added: Call Sheryle Wotton to book a time 027 669 7977 - Tuesday mornings are best 29.3.18 Sfp note added: Scissor lift needed for service (accounts - allowed for in costs) 13.3.19 Sfp note added: Building B roller door added to schedule door comes up Mon – Fri 8.30am so a service can be undertaken in the morning prior to opening. Swipe card needed for access please collect from Colliers as per Sheryle Wooton @ Colliers	The Crossing Christchurch	30/11/2022	T/A The Crossing	PO Box 1047 Linwood		PM SERVICE: Roller Doors - September 2022 RD01 - Entry Lane Car Park RD02 - Exit Lane From Car Park RD03 - Rubbish Room L/H RD04 - Rubbish Room R/H RD05 - Building B	PM Service	XX
22	A B Investments Limited	Jic6XyVRTF8jCg==	A B Investments Limited	252485	431557	PM SERVICE: Roller Doors - June 2022 (x3 Roller doors Mar Jun Sep Dec x2 Rubbish Room Roller Doors Mar Sep) 28.3.22 Sfp note added: Site want serivce done after hours need to re do contract to allow for after hours JUNE 22 11.3.21 sfp note added: Call Sheryle Wotton to book a time 027 669 7977 - Tuesday mornings are best 29.3.18 Sfp note added: Scissor lift needed for service (accounts - allowed for in costs) 13.3.19 Sfp note added: Building B roller door added to schedule door comes up Mon – Fri 8.30am so a service can be undertaken in the morning prior to opening. Swipe card needed for access please collect from Colliers as per Sheryle Wooton @ Colliers	The Crossing Christchurch	16/08/2022	T/A The Crossing	PO Box 1047 Linwood		PM SERVICE: Roller Doors - June 2022 RD01 - Entry Lane Car Park RD02 - Exit Lane From Car Park RD05 - Building B	PM Service	XX
23	AceDoors South Island Ltd	Jic6SydQPFMjCg==	AceDoors South Island Ltd	245276	425163	PM SERVICE: Auto Doors - February 2022 (x3 Auto doors Feb Aug) Note: Client advised we will not sign off 12A servicing 6 monthly. Site advised they have IQP to sign off 3.1	Toyota Rangiora	31/03/2022	PO Box 76105	Manukau City		PM SERVICE: Auto Doors - February 2022	CRS24559	XX
24	AceDoors South Island Ltd	Jic6WyNQTFsgCg==	AceDoors South Island Ltd	256974	434650	PM SERVICE: Auto Doors - August 2022 (x3 Auto doors Feb Aug) Note: Client advised we will not sign off 12A servicing 6 monthly. Site advised they have IQP to sign off 3.1	Toyota Rangiora	30/09/2022	PO Box 76105	Manukau City		PM SERVICE: Auto Doors - August 2022	CRS24559	XX
25	ADHB Facilities & Dev	Jic6TyJSTEskCg==	ADHB Facilities & Dev	243092	423746	BWOF Inspection - 3.1: Breakout/Failsafe Auto/Slide Egress Door. Site extn 22547 - January 2022 Asset No: 112159 BUILDING G15-G17 GREENLANE- NW	Greenlane Clinical Centre	16/03/2022	A07 Aquaheat	2 Park Road		BWOF Inspection - 3.1: Breakout/Failsafe Auto/Slide Egress Door - January 2022 Asset No: 112159 BUILDING G15-G17 GREENLANE- NW 20/1/2022: For BWOF inspection on auto doors as requested.	P0183158 PO3095873	XX
26	ADHB Facilities & Dev	Jic6TyJSTEshCg==	ADHB Facilities & Dev	243093	422198	BWOF Inspection - 3.1: Breakout/Failsafe Auto/Slide Egress Door - January 2022 Asset No. 101904 BUILDING G04 GREENLANE - Ambulatory	Greenlane Clinical Centre	14/02/2022	A07 Aquaheat	2 Park Road		BWOF Inspection - 3.1: Breakout/Failsafe Auto/Slide Egress Door - January 2022 Asset No. 101904 BUILDING G04 GREENLANE - Ambulatory Sign in locate door and set up safety requirements. Complete job safety analysis and carry out BWOF Inspection. Pack up after job completion and complete sign out process. 21/1/2022: Carry out BWOF Inspection of egress door as required.	P0183154 PO3095873	XX
27	ADHB Facilities & Dev	Jic6TyJSTEsgCg==	ADHB Facilities & Dev	243094	422199	BWOF Inspection - 3.1: Breakout/Failsafe Auto/Slide Egress Door - January 2022 Asset No. 101908 - Building G08 Greenlane - Eye Clinic	Greenlane Clinical Centre	14/02/2022	A07 Aquaheat	2 Park Road		BWOF Inspection - 3.1: Breakout/Failsafe Auto/Slide Egress Door - January 2022 Asset No. 101908 - Building G08 Greenlane - Eye Clinic Sign in locate door and set up safety requirements. Complete job safety analysis and carry out BWOF Inspection . Pack up after job completion and complete sign out process. 20/1/2022: Carry out BWOF Inspection on egress door as required.	P0183156 PO3095873	XX
Sheet1

 

[Anbuselvam]

Please upload one drive or dropbox file link with one sample of expected results to try further.

 

[Herakles]

Do the client sheets already have data in them?

If so then can I assume that you want to add onto the end of the existing data?

 

[Herakles]

This code will, for each unique value in a specified column, copy all rows for each unique value
into a sheet with that value as it's name.

A new sheet will be created if it does not exist already.

Call the code using this line:

Call subFilterAndCopyData("Master", 3, "UniqueList")

The procedure has three arguments:

1. The name of the sheet containing the source data.
2. The column number containing the value to split the data on.
3. A worksheet name that is used temporarily to store the unique list of values held in the column specified.

Public Sub subFilterAndCopyData(strSourceSheet As String, intSplitByColumn As Integer, strUniqueListSheetName As String)
Dim Ws As Worksheet
Dim WsTarget As Worksheet
Dim WsMaster As Worksheet
Dim arrUniqueList() As Variant
Dim rngList As Range
Dim i As Integer
Dim blnExists As Boolean
Dim lngLastRow As Long
Dim rngFiltered As Range
Dim blnInvalid As Boolean
Dim x As Integer
Dim strInvalidNames As String
Dim strInvalidChars As String
Dim rngMaster As Range
Dim intCounter As Integer
Dim rngSplitBy As Range

    ActiveWorkbook.Save
    
    strInvalidChars = "/\[]*:?"
        
    Set WsMaster = Worksheets(strSourceSheet)
        
    WsMaster.AutoFilterMode = False
    
    Set rngMaster = WsMaster.Range("A1").CurrentRegion
       
    Set rngSplitBy = WsMaster.Range("A1").CurrentRegion.Columns(intSplitByColumn)
    Set rngSplitBy = rngSplitBy.Offset(1, 0).Resize(rngMaster.Rows.Count - 1, 1)
    
    lngLastRow = WsMaster.Range("A" & Rows.Count).End(xlUp).Row
    
    blnExists = False
    For Each Ws In Worksheets
        If Ws.Name = strUniqueListSheetName Then
            blnExists = True
        End If
    Next Ws
    If Not blnExists Then
        Worksheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = strUniqueListSheetName
    End If
        
    With Worksheets(strUniqueListSheetName).Range("A1")
        .Clear
        .Formula2 = "=UNIQUE(" & WsMaster.Name & "!" & rngSplitBy.Address & ",FALSE,FALSE)"
        Set rngList = .CurrentRegion.Columns(1)
        arrUniqueList = rngList
    End With
        
    For i = 1 To UBound(arrUniqueList, 1)
    
        blnInvalid = False
        
        For x = 1 To Len(arrUniqueList(i, 1))
            If InStr(1, strInvalidChars, Mid(arrUniqueList(i, 1), x, 1), vbTextCompare) > 0 Then
                strInvalidNames = strInvalidNames & vbCrLf & arrUniqueList(i, 1)
                blnInvalid = True
            End If
        Next x
        
        If Not blnInvalid Then
        
            blnExists = False
                    
            For Each Ws In Worksheets
                If Ws.Name = arrUniqueList(i, 1) Then
                    blnExists = True
                End If
            Next Ws
            
            ' Create worksheet if it does not exist.
           If Not blnExists Then
                Worksheets.Add after:=Worksheets(Sheets.Count)
                ActiveSheet.Name = arrUniqueList(i, 1)
                Set WsTarget = Worksheets(CStr(arrUniqueList(i, 1)))
            Else
                Set WsTarget = Worksheets(arrUniqueList(i, 1))
                WsTarget.Cells.ClearContents
            End If

            WsMaster.Rows(1).Copy Destination:=WsTarget.Range("A1")
            intCounter = intCounter + 1
                        
            ' Filter worksheet and copy data.
            With WsMaster
                rngMaster.AutoFilter Field:=intSplitByColumn, Criteria1:=arrUniqueList(i, 1)
                With .AutoFilter.Range
                    Set rngFiltered = .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible)
                    rngFiltered.Copy Destination:=Worksheets(arrUniqueList(i, 1)).Range("A2")
                    Worksheets(arrUniqueList(i, 1)).Cells.EntireColumn.AutoFit
                 End With
                 .AutoFilterMode = False
            End With
            
        End If
                
    Next i
    
    Application.DisplayAlerts = False
    Worksheets("UniqueList").Delete
    Application.DisplayAlerts = True
    
    If strInvalidNames <> "" Then
        MsgBox "These worksheets have not been created." & vbCrLf & strInvalidNames, vbCritical, "Invalid Worksheet Names"
    End If
    
End Sub