Macro to sort two different tables in same sheet

[ExlN00b]

I have a sheet with two separate tables - need to sort them by Column B, separately. The VBA I have combines the two tables and sorts them into one table.

Need:

1. Sort Column B from "1" to the last number in the set (example: Table 1 from 1-6; Table 2 from 1-4)
2. The only constant is "1" to indicate the start of the table

Any assistance or pointing to the right direction would be great!

Sub PO_CSV_Multi()
'
' PO_CSV_Multi Macro
'
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Selection.ColumnWidth = 11.38

Range("B18", Range("J" & Rows.count).End(xlUp).Address).Sort Key1:=[b3], _
Order1:=xlAscending, Header:=xlNo

End Sub

 

[earthworm]

you need to paste the data that can be copied here for users to assist you further , Its difficult to give solution where data is in image

 

[My Aswer Is This]

I may be wrong but I see many postings where users refer to "Tables"
But in this post and the image I see no reference to Tables.

A Table from my understand is a Range on a sheet.
And the Table has a name.
Are you sure you have a Table here?

 

[earthworm]

I may be wrong but I see many postings where users refer to "Tables"
But in this post and the image I see no reference to Tables.

A Table from my understand is a Range on a sheet.
And the Table has a name.
Are you sure you have a Table here?

Never mind . From the picture it seems like a range to me . but you are referring to table . Maybe someone else can assist you

 

[ExlN00b]

Sorry! The "table" I was referring to was the label to indicate two different sets of ranges to sort.

I'm uploading the mini-sheet and attempting better details:

1. "Order Number: 1" and "Order Number: 2" are separate, but are exported to the same CSV file
   1. "Order Number: 1" and "Order Number: 2" are found in G1 and G35
2. Under "Order Number: 1" I would like to sort by the numerals in Column B in order to condense and remove extraneous lines for easy copy and paste
   1. This will bring 1; 2; 3; and 4 together under "Order Number: 1"
3. Separately, I would like to do the same as #2 for "Order Number: 2"
   1. Bringing 1; 2; 3; 4; 5; and 6 together under "Order Number: 2"
4. Is it possible to create a Macro that does both #2 and #3 consecutively so I don't have to do it for each separate order?
   1. Something like start in B18; sort rows 18 - 25
   2. Search for next instance of "1" in Column B; then sort rows 52 - 65
   3. Search for next instance of "1" in Column B; if none; stop

My current VBA allows me to do #2 only if I have one "Order Number" in the sheet. If I have more than one "Order Number", it will sort both "Order Numbers" together and consolidate into a singular sorted range. I would like to keep both "Orders" separate.

Details:

1. The one constant is B18 is always the first instance of "1" in Column B
2. The number of rows to be sorted within each "Order Number" is not constant - it changes depending on the number of items under the "Order Number"
3. There is no other information in Column B except for the numbers to be sorted

Hoping this helps - thank you!

Sort_Different_Ranges.csv
	A	B	C	D	E	F	G	H	I	J
1							Order Number :		1
2							Delivery Date :		10/3/2022
3
4							Order Date:		10/21/2021
5							Phone :
6							Fax :
7
8							PO Created By :
9							Email :
10	Bill To :						F.O.B :
11							Ship Via :
12							Payment Terms :
13							Supplier A/C No. :
14
15	Phone :
16	Fax :
17	Line		Product #	Product Description	Brand	Vendor Prd. No.	Order Qty	Purchase Unit	Price	Cost
18		1	101034	TEST PRODUCT A	BRAND A	123456	49	CASE [18 Each]	$10.00	$490.00
19				Detail a
20				Detail b
21		2	78275	Freight Charges	Freight		1	Each	$0.00	$0.00
22				Each
23				Freight Charge
24		3	10238171	DEPOSIT			1	Each	$2,033.76	$2,033.76
25		4	10238172	DEPOSIT OFFSET			-1	Each	$2,033.76	($2,033.76)
26						Net Purchase Order Amount				$490.00
27						Sales Tax | TAX				$31.85
28						Discount				$0.00
29						NO RATE| MISCELLANEOUS				$0.00
30							PO Total Amount			$521.85
31
32
33
34
35							Order Number :		2
36							Delivery Date :		3/7/2022
37
38							Order Date:		10/27/2021
39							Phone :
40							Fax :
41
42							PO Created By :
43							Email :
44	Bill To :						F.O.B :
45							Ship Via :
46							Payment Terms :
47							Supplier A/C No. :
48
49
50
51	Line		Product #	Product Description	Brand	Vendor Prd. No.	Order Qty	Purchase Unit	Price	Cost
52		1	187244	TEST PRODUCT B	BRAND B	123456	6	Each	$10.00	$60.00
53				Detail c
54				Detail d
55		2	181196	TEST PRODUCT C	BRAND C	123456	20	Each	$10.00	$200.00
56				Detail e
57				Detail f
58		3	181196	TEST PRODUCT D	BRAND D	123456	20	Each	$10.00	$200.00
59				Detail g
60				Detail h
61		4	78275	Freight Charges	Freight		1	Each	$245.40	$245.40
62				Each
63				Freight Charge
64		5	10239495	PRE-PAYMENT			1	Each	$1,995.92	$1,995.92
65		6	10239496	PRE-PAYMENT OFFSET			-1	Each	$1,995.92	($1,995.92)
66						Net Purchase Order Amount				$705.40
67						Sales Tax | TAX				$45.85
68						Discount				$0.00
69						NO RATE| MISCELLANEOUS				$0.00
70							PO Total Amount			$751.25
Sort_Different_Ranges

Cell Formulas
Range	Formula
J18,J58,J55,J52	J18	=G18*I18
J26	J26	=SUM(J18:J23)
J27,J67	J27	=J26*0.065
J30,J70	J30	=SUM(J26:J29)
J66	J66	=SUM(J52:J63)

 

[earthworm]

VBA is more difficult for this type . I think formula approach is much easier . How many max order numbers can be there on sheet.

 

[ExlN00b]

VBA is more difficult for this type . I think formula approach is much easier . How many max order numbers can be there on sheet.

There is no max order numbers. I’m interested in what you mean by “formula”. There wouldn’t be a VBA to find the next “1” in Column B and sort that set of lines? Anything that will make this an easier process than selecting each set of lines for each order number separately. Thank you so much!

 

[earthworm]

I can create template from where you can dump and and then get result from another sheet.

 

[ExlN00b]

That would be so great! Thank you so so much for your help!!

 

[ExlN00b]

I can create template from where you can dump and and then get result from another sheet.

Hello! Checking if you’re able to assist me with the template to be able to format and sort different sections separately? Thank you so much!