Conditional Split File Based on Row and Column header Values

Hi I Would like to split a source Excel file containing a list of shipment of products to multiple destination per week, so that it can be split into individual excel files based on the destination per week. Screenshot 2022-08-09 092733.png
The output of the above single source file would be 5 separate files:

1. Shipment List to London for Week 1
2. Shipment List to London for Week 2
3. Shipment List to New York for Week 1
4. Shipment List to New York for Week 2
5. Shipment List to Bangkok for Week 2

Screenshot 2022-08-09 093232.png

Would like to know if it is possible to do in Macro and if yes, how the code will be.

Attaching the file as well for your reference. Sample Split File.xlsx
Please do note that this is a dummy sample file, and the real use will be on a bigger data source. But the functions needed are already represented in the file.
Looking forward to the support. Thank you!

Upvoting this Thread, Thanks!

Originally Posted by patrickrw99

... and the real use will be on a bigger data source.

That is to say: More rows or more columns?...

Originally Posted by beyond Excel

That is to say: More rows or more columns?...

Yes but its scalable I believe (e.g. more weeks (columns) or more peoducts (rows)

Originally Posted by beyond Excel

Are you absolutely sure that the first four columns will ALWAYS be the same?

And what will be the name of the workbooks to generate?

Hi, Sorry for the late reply. yes the first four columns will be the same, the workbook name will be 'Shipment List_Destination_Week#'. Thank you!

Based on your pics.

Assuming the name of the source data sheet is "Sheet1" and the header starts from A1.

Please Login or Register  to view this content.

Originally Posted by jindon

Based on your pics.

Assuming the name of the source data sheet is "Sheet1" and the header starts from A1.

Please Login or Register  to view this content.

Wow thanks! I will try the code and update to you what I got. Thank you!

Hi. Another way to do it:

Please Login or Register  to view this content.

Originally Posted by beyond Excel

Hi. Another way to do it:

Please Login or Register  to view this content.

Hi, Thank you for the solution, will try this as well.

The Solution from @jindon works great Thank you for all the help