Solved: Changing multiple workbooks

[Sir Babydum GBE]

Hi

I have some sheets that I?m not allowed to put any macros in, so I need to run code from a master.

My Master workbook will be called "Master Workbook" (I was feeling particularly creative when I thought of that)...

The master contains 3 sheets:

1. "Copy Formats"
2. "Copy Formulas"
3. "Validation"

There are several slave workbooks that are sent out for others to update ? but we are sometimes needing to change the formulae and conditional formatting of the sheets as our needs evolve.

So, can someone help me with the following please?:

• The master workbook will look for any workbooks open whose filenames? begin with the word "slave"
• In each "slave" book the macro will unprotect the workbook using "myPassword" and unhide the sheets called "Main" and "Validation"
• On all sheets except "validation" (different books will have different sheet names) the formatting from Master?s "Copy Formats" sheet will be applied to the entire worksheet areas (A1: IV65536) but only the formats
• Again, on all sheets except "validation" the formatting from Master?s "Copy Formulas" sheet (columns O through Z) will be applied columns O through Z
• The info on the Master?s "Validation" sheet will be copied to the Slave?s "Validation" sheet. (all cells again)
• Finally, sheets "Main" and "Validation will be hidden, and the workbook will be protected with "myPassword"

Had i been allowed to use macros in the Slave sheets this would have been easy for me ? but I?m not good at looping through un-named workbooks, so I need help.

Thanks in advance

[Bob Phillips]

Hi

I have some sheets that I?m not allowed to put any macros in, so I need to run code from a master.

My Master workbook will be called "Master Workbook" (I was feeling particularly creative when I thought of that)...

The master contains 3 sheets:

1. "Copy Formats"
2. "Copy Formulas"
3. "Validation"

There are several slave workbooks that are sent out for others to update ? but we are sometimes needing to change the formulae and conditional formatting of the sheets as our needs evolve.

So, can someone help me with the following please?:

• The master workbook will look for any workbooks open whose filenames? begin with the word "slave"
• In each "slave" book the macro will unprotect the workbook using "myPassword" and unhide the sheets called "Main" and "Validation"
• On all sheets except "validation" (different books will have different sheet names) the formatting from Master?s "Copy Formats" sheet will be applied to the entire worksheet areas (A1: IV65536) but only the formats
• Again, on all sheets except "validation" the formatting from Master?s "Copy Formulas" sheet (columns O through Z) will be applied columns O through Z
• The info on the Master?s "Validation" sheet will be copied to the Slave?s "Validation" sheet. (all cells again)
• Finally, sheets "Main" and "Validation will be hidden, and the workbook will be protected with "myPassword"

Had i been allowed to use macros in the Slave sheets this would have been easy for me ? but I?m not good at looping through un-named workbooks, so I need help.

Thanks in advance

The loop is easy


[VBA]
Dim wb As Workbook

For Each wb In Application.Workbooks
If wb.Name Like "Slave*" Then
Debug.Print wb.Name
'do your stuff
End If
Next wb
[/VBA]

[Sir Babydum GBE]

Thanks!

How do I select all sheets bar "validation" on the slave w/bs?

[Bob Phillips]

One way

[VBA]
Sub BabyD()
Dim wb As Workbook
Dim Sh As Worksheet
Dim cSheets As Long
Dim idx As Long
Dim ary

For Each wb In Application.Workbooks
If wb.Name Like "Book*" Then
cSheets = wb.Worksheets.Count
If SheetExists("Validation", wb) Then
cSheets = cSheets - 1
End If
ReDim ary(1 To cSheets)
For Each Sh In wb.Worksheets
If Sh.Name <> "Validation" Then
idx = idx + 1
ary(idx) = Sh.Name
End If
Next Sh
ReDim Preserve ary(cSheets)
Sheets(ary).Select
End If
Next wb
End Sub

'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function

[/VBA]

[Sir Babydum GBE]

XLD,

For what its worth. You really do make my life easier, and help me to hit deadlines. It's very much appreciated Sir.

[Bob Phillips]

XLD,

For what its worth. You really do make my life easier, and help me to hit deadlines. It's very much appreciated Sir.

As long as I am duly accredited . A legend in my own time within BG

[Bob Phillips]

BD,

I have a logic error in the first sub that can cxause a problem. Use

[VBA]
Sub BabyD()
Dim wb As Workbook
Dim Sh As Worksheet
Dim cSheets As Long
Dim idx As Long
Dim ary

For Each wb In Application.Workbooks
If wb.Name Like "Book*" Then
idx = 0
cSheets = wb.Worksheets.Count
If SheetExists("Validation", wb) Then
cSheets = cSheets - 1
End If
ReDim ary(1 To cSheets)
For Each Sh In wb.Worksheets
If Sh.Name <> "Validation" Then
idx = idx + 1
ary(idx) = Sh.Name
End If
Next Sh
wb.Activate
Sheets(ary).Select
End If
Next wb
End Sub[/VBA]