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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..
 

francisp

New Member
Joined
Apr 6, 2003
Messages
41
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
 

ctbytc

New Member
Joined
Oct 19, 2006
Messages
13
each spreadsheet is a file named differently the business unit - I can't select multiples??
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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?
 

ctbytc

New Member
Joined
Oct 19, 2006
Messages
13
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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:

ctbytc

New Member
Joined
Oct 19, 2006
Messages
13
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")
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..
 

Watch MrExcel Video

Forum statistics

Threads
1,123,382
Messages
5,601,309
Members
414,441
Latest member
KellyTheKid

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
Top