Shorter VBA version to do the same thing?

[mrexcelhelp123]

HI, I have the code I'm using now which works but was wondering if someone can help me condense it to do the same thing: THANKS!

Sub Modify_Instr_Tab()
 
Dim ws As Worksheet
 
'Assigns a due date to cell D10
Worksheets("Instructions").Range("D10").Select
ActiveCell.Formula = "5/28/2010"
 
'Assigns another due date to cell E10 if applicable
Worksheets("Instructions").Range("E10").Select
ActiveCell.Value = "NO UPDATE REQUIRED"
 
'Shades range A10:E10
Range("A10:E10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
 
For Each ws In Sheets(Array("Instructions"))
'Unshade filling for range A9: E9
With ws.Range("A9:E9")
.Interior.Pattern = xlNone
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
 
'Clear contents for range D9:E9
ws.Range("D9:E9").ClearContents 
End With
Next ws
End Sub

 

[Zack Barresse]

My code should work in all files, as long as that sheet is part of the active workbook.

And solve that by further qualifying the sheet listed in your With statement to include the workbook. I would recommend not keeping that as an assumption.

 

[wigi]

Hi Zack,

post #9 in this topic justifies it doing this way...

 

[mrexcelhelp123]

Hi Wigi,

So the only way to update the Instruction tab on all 40 workbooks containing the same tab is to activate a workbook one at a time and run the macro. In otherwords, run the macro 40 times?

 

[Norie]

Try this.

Option Explicit
 
Sub Modify_Instr_Tab()
Dim wb As Workbook
Dim wsInstr As Worksheet
 
    For Each wb In Workbooks

        With wb

            Set wsInstr = .Worksheets("Instructions")

            With wsInstr

                'Assigns due dates
                .Range("D10:E10") = Array("5/28/2010", "NO UPDATE REQUIRED")
 
                'Shades range A10:E10
                With .Range("A10:E10").Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 49407
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                
                'Unshade filling for range A9:E9
                With .Range("A9:E9").Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                
                .Range("D9:E9") = ""
            End With

        End With

    Next wb
    
End Sub

 

[mrexcelhelp123]

Thank you so much! This works perfectly!

 

[Zack Barresse]

Hi Zack,

post #9 in this topic justifies it doing this way...

Uh, actually, no it doesn't. Using the "assumption" method isn't justified just because you want it to work in the active workbook. If you want it to work in the active workbook, I still recommend you use that object "ActiveWorkbook", and not just leaving it off the child object (i.e. worksheet, et al). Explicitly defining the object(s) is always a good idea.

Norie has shown an example of looping through all workbooks.