selecting multiple sheets

[scarlettsr]

Hello,
I am a VBA newbie. I am trying to piece together some code to pull data from a specified range (L9 to L173-that does not change) in about 152 worksheets. I need to SKIP any worksheet in the 152 sheets that has the word "Total" in the Worksheet name (ex: Total 2121 Finance Dept). How do I write the code to actually SKIP the TOTAL sheets? Thanks for your help!

Dim WkSht As Integer
Dim R As Integer
R = 1
For WkSht = 1 To 152 'sub your number here
Dim Rng As Range
For Each Rng In Sheets(WkSht).Range("L9:L173")
If (Sheets(WkSht).Cells(Rng.Row, 1).Value Mod 100 <> 0) Then
If (Rng.Value) > 0 Then
Sheets("Summary").Select
' set all of the data

Cells(R, 1) = Sheets(WkSht).Cells(5, 2).Value ' Budget Unit from Spreadsheet
Cells(R, 2) = Sheets(WkSht).Cells(Rng.Cells.Row, 1).Value ' Expense Category
Cells(R, 3) = Year(Now) Mod 100
Cells(R, 4).Value = Rng.Value
R = R + 1

End If
End If
Next Rng
Next WkSht

 

[Weaver]

if instr(wksht.name,"total")=0 then 
'main code here
end if

Include

 option compare text

as the beginning of the module (before any subs/functions) to avoid case sensitivity

 

[JackDanIce]

Try:

Dim WkSht As Integer, R As Integer, i As Integer, YearMod as Integer

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

R = 1
YearMod = Year(Now) Mod 100

For WkSht = 1 To Worksheets.Count
    If InStr(UCase(Sheets(WkSht).Name), "TOTAL") = 0 Then
        With Sheets(WkShet)
            For i = 9 To 173
                If (.Range("A" & i) Mod 100 <> 0) Then
                    If .Range("L" & i) > 0 Then
                        With Sheets("Summary")
                            ' set all of the data
                            .Cells(R, 1) = Sheets(WkSht).Cells(5, 2).Value ' Budget Unit from Spreadsheet
                            .Cells(R, 2) = Sheets(WkSht).Cells(i, 1).Value ' Expense Category
                            .Cells(R, 3) = YearMod
                            .Cells(R, 4) = Sheets(WkSht).Range("L" & i)
                            R = R + 1
                        End With
                    End If
                End If
            Next i
        End With
    End If
Next WkSht

Sheets("Summary").Select

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

 

[scarlettsr]

Thanks for the posts!

Weaver -- I understand the instr line, but maybe I'm putting it in the wrong place because I'm getting a compile error, "Invalid Qualifier."

JackDanIce-I'm getting an over flow error on Line 16.

 

[JackDanIce]

Can you post your full code where you've used mine?

 

[scarlettsr]

Wow you are quick! Sure! The line with the "type mismatch" error is: If (.Range("A" & i) Mod 100 <> 0) T

Sub Macro1()
Dim WkSht As Integer
Dim R As Integer
Dim i As Integer
Dim YearMod As Integer

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

R = 1
YearMod = Year(Now) Mod 100

For WkSht = 1 To Worksheets.Count
If InStr(UCase(Sheets(WkSht).Name), "TOTAL") = 0 Then
With Sheets(WkSht)
For i = 9 To 173
If (.Range("A" & i) Mod 100 <> 0) Then
If .Range("L" & i) > 0 Then
With Sheets("Summary")
' set all of the data
.Cells(R, 1) = Sheets(WkSht).Cells(5, 2).Value ' Budget Unit from Spreadsheet
.Cells(R, 2) = Sheets(WkSht).Cells(i, 1).Value ' Expense Category
.Cells(R, 3) = YearMod
.Cells(R, 4) = Sheets(WkSht).Range("L" & i)
R = R + 1
End With
End If
End If
Next i
End With
End If
Next WkSht

Sheets("Summary").Select

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub

 

[JackDanIce]

Just at home watching South Park and practising VBA as have an interview for it tomorrow!

Try:

Sub Macro1()
Dim WkSht As Integer, R As Integer, i As Integer, YearMod As Integer

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

R = 1
YearMod = Year(Now) Mod 100

For WkSht = 1 To Worksheets.Count
    If InStr(UCase(Sheets(WkSht).Name), "TOTAL") = 0 Then
        With Sheets(WkSht)
            For i = 9 To 173
                If .Range("A" & i) Mod 100 <> 0 Then
                    If .Range("L" & i) > 0 Then
                        With Sheets("Summary")
                            ' set all of the data
                            .Cells(R, 1) = Sheets(WkSht).Cells(5, 2).Value ' Budget Unit from Spreadsheet
                            .Cells(R, 2) = Sheets(WkSht).Cells(i, 1).Value ' Expense Category
                            .Cells(R, 3) = YearMod
                            .Cells(R, 4) = Sheets(WkSht).Range("L" & i)
                            R = R + 1
                        End With
                    End If
                End If
            Next i
        End With
    End If
Next WkSht

Sheets("Summary").Select

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

(I tend to indent loops and stuff, just makes readability for me easier)

 

[scarlettsr]

Same "type mismatch" error on the same line. Or could it be that the data in my sheet is formatted as the wrong type? Though the code in your 2nd post looks the same as the first post? Maybe I missed something? Good luck on the interview!

 

[JackDanIce]

Send me a private message with your email on it, I'll have a look at your sheet now. It's working on my Excel, I'm using 2007

 

[scarlettsr]

I surely appreciate the offer but unfortunately it contains sensitive data so I can't share it. I'm also using Excel 2007. I'm going to create a "dummy" test file and try it. It has to be something with my workbook if it's running OK for you. I appreciate your time! I think it's close!