Cancel merged columns and arrange structure across files in the folders

[Abdo]

Hi,
I would cancel merged columns and cells across all of files are existed in this device"C:\Users\Abdo\Desktop\TYR"
each file contains different counts sheets also I would arrange columns across files from A,C,E,H,M,N,K to A,B,C,D,E,F,G across sheets for each file.
so the macro should be in MASTER file is open , others files are closed .
the result should be for each file is closed , but the macro should in MASTER file
all of files are xls extension .
thanks.
.

 

[Joe4]

Record yourself moving them one at a time.
You will only need to do that once, as once you have it in your code, VBA will do all those steps every time.

 

[Abdo]

how about this try?

Range("A:A,C:C,E:E,H:H,M:M,N:N,K:K").Select
    Range("K1").Activate
    Selection.Copy
    Columns("A:H").Select
    ActiveSheet.Paste

 

[Joe4]

Did you even try it? That code returns errors for me.
It looks to me like you are copying the value from K1 to column A:H.

You originally said:

I would arrange columns across files from A,C,E,H,M,N,K to A,B,C,D,E,F,G across sheets for each file

It looks like you really just want to move column K to the end and delete a bunch of columns.
If so, this should do that:

Sub MyFormattingFixMacro()

    Dim pth As String
    Dim ext As String
    Dim fl As String
    Dim wb As Workbook
    Dim ws As Worksheet

    Application.ScreenUpdating = False

'   Enter path and file extension to look for
    pth = "C:\Users\Abdo\Desktop\TYR\"
    ext = "*.xls*"

'   Target path and extension
    fl = Dir(pth & ext)

'   Loop through each Excel file in folder
    Do While fl <> ""
'       Open workbook and assign to object variable
        Set wb = Workbooks.Open(Filename:=pth & fl)
'       Loop through all sheets
        For Each ws In wb.Worksheets
            With ws
'               Move and delete columns
                .Columns("K:K").Copy .Range("O1")
                .Range("B:B,D:D,F:F,G:G,I:I,J:J,K:K,L:L").Delete Shift:=xlToLeft
            End With
        Next ws
'       Close workbook
        wb.Close SaveChanges:=True
'       Get next file name
        fl = Dir
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

 

[Abdo]

as I said

I would arrange columns across files from A,C,E,H,M,N,K to A,B,C,D,E,F,G across sheets for each file

It looks like you really just want to move column K to the end and delete a bunch of columns.

no this is based on recorded macro what I got .

 

[Joe4]

as I said





no this is based on recorded macro what I got .

Did you try the complete code I posted in my last reply?

 

[Abdo]

yes and your code delete columns without move to target location !

 

[Joe4]

Then I am not clear on what you want moved and what you want removed.
So let's approach it from this angle.

Make a two column table and post it here.
In the first column, list all the columns that are on the sheet initially, i.e. A, B, C, D, etc all the way to the last column you have with data.
In the second column, indicate where each of these columns should reside when finished, i.e. which column they should be in, or if they should be deleted altogether.

That should remove any doubt about what you are trying to do, and then we can make sure that the code does that.

 

[Abdo]

after cancel merged cells
here is example.
before

ABB.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	REF NO	BRAND	DDT.NO	FIRST	SECOND		NAME			INPUT		OUTPUT	NOTE
2	1	ESS 00	VBGTL100	D#	HSD0001	SS1233		AAB1			2000		250	##1
3	2	ESS 01	VBGTL101	D#	HSD0002	SS1234		AAB2			30000		150
4	3	ESS 02	VBGTL102	D#	HSD0003	SS1235		AAB3			4000		120
5	4	ESS 03	VBGTL103	D#	HSD0004	SS1236		AAB4			3200		130	##CV
6	5	ESS 04	VBGTL104	D#	HSD0005	SS1237		AAB5			1200		140
7	6	ESS 05	VBGTL105	D#	HSD0006	SS1238		AAB6			1300		150
8	7	ESS 06	VBGTL106	D#	HSD0007	SS1239		AAB7			1500		160	##$SD
9	8	ESS 07	VBGTL107	D#	HSD0008	SS1240		AAB8			1600		170
10	9	ESS 08	VBGTL108	D#	HSD0009	SS1241		AAB9			1700		8
11	10	ESS 09	VBGTL109	D#	HSD0010	SS1242		AAB10			1800		58
ss

ABB.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	DATE	INVOICE NO	BATCH NO	CONDITION NO	COMPANY NAME	CUSTOMER NO		IMPORT			EXPORT		REF NO	TOTAL
2	01/01/2024	AXCD344	VFGT555	CDFFR	AQ1	AZXSDDO1		2000			100		REE#	1900
3	02/01/2024	AXCD345	VFGT556	SDERR	AQ2	AZXSDDO2		300			20		REE#	280
4	03/01/2024	AXCD346	VFGT557	ASWER	AQ3	AZXSDDO3		1320			50		REE#	1270
5	04/01/2024	AXCD347	VFGT558	ASDFR	AQ4	AZXSDDO4		100			10		REE#	90
6	05/01/2024	AXCD348	VFGT559	AGGG	AQ5	AZXSDDO5		220			10		REE#	210
7	06/01/2024	AXCD349	VFGT560	ERRR	AQ6	AZXSDDO6		220			100		REE#	120
AQ

after

ABB.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	BRAND	FIRST	NAME	INPUT	SECOND	OUTPUT	NOTE
2	1	VBGTL100	HSD0001	AAB1	2000	SS1233	250	##1
3	2	VBGTL101	HSD0002	AAB2	30000	SS1234	150
4	3	VBGTL102	HSD0003	AAB3	4000	SS1235	120
5	4	VBGTL103	HSD0004	AAB4	3200	SS1236	130	##CV
6	5	VBGTL104	HSD0005	AAB5	1200	SS1237	140
7	6	VBGTL105	HSD0006	AAB6	1300	SS1238	150
8	7	VBGTL106	HSD0007	AAB7	1500	SS1239	160	##$SD
9	8	VBGTL107	HSD0008	AAB8	1600	SS1240	170
10	9	VBGTL108	HSD0009	AAB9	1700	SS1241	8
11	10	VBGTL109	HSD0010	AAB10	1800	SS1242	58
ss

ABB.xlsm
	A	B	C	D	E	F	G
1	DATE	BATCH NO	COMPANY NAME	IMPORT	EXPORT	REF NO	TOTAL
2	01/01/2024	VFGT555	AQ1	2000	100	REE#	1900
3	02/01/2024	VFGT556	AQ2	300	20	REE#	280
4	03/01/2024	VFGT557	AQ3	1320	50	REE#	1270
5	04/01/2024	VFGT558	AQ4	100	10	REE#	90
6	05/01/2024	VFGT559	AQ5	220	10	REE#	210
7	06/01/2024	VFGT560	AQ6	220	100	REE#	120
AQ

should keep formatting and delete unwanted columns.

 

[Joe4]

I have no idea what you posted, why there are two very different images for before and after (two very different formats), unless you are introducting new layers of complexity you did not mention before.

But you seemed to totally ignore what I requested in my last post (I was looking for a field mapping).
So I don't think there is anything I can really do with what you posted.

 

[Abdo]

why there are two very different images for before and after (two very different formats)

because the thread title contains arranging again .

Cancel merged columns and arrange structure across files in the folders

and I have ever mentioned in OP !

would arrange columns across files from A,C,E,H,M,N,K to A,B,C,D,E,F,G across sheets for each file

so normally you see change in location .
I see you don't understand my thread totally despite of I I gave you example!

So I don't think there is anything I can really do with what you posted.

it's not problem I will wait for others members to help or post in another forum If I don't getting solution.
anyway thank you for your time.