Change Multiple Worksheets at once

[CLEANCONDENSER]

Hello:
1) I have 5 Identical Excell 2000 Workbooks one for each person
2) Each Workbook has 50 Worksheets ,one for each city
3) The only difference between the 5 Workbooks is that each person has
their favorite Worksheets towards the front of their Workbook
4) The worksheets for each city are Identical in all 5 Workbooks
5) If required ,All five Workbooks can be opened at the same time on
the same computer

Question : How can I make changes in one work book in any Worksheet and
cause those exact changes to be made to the same worksheet
in the other 4 remaining Workbooks

 

[Dave Peterson]

If the worksheets were in the same workbook, you could group the worksheets by
clicking on the first worksheet tab and then ctl-clicking on the rest. Then
make your changes, then ungroup.

But since your worksheets are in different workbooks, you can't group the
worksheets.

But maybe you could open all 5 of the workbooks, move (not copy) the single
worksheets to a common workbook, group these 5 worksheets, make your changes and
then ungroup and move them back to their real home.

An alternative may be to use a macro.

You could record your macro when you make the changes to the first worksheet,
then play it back when you want to update the remaining 4 worksheets.

 

[Arnie]

Exactly what kind of changes to a worksheet are you talking about?

If you mean you would like to change a data value on one sheet and
have that reflected on all sheets you would take one approach.

If you mean you would like to add or delete rows or columns on a sheet
you would take another approach.

Also, who will be making the changes, you or the individual users?

Arnie

 

[Earl Kiosterud]

Mr. Clean,

I think it may well be better to contain all the data in one workbook, and
possibly in one sheet. Spreading data across worksheets precludes the use
of many Excel capabilities, and even more so when it's in separate
workbooks. I realize this may involve issues of access sensitivities. But
it's just a big pain to have data spread all over the place. I recommend
you consider this.


Earl Kiosterud
mvpearl omitthisword at verizon period net

 

[CLEANCONDENSER]

The kind of changes to a worksheet I would like to make are

changing a data value on one sheet and
have that reflected in all of the the other workbooks with the same
sheetname

The only difference between each users workbook is that they have the
worksheets that they always use at the beginning of their workbook

It would be good that when I change a value in a worksheet that I
always use ,it would automatically be updated in all of the other users
workbooks

 

[Dave Peterson]

You could use a macro based on this:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim wkbk As Workbook

For Each wkbk In Workbooks
With wkbk
Set wks = Nothing
On Error Resume Next
Set wks = .Worksheets("sheet1")
On Error GoTo 0

If wks Is Nothing Then
'do nothing
Else
wks.Range("b9").Value = "hi there"
End If
End With
Next wkbk

End Sub

I looked for a worksheet named sheet1 in each open workbook. Then I put the
value "hi there" in cell B9 of that sheet.

If you like this idea, open all the workbooks that need to be updated. Then
change the name (sheet1), the address (b9) and the value (hi there) to what you
need.

If you don't actually have access to the user's workbooks, you could send out a
workbook with the macro written the way you want. And tell them to open their
workbooks and run the macro.

(or if the workbook names are nice, you could actually open the workbook for
them.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Don't forget to save the workbook when you close them.