Loop Execution--Questions

Hey guys,

Here is some background about me and this project I am working on. I am a college grad that just recieved his first position. My job is to go through this workbook that is broken from the database and begin rewriting some programming to make it automated again. I've reached my first stump, I hope you guys can help!


The spreadsheet consist of the previous year's last quarter, and the current year's 4 quarters. The macros will take whatever is in the last column (Previous year's last quarter), copy it, and paste it into the correct column. Next, it would clear everything else. There is 4 different worksheets that it will automate through. For some reason, I keep getting inconsistant results with this macros and I cannot figure it out.

Option Explicit

Sub resetAll()
Dim copyStr As String
Dim pasteStr As String
Dim i As Integer
Dim rng As String
Dim startRng As String
Dim endRng As String
'Dim pos1 As Integer
Dim pos2 As Integer
Dim endRow As Range

Dim startRow As Integer
Dim kpiWS As Worksheet
Dim flg As Integer
Dim msg As String

Application.ScreenUpdating = False


msg = MsgBox("This action clears all the columns EXCEPT last QDec column. Do you still want to reset?", vbYesNoCancel + vbExclamation, "Reset Warning")

If msg = vbYes Then
    copyStr = ActiveWorkbook.Worksheets("Config").Range("H5").Value 'S:U
    pasteStr = ActiveWorkbook.Worksheets("Config").Range("G5").Value 'G:I
     
    'pos1 = InStr(pasteStr, ":")
    pos2 = InStr(copyStr, ":")

    startRng = ActiveWorkbook.Worksheets("Config").Range("I5").Value
    endRng = Mid(copyStr, pos2 + 1)


    For i = 2 To 5 '####### i value hardcoded - 4 KPI regions
        rng = "L" & i
        'get the worksheet name to enter the data
        Set kpiWS = ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets("Config").Range(rng).Value)
    
        'copy from QDEC and copy to QDEC...
        With kpiWS
            .Activate
            .Columns(copyStr).Copy
            .Columns(pasteStr).Select
            .Paste
        End With
    
        startRow = 4
    
        'delete the rest
        With kpiWS
            .Activate
            Set endRow = .Columns(1).find(What:="Title", After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
            If Not endRow Is Nothing Then
                flg = endRow.Row
                Do
                    .Range(startRng & startRow & ":" & endRng & endRow.Row).ClearContents
                    startRow = endRow.Row + 4
            
                    Set endRow = .Columns(1).FindNext(endRow)
                Loop Until flg = endRow.Row
            
            End If
        
        
        End With

    Next i


    Call setColumnHead  'sets the column headers by quarters and QCRs
    
    MsgBox "Reset Complete!!"

End If

    ActiveWorkbook.Worksheets("MainSheet").Activate
    Application.ScreenUpdating = True

    
End Sub

Any ideas? questions?
Thanks!