VBA Macro Loop Through worksheet

[LearnVBA83]

Is there a way to write a macro that will loop through each worksheet in workbook and copy the name of the worksheet into that worksheet in cell A1 to A200?

 

[skillilea]

Play with it. It's most likely adding the contingent in before deleting it.

 

[LearnVBA83]

hmmm... it looks like the sum formula is covering the whole area... I was walking through it using F8 and saw this. Looks like it's totally the whole area.

 

[LearnVBA83]

Oh My! I think i fixed it! This is unreal! I changed ":P" & expRow to ":E" & expRow and it seems to work perfect!

 ' if we have both, put sum, fill across
                    If monRow > 0 And expRow > 0 Then
                        Set fillRangeRow = ws.Range(ws.Cells(expRow, 5), ws.Cells(expRow, 16))
                        fillRangeRow.Formula = "=SUM(E" & monRow & ":E" & expRow - 1 & ")"

                        monRow = 0
                        expRow = 0
                        hasGLNum = 0
                    End If

 

[skillilea]

You rock! Look your a programmer Well done!

 

[LearnVBA83]

Ok I think my eyes are burry or i'm going crazy! In this part of the code you're adding contingent value .. ... deleting contingent.... then a few more code steps.... then copy contingent to master file. My question is: If you deleted the contingent before the copy contingent to master file code how is it working? It's 100% working but i can't figure out how b/c you deleted the contingent in the code before it... What am i missing is it storing the contingent lines hidden somewhere?

What i'm trying to do is figure out how to exclude the contingent lines that have Jan-Dec all 0 or blank... I've not been able to figure it out as easy as it was for the expense lines.

' add contingent values
                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, "B").Value = "contingent" Then
                        For x = 5 To 16
                            If IsNumeric(ws.Cells(i, x).Value) Then
                                sumMonths(x - 4) = sumMonths(x - 4) + ws.Cells(i, x).Value
                            End If
                        Next
                    End If
                Next

                ' delete contingent
                For i = ws.UsedRange.Rows.Count To 1 Step -1
                    If InStr(1, ws.Range("D" & i).Value, "Contingent", vbTextCompare) > 0 Then
                       ws.Cells(i, 1).EntireRow.Delete
                    End If
                Next
    
                ' overwrite with just values
                With .UsedRange
                    .Value = .Value
                End With

                ' copy expenses to master temp
                lr = mstTemp.Cells(.Rows.Count, "A").End(xlUp).Row + 1
                mstTemp.Columns("A:A").NumberFormat = "@"
                For i = 1 To .UsedRange.Rows.Count
                    If InStr(1, ws.Range("D" & i).Value, "Expense", vbTextCompare) > 0 And ws.Range("C" & i).Value <> "" _
                    And ws.Range("Q" & i).Value <> 0 Then
                        Set fillRangeRow = ws.Range(ws.Cells(i, 1), ws.Cells(i, 16))
                        Set rng = mstTemp.Cells(lr, 1)
                        Set rng = rng.Resize(1, fillRangeRow.Columns.Count)
                        rng.Value = fillRangeRow.Value
    
                        lr = lr + 1
                    End If
                Next

                ' copy contingent to master file
                With mstTemp
                    lr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                    .Range("A" & lr).Value = numericPart
                    .Range("C" & lr).Value = Contingent
                    .Range("D" & lr).Value = "Contingent"
                    
                    For x = 1 To 12
                       .Cells(lr, x + 4).Value = sumMonths(x)
                    Next
                End With
            End With

 

[skillilea]

before it deletes the contingent lines it stores all of them in an array called sumMonths.
It deletes
then I can put the array anywhere I want later in the code

                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, "B").Value = "contingent" Then
                        For x = 5 To 16
' you would have to do a test here to see if you wanted to keep the row
                            If IsNumeric(ws.Cells(i, x).Value) Then
                                sumMonths(x - 4) = sumMonths(x - 4) + ws.Cells(i, x).Value
                            End If
                        Next
                    End If
                Next

 

[LearnVBA83]

I tried this one but it doesn't seem to be working

  ' add contingent values
                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, "B").Value = "contingent" And .Cells(i, "Q").Value <> 0 Then
                        For x = 5 To 16
                            If IsNumeric(ws.Cells(i, x).Value) Then
                                sumMonths(x - 4) = sumMonths(x - 4) + ws.Cells(i, x).Value
                            End If
                        Next
                    End If
                Next

 

[LearnVBA83]

Tried this one too. I feel like i'm close but still giving me the same results

' add contingent values
                For i = 1 To .UsedRange.Rows.Count
                    If .Cells(i, "B").Value = "contingent" Then
                        For x = 5 To 16
                            If IsNumeric(ws.Cells(i, x).Value) <> 0 Then
                                sumMonths(x - 4) = sumMonths(x - 4) + ws.Cells(i, x).Value
                            End If
                        Next
                    End If
                Next

 

[skillilea]

What i'm trying to do is figure out how to exclude the contingent lines that have Jan-Dec all 0 or blank... I've not been able to figure it out as easy as it was for the expense lines.

If you don't get it. I'll look at it this weekend.

 

[LearnVBA83]

I'm will keep plugging away at it as well. I got pulled into what i hate most!!!! Powerpoint!