MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hello again...need help with identical changes to 62 different workbooks...


Posted by Dustin on May 03, 2001 12:28 PM

Good day all,
Hopefully someone can help with this. I need to make some common changes to 62 different workbooks.
The changes are:
- insert a row and text on a specific tab, and
- copy a tab from an existing tab, rename the tab (same new tab name for each of the 62 workbooks), and
- unprotect a sheet, rewrite a formula, copy the formula to a bunch of cells on the sheet, re-protect the sheet
- save the workbook

I have almost no experience with VBA and macros, but am willing to try to avoid spending the next 3 hours doing this manually.

Thanks in advance for your help.


Posted by Barrie Davidson on May 03, 2001 2:22 PM

Hi Dustin. I think this code will work for you. It will allow you to select more than one file to make your changes to. You'll have to insert your own code to make the changes you want (in the loop, where it is commented: Insert code here). If you need any help with the code to do your changes let me know.

Dim File_Names As Variant
Dim File_count As Integer
Dim Counter As Integer


File_Names = Application.GetOpenFilename(, , , , True)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
File_count = UBound(File_Names)
Counter = 1
Do Until Counter > File_count
'Insert code here
Counter = Counter + 1
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True


Barrie

Posted by Dustin on May 04, 2001 12:16 PM

Thanks Barrie - I'll try it.

File_Names = Application.GetOpenFilename(, , , , True) Application.ScreenUpdating = False Application.DisplayAlerts = False File_count = UBound(File_Names) Counter = 1 Do Until Counter > File_count 'Insert code here Counter = Counter + 1 Loop Application.ScreenUpdating = True Application.DisplayAlerts = True