PDA

View Full Version : Solved: Changing multiple workbooks



Sir Babydum GBE
10-24-2005, 05:06 AM
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:


"Copy Formats"
"Copy Formulas"
"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
10-24-2005, 05:35 AM
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:



"Copy Formats"
"Copy Formulas"
"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



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

Sir Babydum GBE
10-24-2005, 05:51 AM
Thanks!

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

Bob Phillips
10-24-2005, 06:07 AM
One way


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

Sir Babydum GBE
10-24-2005, 06:14 AM
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
10-24-2005, 06:16 AM
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 http://vbaexpress.com/forum/images/smilies/001.gif. A legend in my own time within BG http://vbaexpress.com/forum/images/smilies/023.gif

Bob Phillips
10-24-2005, 06:26 AM
BD,

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


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