Macro

[DOF2001]

Hi!

i'm trying to make this macro run in many sheet within a woorkbook, but for some reason it only run it to the sheet i am in. any ideas why is this happening, here is the code.

Sub Test_ToAllSheets()

Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
Select Case ws.Name
Case " First", "Second"
Case Else
With ws
Range("Q694:DJ694") = Range("Q1:DJ1").Value
Dim b As Range, rng1
Set rng1 = Range("Q693:DJ693")
For Each b In rng1
If b = "0" Then
b = b.Offset(1, 0)
End If
Next b
'Rows("694:694").Select
'Selection.Delete Shift:=xlUp
End With
End Select
Next ws


End Sub

i want to run it to all sheets in the workbook besides the fist 3.

Thanks

 

[Norie]

Try this.

Sub Test_ToAllSheets()

Dim wb As Workbook
Dim ws As Worksheet
Dim b As Range, rng1 As Range

    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        Select Case ws.Name
        Case " First", "Second"
            ' do nothing
        Case Else
            With ws
                .Range("Q694:DJ694") = .Range("Q1:DJ1").Value
        
                Set rng1 = .Range("Q693:DJ693")
                For Each b In rng1
                    If b = "0" Then
                        b = b.Offset(1, 0)
                    End If
                Next b
        
            End With
        End Select
    Next ws


End Sub

 

[DOF2001]

Thanks a lot Norie, it work out perfectly, just one question so i can understand, why did you move the declaration on hte range to the beggining, is this so this is public so it can be used by all the sheets????????????

 

[Norie]

I moved the declarations because, to me anyway, it doesn't make any sense to have declarations in the middle of the code.

Others will probably disagree with that and there may be valid reasons for declaring in the code but of the top of my head I can't think of any.

And no, it doesn't change the scope of the variables.

 

[Zack Barresse]

Keeping the dimensioning at the top is just preference. Those lines are only compiled and not ran (traditionally in the sense). The only difference would be is if your variables were declared outside of your sub routine. That would make them (by default) public, unless otherwise specified. Here are some examples ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Compare</SPAN> <SPAN style="color:#00007F">Text</SPAN>

Global <SPAN style="color:#00007F">Const</SPAN> NL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = vbNewLine
Global <SPAN style="color:#00007F">Const</SPAN> APPNAME <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "My Application"
<SPAN style="color:#00007F">Public</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Public</SPAN> rngTest <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Public</SPAN> strFilePath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Private</SPAN> lngCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
<SPAN style="color:#00007F">Private</SPAN> blnCreated <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Blahblahblah()
    
    <SPAN style="color:#007F00">'Can be used in any module..</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws = Worksheets("Sheet1")
    
    <SPAN style="color:#007F00">'Can be used in any module..</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngTest = ws.Range("A1:B2")
    
    <SPAN style="color:#007F00">'Can be used in any module..</SPAN>
    strFilePath = rngTest(1, 1).Value
    
    <SPAN style="color:#007F00">'Can be used in ONLY this module..</SPAN>
    lngCount = rngTest.Cells.Count
    
    <SPAN style="color:#007F00">'Can be used in ONLY this module..</SPAN>
    blnCreated = (lngCount = rngTest.Cells.Count)
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>



The reason your code didn't work for anything except the active sheet was because of lines like this ..

 Range("Q694:DJ694") = Range("Q1:DJ1").Value
Set rng1 = Range("Q693:DJ693")

All of those ranges are unqualified. Meaning you define a worksheet (ws) but don't specify those ranges to be on that sheet. With nothing there, Excel will always assume it's the activesheet. If no activesheet is there, your code will fail with a range class failure. If you wanted to fully qualify your code, just add your worksheet variable to the ranges...

 ws.Range("Q694:DJ694") = ws.Range("Q1:DJ1").Value
Set rng1 = ws.Range("Q693:DJ693")

That makes all the difference in the world. Do the same for workbooks if you are working with more than one workbook via code. Or use With/End With statements for them (which, if used correctly, may even speed up your code a bit).

 

[DOF2001]

Thanks for all teh info.

One more thing, i am try to combine few small simmilar macros into one, i have most of them to work besides the very last one, it has simmilar characteristics as the other one, here is the code

Sub Curves_Transpose_Part2_ToAllSheets56()

Dim wb As Workbook
Dim ws As Worksheet
Dim b As Range, rng1
Dim a2 As Range, rng3

Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
Select Case ws.Name
Case " Deleted Data", "GRAPH"
Case Else
With ws
Set rng3 = ws.Range("Q694:DJ694")
For Each a2 In rng3
If a2.Offset(-1, 0) > " " Then
a2.FormulaR1C1 = "=R[-1]C+RC[-1]"
a2.Offset(1, 0).FormulaR1C1 = "=R[-1]C/R694C114"
End If
Next a2
Rows("694:694").Select
Selection.NumberFormat = "0"
Rows("695:695").Select
Selection.NumberFormat = "0.0%"

End With
End Select
Next ws


End Sub

same as before it only aplies to the selected sheet, i made the cjhange on the range but still not aplying to all the sheets.

Thanks

 

[Norie]

That's because again you have unqualified references, to be specific Rows.

Sub Curves_Transpose_Part2_ToAllSheets56()

Dim wb As Workbook
Dim ws As Worksheet
Dim b As Range, rng1
Dim a2 As Range, rng3

    Set wb = ActiveWorkbook
    For Each ws In wb.Worksheets
        With ws
            Select Case .Name
                Case " Deleted Data", "GRAPH"
                    ' do nothing
            Case Else
            
            Set rng3 = .Range("Q694:DJ694")
                For Each a2 In rng3
                    If a2.Offset(-1, 0) > " " Then
                        a2.FormulaR1C1 = "=R[-1]C+RC[-1]"
                        a2.Offset(1, 0).FormulaR1C1 = "=R[-1]C/R694C114"
                    End If
            Next a2
            
            .Rows("695:695").NumberFormat = "0.0%"
            
           
            End Select
         End With
    Next ws


End Sub

 

[DOF2001]

Thanks Norie, sorry to disturbe you to much but i try to understand so i don't make the same mistakes over and over, can you please elaborate a little more, point out which are the unqualified references and why.

Thanks a lot

 

[Norie]

Like I said it was the Rows part that was unqualified.

All you needed to do would, if that section of code was within the With...End With structure, is precede them with the . dot qualifier.

.Rows("695:695").NumberFormat = "0.0%"

If the code was outside the With.,.End With you would add the worksheet reference.

ws.Rows("695:695").NumberFormat = "0.0%"

 

[DOF2001]

Thanks!!!!!!!!!!