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]

As long as your sheet formats are consistent, you can actually get the code you need for the formatting of the sheet (removing merges, rearranging columns, etc) by turning on your Macro Recorder and record yourself doing performing those steps manually on one sheet, and then stopping the Macro Recorder. This will give you the code you need for that part.

Then, we can drop the code into some VBA code that loops through your folder, opens each workbook, makes the changes to each sheet, and then save and closes it.

Here is the "shell" for that code:

Sub MyFormattingFixMacro()

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

    Application

'   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
'***********YOU WOULD ACTUALLY HAVE YOUR CODE BELOW*******************
            MsgBox "Worksheet: " & ws.Name
        Next ws
'       Close workbook
        wb.Close SaveChanges:=True
'       Get next file name
        fl = Dir
    Loop
    
End Sub

So you would just replace this section of that code:

'***********YOU WOULD ACTUALLY HAVE YOUR CODE BELOW*******************
            MsgBox "Worksheet: " & ws.Name

with the code you recorded.

Just note two things:

1. You may want to clean-up your recorded code a bit to get rid of all the "Select" and "ActiveCell" references - the Macro Recorder is very literal, and records every motion, so some lines can be combined and/or eliminated. We can help you with this part if you post the code you recorded.

2. Be sure to update all the Range references in your recorded code with the "ws." prefix, which tells it which sheet to apply to.
So, if you have a reference like this:

Range("A1")

you would want to change it to this:

ws.Range("A1")

 

[Joe4]

Note that I think you should be able to umerge all the cells at once on each sheet by placing this line of code within the loop:

ws.Cells.UnMerge

 

[Abdo]

your code really works as what I want .
but still remaining another step after unmerged cells , I'm not sure if you read this

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.

 

[Joe4]

your code really works as what I want .
but still remaining another step after unmerged cells , I'm not sure if you read this

I think you must have missed this part of my reply (I gave you a little work to do on your own):

As long as your sheet formats are consistent, you can actually get the code you need for the formatting of the sheet (removing merges, rearranging columns, etc) by turning on your Macro Recorder and record yourself doing performing those steps manually on one sheet, and then stopping the Macro Recorder. This will give you the code you need for that part.

The Macro Recorder is a great little tool to use to get snippets of VBA code (even us "experts" sometimes use it!). I suggest you try it out, and see you do (it will be a good learning experience).
If you have any trouble incorporating your recorded code into the VBA code I provided for you, post the code you recorder here, and we can help you clean it up and insert it into the larger code block.

 

[Abdo]

The Macro Recorder is a great little tool to use to get snippets of VBA code (even us "experts" sometimes use it!). I suggest you try it out, and see you do (it will be a good learning experience).

do you mean select columns and copy to correction location by selection column when try recording macro ?

 

[Joe4]

do you mean select columns and copy to correction location by selection column when try recording macro ?

Simply turn on your Macro Recorder and record yourself moving the columns around the way you want on one of your sheets.
Then stop/turn off the Macro Recorder.
You will have recorder the VBA code you need to complete this task. So you should then be able to copy the copy of that recorded VBA code into your larger VBA procedure.

 

[Abdo]

sorry for delaying .
I'm not sure if I recorded correctly
here is what I got.

Range("A:A,C:C,H:H,M:M,N:N,K:K").Select
    Range("K1").Activate
    ActiveWindow.ScrollColumn = 1
    Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H").Select
    Range("H1").Activate

how arrange and put in your code?

 

[Joe4]

I think you missed the actually moving the columns around in that recorded code.
All that you have there just selects columns, it does not move them.
Did you record your actual moves?
If not, try again, and post ALL the code that you recorded.

 

[Abdo]

Did you record your actual moves?

no ! because excel prohibit me to copy multiple columns that at once after selecting !