Split into worksheets based on multiple criteria

C

cdrobinson83

New Member
Joined
May 3, 2021
Messages
31
Office Version
  1. 365
Platform
  1. Windows
Hello,

Can someone please help to create a macro that splits the below worksheet data into individual worksheets based on:

1. ACCOUNT # (Column H)
2. BUY/SELL (Column D)
3. CONTRACT (Column L)

Data starts in Row 2, I would like to keep the header (Row 1) and copy ALL data to the new worksheets based on the above criteria. Sheets to be named ACCOUNT #_BUY/SELL_CONTRACT if possible.

Thanks again for all of the help on this board.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
BUS DATEEXCHANGEINVOICE NUMBERBUY/SELLCMFTMFORIGINACCOUNT #OPPOSITE CMFOPPOSITE TMFOPPOSITE ORIGINCONTRACTPERIODCVFPRICECONTRACT VALUECCYINTENT DATEDELIVERY DATEFACILITY NAMEFACILITY NUMBERCERTIFICATE NUMBERGRADEFORMBRAND(S)NUMBER OF BARSNUMBER OF BUNDLESNUMBER OF PIECESNET WEIGHTFINE WEIGHTMETRIC TONSSTORAGE TYPESTORAGE DAYSSTORAGE RATESTORAGE PAID THRU DATEFUTURES COSTHANDLING OUT CHARGESTORAGE CHARGETOTAL AMOUNT
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069051FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1624842 250.511/30/2024-108000.6-625-155.26-108780.86
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069061FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/525172 250.511/30/2024-109435.27-625-157.33-110217.6
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069071FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1624612 250.511/30/2024-107000.67-625-153.83-107779.5
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069081FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/325471 250.511/30/2024-110735.17-625-159.2-111519.37
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069091FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1624895 250.511/30/2024-108231.01-625-155.6-109011.61
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069101FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/525147 250.511/30/2024-109326.58-625-157.18-110108.76
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069111FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/324718 250.511/30/2024-107461.51-625-154.49-108241
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069121FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/425046 250.511/30/2024-108887.49-625-156.54-109669.03
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069131FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/325498 250.511/30/2024-110852.56-625-159.36-111636.92
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069141FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/625272 250.511/30/2024-109870.02-625-157.95-110652.97
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069151FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/325026 250.511/30/2024-108800.54-625-156.41-109581.95
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069161FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1624592 250.511/30/2024-106913.72-625-153.7-107692.42
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069171FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/325254 250.511/30/2024-109791.77-625-157.84-110574.61
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069181FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1624667 250.511/30/2024-107239.78-625-154.18-108018.96
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069191FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/225271 250.511/30/2024-109865.67-625-157.95-110648.62
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069201FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/425472 250.511/30/2024-110739.52-625-159.2-111523.72
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069211FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1625195 250.511/30/2024-109535.26-625-157.48-110317.74
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT1QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069221FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1624650 250.511/30/2024-107165.88-625-154.06-107944.94
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT5QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069231FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/525061 250.511/30/2024-108952.7-625-156.64-109734.34
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT9QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069241FULL_PLATECCCP/CCCP/CCCP/CCCP/CCCP 1/2/3/4/5 16/16/16/16/524929 250.511/30/2024-108378.83-625-155.81-109159.64
11/4/2024COMEX1274862322BUYERXXXYYYCUSTACCOUNT12QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 59331069251FULL_PLATECCCP/CCCP/CCCP/CCCP 1/2/3/4 16/16/16/1625289 250.511/30/2024-109943.93-625-158.06-110726.99
11/4/2024COMEX1274862318SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148551FULL_PLATECSBL/CSBL/CSBL/CSBL/CSBL 1/2/3/4/5 55/55/55/55/5525301 250.466811/30/2024109996.1600147.64110743.74
11/4/2024COMEX1274862318SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148561FULL_PLATECSBL/CSBL/CSBL/CSBL/CSBL 1/2/3/4/5 55/55/55/55/2025349 250.466811/30/2024110204.78600147.92110952.7
11/4/2024COMEX1274862318SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148571FULL_PLATECSBL/CSBL/CSBL/CSBL/CSBL 1/2/3/4/5 55/55/55/55/3025227 250.466811/30/2024109674.38600147.21110421.59
11/4/2024COMEX1274862324SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148581FULL_PLATECSBL/CSBL/CSBL/CSBL/CSBL/CSBL 1/2/3/4/5/6 55/55/55/55/25/725496 250.466811/30/2024110843.86600148.77111592.63
11/4/2024COMEX1274862315SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148751FULL_PLATEMELT/MELT/MELT/MELT/MELT 1/2/3/4/5 64/57/62/50/6024680 250.466811/30/2024107296.3600144.01108040.31
11/4/2024COMEX1274862315SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148761FULL_PLATEMELT/MELT/MELT/MELT/MELT 1/2/3/4/5 52/52/58/56/4825311 250.466811/30/2024110039.57600147.7110787.27
11/4/2024COMEX1274862315SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148771FULL_PLATEMELT/MELT/MELT/MELT/MELT 1/2/3/4/5 54/48/50/54/5125414 250.466811/30/2024110487.37600148.29111235.66
11/4/2024COMEX1274862315SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148781FULL_PLATEMELT/MELT/MELT/MELT/MELT 1/2/3/4/5 56/62/54/45/4825207 250.466811/30/2024109587.43600147.09110334.52
11/4/2024COMEX1274862315SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593148791FULL_PLATEMELT/MELT/MELT/MELT/MELT 1/2/3/4/5 54/62/47/62/5025355 250.466811/30/2024110230.86600147.95110978.81
11/4/2024COMEX1274862324SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593150361FULL_PLATEESOX/ESOX/ESOX/ESOX/ESOX 1/2/3/4/5 34/35/32/33/1525133 250.466811/30/2024109265.72600146.66110012.38
11/4/2024COMEX1274862324SELLERXXXYYYCUSTACCOUNT3QQQZZZCUSTHG202411250004.3475108687.5USD11/1/202411/5/2024 593150371FULL_PLATEESOX/ESOX/ESOX/ESOX/ESOX 1/2/3/4/5 34/31/34/31/1525079 250.466811/30/2024109030.95600146.34109777.29
 
Upvote 0
Assuming the data source sheet is named Sheet1.
If the length of the combined name exceeds 31, it will raise runtime error.
Rich (BB code): 
Sub test()
    Dim a, i&, ii&, s$, r As Range, c As Range, dic As Object
    Application.ScreenUpdating = False
    Set dic = CreateObject("Scripting.Dictionary")
    Set r = Sheets("sheet1").[a1].CurrentRegion '<--- Change sheet1 to actual source sheet name 
    Set c = r.Offset(, r.Columns.Count + 2).Range("a1:a2")
    a = Application.Index(r, Application.Sequence(r.Rows.Count, , 1, 1), [{8,4,12}])
    For i = 2 To UBound(a, 1)
        s = Join(Array(a(i, 1), a(i, 2), a(i, 3)), "_")
        If Not dic.exists(s) Then
            dic(s) = Empty
            If Not Evaluate("isref('" & s & "'!a1)") Then
                Sheets.Add(, Sheets(Sheets.Count)).Name = s
            End If
            For ii = 1 To UBound(a, 2)
                If Not IsNumeric(a(i, ii)) Then a(i, ii) = Chr(34) & a(i, ii) & Chr(34)
            Next
            With Sheets(s)
                .UsedRange.Clear
                r.Rows(1).Copy .[a1]
                For ii = 1 To r.Columns.Count
                    .Columns(ii).ColumnWidth = r.Columns(ii).ColumnWidth
                Next
                c(2).Formula = "=and(h2=" & a(i, 1) & ",d2=" & a(i, 2) & ",l2=" & a(i, 3) & ")"
                r.AdvancedFilter 2, c, .[a1].CurrentRegion
            End With
        End If
    Next
    c.Clear
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
I just realized that if the account number begins with a zero, then when the new worksheets are created, those sheets are empty. Is that something that can be amended? It works perfectly for all other accounts.

1730828574024.png


1730828617376.png
 
Upvote 0
Try change
Rich (BB code): 
            For ii = 1 To UBound(a, 2)
                If Not IsNumeric(a(i, ii)) Then a(i, ii) = Chr(34) & a(i, ii) & Chr(34)
            Next
to
Rich (BB code): 
            For ii = 1 To UBound(a, 2)
                If TypeName(a(i, ii)) = "String" Then a(i, ii) = Chr(34) & a(i, ii) & Chr(34)
            Next
 
Upvote 0
Solution
You are welcome and thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Replacing a cell value using VBA code
Replies
4
Views
178
RobP
R
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
225
igold
igold
S
  • Question Question
Split data into two halves based on column values
Replies
1
Views
209
Joe4
Joe4
S
  • Solved
Copying columns of data to second worksheet based on input on third worksheet
Replies
2
Views
168
StuartE
S
W
  • Question Question
Move Row to Another Sheet Based on Multiple Cell Values
Replies
9
Views
484
jolivanes
jolivanes

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top