For Loop Not Executing

[OilEconomist]

Thanks in advance for any suggestions and I will post feedback!

The code should do copy and Paste Transpose seven times, but it only does it once. Any idea why? Seems like it's not running the Loop and the issue must be with If Then Statements.

The only part of my code that seems to get executed is for the following:

If Cells(i, 12) = "Grand Total" Then
                RowPasteCF = 4
                StartRow = i + 4

Where the following is done:

'Gross Volumes
                'Copying
                    With Sheets("Data")
                        .Range(.Cells(StartRow, 2), .Cells(LRPHDW, 4)).Copy
                    End With
 
                'Pasting
                    Sheets("Cash.Flow").Select
                    Worksheets("Cash.Flow").Range("B" & RowPasteCF).Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=True

Entire Code:

Sub TransposeP()
   
    'Dimensioning
        Dim LastRow As Long  
        Dim StartRow As Long
        Dim LRPHDW As Long
        Dim Category As String
   
    'Turn OFf Screen Updating
        Application.ScreenUpdating = False
   
    'Turn off Automatic Calculation & Calculate
        Application.Calculation = xlManual
        Calculate
   
    'Expands the Cash Flow Tab
        Worksheets("Cash.Flow").Activate
            ActiveSheet.Outline.ShowLevels RowLevels:=8, ColumnLevels:=8
           
    'Pasting the Total Values
 
   
        'Calculate and Turn on Automatic Calculation
            Calculate
            Application.Calculation = xlAutomatic
       
        'Turn On Screen Updating
            Application.ScreenUpdating = True
           
        'Placing the cursor
            Sheets("Cash.Flow").Activate
            Sheets("Cash.Flow").Range("B4").Select
   
    'Activate the "Data" Tab
        Sheets("Data").Activate
   
        Cells(1, 12).Value = "Grand Total"
 
    'Find the Last Row of all the data
        LastRow = Cells.Find(What:="*", after:=Range("A1"), LookAt:=xlPart, _
            LookIn:=xlFormulas, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False).Row
 
   
    'Loop through the data and find the different Reserve Categories to Paste
        For i = 1 To LastRow
 
            If Cells(i, 12) = "Grand Total" Then
                RowPasteCF = 4
                StartRow = i + 4
               
            ElseIf Cells(i, 12) = "Cat2" Then
                RowPasteCF = 44
                StartRow = i + 4
               
            ElseIf Cells(i, 12) = "Cat3" Then
                RowPasteCF = 84
                StartRow = i + 4
               
            ElseIf Cells(i, 12) = "Cat4" Then
                RowPasteCF = 164
                StartRow = i + 4
               
            ElseIf Cells(i, 12) = "Cat5" Then
                RowPasteCF = 244
                StartRow = i + 4
                       
            ElseIf Cells(i, 12) = "Cat6" Then
                RowPasteCF = 324
                StartRow = i + 4
                   
            ElseIf Cells(i, 12) = "Cat7" Then
                RowPasteCF = 404
                StartRow = i + 4
           
            End If
           
   
    Category = Cells(i, 12)
   
    If Category = "Grand Total" _
        Or Category = "Cat2" _
        Or Category = "Cat3" _
        Or Category = "Cat4" _
        Or Category = "Cat5" _
        Or Category = "Cat6" _
        Or Category = "Cat7" _
    Then
   
        'Copying and Pasting The Data
            'Find the last row (LRPHDW)(this will be applicable for all data for all categories)
                LRPHDW = Range("A" & StartRow).End(xlDown).Offset(1).Row
                LRPHDW = LRPHDW - 2
           
            'Gross Volumes
                'Copying
                    With Sheets("Data")
                        .Range(.Cells(StartRow, 2), .Cells(LRPHDW, 4)).Copy
                    End With
 
                'Pasting
                    Sheets("Cash.Flow").Select
                    Worksheets("Cash.Flow").Range("B" & RowPasteCF).Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=True
           
        End If
           
    Next i
 
    'Ending the Loop
 
End Sub

 

[OilEconomist]

Yikes, after messing with this forever I decided to post my question here, and then I finally figured it out after I posted.

I added in some additional code (i.e. Message Box) and saw that the Grand Total is the only Category and finally figured out that I was not reactivating the Data Sheet.

Basically I have figured this one out. I changed:

For i = 1 To LastRow


            If Cells(i, 12).Value = "Grand Total" Then
                RowPasteCF = 4
                StartRow = i + 4

to the following:

For i = 1 To LastRow
            Sheets("Data").Activate
            If Cells(i, 12).Value = "Grand Total" Then
                RowPasteCF = 4
                StartRow = i + 4