changing 503 spreadsheets at once

ctbytc

New Member
Joined
Oct 19, 2006
Messages
13
I have been handed 503 spreadsheets containing system data for multiple business units in my company. Each spreadsheet is identical in structure (same column headings) but just varies in the number of rows of data

I’ve now been asked to add (4) additional columns to each of the 503 spreadsheets. The (4) new columns are to be called:

“Class”
“OpCat Template ID”
“Agent”
“Last Occurrence”

And added in columns L, M, N, and O.

Is there a way to do this automatically instead of having to manually enter this data 503x??

Thanks

Cris
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Welcome to the board...

click the tab for the sheet furthest to the left.
Hold down your SHIFT Key
Then using the little scroll buttons on the bottom left, go to and click the Tab for the sheet furthest to the right.

You'll see now all sheets are selected.
type in the values into the approprioate cells, all sheets will be updated simultaneousely..

Hope that helps..
 
Upvote 0
Assuming you are using Excel 2003.

Right click on the sheet name i.e. the sheet name which appears at the bottom of the file on the left side.

Select All Sheets.

This will group all the sheets.

Type in Cell L1 "Class"
Type in Cell M1 “OpCat Template ID”
Type in Cell N1 “Agent”
Type in Cell O1 “Last Occurrence”.

Now right click on the Sheet name, Select the first option..Ungroup sheets.

All your sheets are now updated with the above names in Cell L1, M1, N1 & O1

Hope this helps

Regards
Frank
 
Upvote 0
each spreadsheet is a file named differently the business unit - I can't select multiples??
 
Upvote 0
Ok, so you're talkinga bout 503 workBOOKs, not WorkSHEETs...

Are they all in the same folder?, and is there only 1 sheet in each book, or multiple?
 
Upvote 0
sorry, yes, 503 workbooks, all in the same directory on a server in our datacenter

yes, there is only one sheet (sheet 1) that contains the data

the other 2 tabs (sheet 2 and 3) are blank
 
Upvote 0
You may want to test this on a seperate set of files before running on the real files...But it should work

Also, are the files you're updating the ONLY files in that directory? This will make the updates to ALL files with .xls extention in that directory. It can be modified to do only certain files, but would need some method of checking the file name for criteria...

Rich (BB code):
Sub UpdateFiles()
Dim MyPath As String
Dim MyFile As String
Dim wb As Workbook
 
MyPath = "\\Path\"
 
MyFile = Dir(MyPath & "*.xls")
 
Do While MyFile <> ""
    Set wb = Workbooks.Open(MyPath & MyFile)
    With wb.Sheets("Sheet1")
        .Range("L1").Value = "Class"
        .Range("M1").Value = "OpCat Template ID"
        .Range("N1").Value = "Agent"
        .Range("O1").Value = "Last Occurrence"
    End With
    wb.Save
    wb.Close
    MyFile = Dir
Loop
End Sub
 
Last edited:
Upvote 0
jonmo1

where does this code go? I'm placing it in module 1, but getting a debug error "52":

MyPath = "\\D:\company name\EMM Program Office\excel test\"

MyFile = Dir(MyPath & "*.xls")
 
Upvote 0
I'm guessing D is a mapped network drive ? it's best to not use the mapped drive. Because sometimes the drive might not be mapped to D...

So it should be

MyPath = "\\NetworkServerName\ShareName\company name\EMM Program Office\excel test\"

You can get that info looking at the mapped drive in my computer..
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top