How to split a single CSV to multiple ones using different headers in the end results

I have this problem one of my clients wants me to split a spreadsheet I have been sending him for a long time into 3 separate sheets, that is

I regularly sent a file called A.csv with headers head1, head2, head 3, head4 ..... head25

Now he needs instead of just the A.csv file

B.csv head1, head14
C.csv head4, head5, head6, head7, head10
D.csv head3, head14,head15
(This is just an example, no relation with the real one, but the method to solve this should be the same)
In fact I need just to copy the columns and split the original sheet into the 3 separate ones, using just the columns he wants for each case. I have no clue about how to do this, the thing is I tried concatenating all into a single column with separated values, but I have some cells that have commas in the content, so I am open to suggestions. Another problem is the fact that the CSVs are really long sometimes 10s of thousands of products. I know programming, but what I never did is to program within Excel, so I don't know the syntax nor the commands.

You could try this.

Sub ams()

Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet1").Range("A1:A" & lr).Copy Destination:=Sheets("Sheet2").Range("A1")
    Sheets("Sheet1").Range("N1:N" & lr).Copy Destination:=Sheets("Sheet2").Range("B1")
    Sheets("Sheet1").Range("D1:D" & lr).Copy Destination:=Sheets("Sheet3").Range("A1")
    Sheets("Sheet1").Range("E1:E" & lr).Copy Destination:=Sheets("Sheet3").Range("B1")
    Sheets("Sheet1").Range("F1:F" & lr).Copy Destination:=Sheets("Sheet3").Range("C1")
    Sheets("Sheet1").Range("G1:G" & lr).Copy Destination:=Sheets("Sheet3").Range("D1")
    Sheets("Sheet1").Range("J1:J" & lr).Copy Destination:=Sheets("Sheet3").Range("E1")
    Sheets("Sheet1").Range("C1:C" & lr).Copy Destination:=Sheets("Sheet4").Range("A1")
    Sheets("Sheet1").Range("N1:N" & lr).Copy Destination:=Sheets("Sheet4").Range("B1")
    Sheets("Sheet1").Range("O1:O" & lr).Copy Destination:=Sheets("Sheet4").Range("C1")
End Sub

ok here's what I got so far, my problem is I don't know how can I use this in multiple sheets, what I mean is I have many csv files, over 100, so right now I need to apply the macro to each one, is there a way to read the original file name so my sheets are named xxx-12345.csv, where xxx is any name with different lengths.

Sub CreateSheet1justName()
'
' CreateSheet1justName Macro
' Rick Fitzgerald (2013)

'

    Range("D:D,G:G,AK:AK,AM:AM,AP:AP,AS:AS,AT:AW").Select
    Range("AT1").Activate
    Selection.Copy
    Sheets("aem").Select
    'ActiveWindow.SmallScroll Down:=-78
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    'Dim targetCell As Range
    'targetCell.RowHeight = 16
    ActiveSheet.Copy
    ActiveWorkbook.Close True, "path"
    
End Sub
Sub vehicles()
'
' vehicles Macro
' captures all vehicle applications
'

'
    
    Range("AK:AK,BJ:BO,BQ:BQ").Select
    Range("BQ1").Activate
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
End Sub

Please note I need to add another sub plus so far I am just copying data in the same workbook, the end result, should be 3 separate csv files, one subroutine needs a function or a method to do this >>>

my source column looks like

Vehicle Warning Note
Cat Back Exhaust, Piping: 2.5", Tip: 1 Brushed Stainless, Tip Size: 2.5"

I need to output that in another sheet this way >>>

Part Number Attribute Name Attribute Value

600-0100 Piping 2.5"
600-0100 Tip 1 Brushed Stainless
600-0100 Tip Size 2.5"

I have another column called part number, but I need to split what's on warning note into the 3 or more separate rows duplicating the part number or leaving it blank so there are no confusions. Please note I didn't write that sub yet, the other two in the code section are working except for saving the each sheet to a separate one.