Edit Multiple Excels To Match Master Template (I.E. Table Headers, Sheet Name)

[ub3rm0nk3y]

I need to edit about 2,000 excels to match my Master Template. The Master Template contains adds extra columns, removes some columns, and a rename of the sheets. The 2,000 excels should be uniform. Would there be any method of automating this through a VBA? Or is this not possible.
I can provide any additional details if needed. Thank you for taking a look.

[NeedForExcel]

Please clarify more.. I am not able to understand what the desired output is..

[ub3rm0nk3y]

I apologize. I have around 2,000 excels in which we use to log data. We're updating the template of these logs. Without any excel magic, I would need to enter each log and change them to match our Template.

Sheet 1 (Summation):
Swap Column H and I.
Copy & Paste A1:J1 from the Master Template (Sheet 1) to the other excel.

Sheet 2:
Clear column C
Delete Column I
Clear Column K (or delete) either or
Copy & Paste A1:J1 from the Master Template (Sheet 2) to the other excel.

Rename Sheets
"Summation" to "INPUT (PROCESSING)"
"Production" to "OUTPUT (PRODUCTION)"

I've never tried or even thought of anything like this so I don't even know if it's possible. I figure I might be able to come up with a macro since it's the same steps over and over but as far as making a script to open every excel and execute the macro, I wouldn't know where to start.

What I'm picturing, sorry if it's absurd, is opening a macro excel. Running a script that says opening 1st workbook, perform the macro to edit the excel, save and close the excel, go to the next excel. Do you believe this is possible?

[NeedForExcel]

Please create a Module in in Example Workbook and paste the following code.

Please change the Path of the Master sheet in the code -

Sub ChangeFormats()

    Dim Master As Workbook
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets("Summation").Activate

    Columns("H:H").Select
    Selection.ListObject.ListColumns.Add Position:=8
    Columns("J:J").Select
    Selection.Copy
    Columns("H:H").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("J:J").Select
    Selection.ListObject.ListColumns(10).Delete
    Range("H2").Select
    
    Range("A1:J1").Copy
    
    Set Master = Workbooks.Open("C:\Users\Admin\Desktop\Template.xls")
    
    ActiveWorkbook.Sheets("INPUT (PROCESSING)").Range("A1").PasteSpecial
    
    Master.Save
    Master.Close
    
    ThisWorkbook.Sheets("Production").Activate
    Columns("H:H").Select
    Selection.ClearContents
    Columns("I:I").Delete
    Columns("J:J").ClearContents
    
    ThisWorkbook.Sheets("Summation").Name = "INPUT (PROCESSING)"
    ThisWorkbook.Sheets("Production").Name = "OUTPUT (PRODUCTION)"
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    
End Sub

And please let me know if this is what you want??


Thank You,

Deep

[ub3rm0nk3y]

Sheet 2:
Column C did not clear
Row 1 did not copy from the Template. I'm sorry. Not sure if I specified this correctly.
Everything else worked great.

[NeedForExcel]

Yes it is very well possible! However I am still unclear on what you are trying to say.

Let me work around it and give you a solution!!

[NeedForExcel]

Please see this one.

The code isnt really best written, however I was slightly in a hurry..

but will do what you want it to..

Sub ChangeFormats()

    Dim Master As Workbook
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ThisWorkbook.Sheets("Summation").Activate

    Columns("H:H").Select
    Selection.ListObject.ListColumns.Add Position:=8
    Columns("J:J").Select
    Selection.Copy
    Columns("H:H").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Columns("J:J").Select
    Selection.ListObject.ListColumns(10).Delete
    Range("H2").Select
    
    Range("A1:J1").Copy
    
    Set Master = Workbooks.Open("C:\Users\Admin\Desktop\Template.xls")
    
    ActiveWorkbook.Sheets("INPUT (PROCESSING)").Range("A1").PasteSpecial
    
    Master.Save
    Master.Close
    
    ThisWorkbook.Sheets("Production").Activate
    Columns("C:C").Select
    Selection.ClearContents
    Columns("I:I").Delete
    Columns("J:J").ClearContents
    
    Range("A1:J1").Copy
    
    Set Master = Workbooks.Open("C:\Users\Admin\Desktop\Template.xls")
    
    ActiveWorkbook.Sheets("OUTPUT (PRODUCTION)").Range("A1").PasteSpecial
    
    Master.Save
    Master.Close
    
    ThisWorkbook.Sheets("Summation").Name = "INPUT (PROCESSING)"
    ThisWorkbook.Sheets("Production").Name = "OUTPUT (PRODUCTION)"
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    
End Sub

Please let me know if it worked fine!!

Thank You,

Deep

[ub3rm0nk3y]

I was able to figure out what I needed by disseminating your code and one or two more out there. This is what I came up with which works great.

I run it in my Template workbook and it opens excels in the specified folder, performs the operations I want it to, saves, closes, and goes on to the next.

Thanks for your help.

Sub LookNoHands()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim MyFile As String
Dim Master As Workbook
Dim wbk As Workbook

Set Master = ThisWorkbook
    
MyPath = "C:\"
MyFile = Dir(MyPath)
Do While MyFile <> ""
If MyFile Like "*.xls" Then
Workbooks.Open MyPath & MyFile

Set wbk = ActiveWorkbook

``VBA I want to loop with every excel

wbk.Save
wbk.Close

End If
MyFile = Dir
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub