migsvill Send Message View User Items on April 16, 2014 02:49 PM
Here is a sample vba code.
What this code does is open every excel file in a given directory and copy the sheet2 of main.xlsm (your main excel file that will
contain this macro). Again sheet2 of this file will replace sheet2 of 100 files in your folder.
The code below will open one of 100 files one by one and will delete sheet2, excel will prompt you if you really delete the sheet2, then activate the main excel file then copy the sheet2 of this file to one of the 100 files just opened. After copying the sheet2, this file will be closed. Then another file will be opened.
1. Create main.xlsm
2. Make sheet2 of this file as the one that will replace the sheet2 of the 100 excel files
3. Copy the 2 macro below into the module of main.xlsm
Dim file_name As String
Dim Folder As String
Dim File As String
Folder = "c:\tpc\project 1"
File = Dir(Folder & "\*.xlsx")
'Counter how many files are opened and processed
numberOfFiles = 0
Do While File <> ""
file_name = Folder & "\" & File
MsgBox "name of file to be revised is " & File
'Replace sheet2 of the file just opened
numberOfFiles = numberOfFiles + 1
'Save the file before closing
File = Dir
MsgBox "done, there are " & numberOfFiles & " files processed!!"
Sub ReplaceSheetOf(fileNameTo As String)
'Delete sheet2 of this file
'Activate the main file and copy its sheet2 to filenameTo
MsgBox "sheet2 of main.xlsm was copied to " & fileNameTo
4. In the example macro above, You should put your 100 files in directory c:\tpc\project 1,
I repeat, project 1 is a sub-directory of the tpc directory under c drive,
or revise the macro depending on the current directory of your 100 files.
1. Before running the macro with your real files, it is better to run some sample files say 5 files first
to familiarize with this macro.
2. I provide some msgbox along the code so, it is better to comment this out if you will be running your real 100 files,
as otherwise you will be pressing these boxes num_file times.
3. Always make a backup of your original files.
4. There are comment in the code, have a look on it.
5. Do not put other excel files in the directory, because it will be opened get processed. Only those 100 files will be in that directory.
6. If you run a sample run just put 5 files in that directory.
7. A msgbox will display how many files are processed after the run.
8. I have tried this code for 3 files and it worked.
9. Do not include your main.xlsm in the the directory of your 100 files. Save it somewhere.
-- edited by ram2010 on Apr 16 2014, 10:41 PM