Can someone help fix my VBA code?

Question

I have a VBA macro that imports daily call statistics from an exported Excel file into a master workbook. The macro mostly works, but I’m running into inconsistent behaviour and want help fixing and tightening it up.

What the macro is supposed to do

• Prompt the user to select an exported Excel file.

Read totals from a sheet called “Call Logger” by searching column G for:

Total Recorded Logs

  `Total RPCs`

  
     `Total Converted`

     
     Write those values into an **“OB & RPC”** sheet in the target workbook, matched by today’s date (formatted as text, e.g. `Mon 5th Jan 2026`)

     
     Ask the user if inbound calls should be added to the RPC count and optionally add them

     
     Write the final RPC count into a **“CPC & CDC”** sheet

     
     If a sheet called **“P2Ps Set”** exists in the source file, copy all rows into a **“P2P”** sheet in the target workbook, appending to the next empty row

     
     Close the source workbook without saving and show a summary message
```**The problems I’m seeing**

Date matching sometimes fails even though the date text exists in column A

Data occasionally doesn’t write even when the target cells are empty

The macro relies on looping `1 To 500` rows in multiple places, which feels fragile

Error handling around optional sheets (like “P2Ps Set”) feels unreliable

I want to avoid overwriting existing data but still handle edge cases cleanly

**What I’m looking for**

Help fixing the date matching so it’s reliable

Suggestions to make the loops more robust (last used row instead of hard limits)

Cleaner error handling, especially for missing sheets

Any obvious logic bugs or best-practice improvements

I’ve included the full macro below for context.I have a VBA macro that imports daily call statistics from an exported Excel file into a master workbook. The macro mostly works, but I’m running into inconsistent behaviour and want help fixing and tightening it up.

**What the macro is supposed to do**

Prompt the user to select an exported Excel file

Read totals from a sheet called **“Call Logger”** by searching column G for:

   `Total Recorded Logs`
   

Total RPCs

 `Total Converted`

 
 Write those values into an **“OB & RPC”** sheet in the target workbook, matched by today’s date (formatted as text, e.g. `Mon 5th Jan 2026`)

 
 Ask the user if inbound calls should be added to the RPC count and optionally add them

 
 Write the final RPC count into a **“CPC & CDC”** sheet

 
 If a sheet called **“P2Ps Set”** exists in the source file, copy all rows into a **“P2P”** sheet in the target workbook, appending to the next empty row

 
 Close the source workbook without saving and show a summary message

Date matching sometimes fails even though the date text exists in column A

Data occasionally doesn’t write even when the target cells are empty

The macro relies on looping `1 To 500` rows in multiple places, which feels fragile

Error handling around optional sheets (like “P2Ps Set”) feels unreliable

I want to avoid overwriting existing data but still handle edge cases cleanly

**What I’m looking for**

Help fixing the date matching so it’s reliable

Suggestions to make the loops more robust (last used row instead of hard limits)

Cleaner error handling, especially for missing sheets

Any obvious logic bugs or best-practice improvements

I’ve included the full macro below for context.


```vba
Sub ImportDailyCallStats()
    On Error GoTo ErrorHandler
    
    Dim wbTarget As Workbook
    Dim wbSource As Workbook
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim wsInbound As Worksheet
    Dim wsP2P As Worksheet
    Dim wsP2PSource As Worksheet
    
    Dim filePath As Variant
    Dim callDateShort As String
    Dim totalCalls As Long
    Dim totalRPCs As Long
    Dim totalConverted As Long
    Dim inboundCalls As Long
    Dim finalRPCCount As Long
    
    Dim foundCell As Range
    Dim i As Long
    Dim p2pRow As Long
    Dim p2pSourceRow As Long
    Dim p2pCount As Long
    
    Const TARGET_SHEET_NAME As String = "OB & RPC"
    Const INBOUND_SHEET_NAME As String = "CPC & CDC"
    Const P2P_SHEET_NAME As String = "P2P"
    
    Set wbTarget = ThisWorkbook
    
    ' Open file dialog
    filePath = Application.GetOpenFilename("Excel Files (*.xls;*.xlsx), *.xls;*.xlsx", , "Select the exported Call Logger file")
    If filePath = False Then Exit Sub
    
    ' Open source workbook
    Set wbSource = Workbooks.Open(filePath)
    Set wsSource = wbSource.Sheets("Call Logger")
    
    ' Get today's date in short format (e.g., "Mon 5th Jan 2026")
    callDateShort = Format(Date, "ddd d\th mmm yyyy")
    
    ' Find the metric values by searching column G
    totalCalls = 0
    totalRPCs = 0
    totalConverted = 0
    
    For i = 1 To 500
        If wsSource.Cells(i, 7).Value = "Total Recorded Logs" Then
            totalCalls = wsSource.Cells(i, 8).Value
        End If
        If wsSource.Cells(i, 7).Value = "Total RPCs" Then
            totalRPCs = wsSource.Cells(i, 8).Value
        End If
        If wsSource.Cells(i, 7).Value = "Total Converted" Then
            totalConverted = wsSource.Cells(i, 8).Value
        End If
    Next i
    
    ' --- UPDATE OB & RPC SHEET ---
    Set wsTarget = wbTarget.Sheets(TARGET_SHEET_NAME)
    
    For i = 1 To 500
        If wsTarget.Cells(i, 1).Value = callDateShort Then
            If wsTarget.Cells(i, 2).Value = "" Or wsTarget.Cells(i, 2).Value = 0 Then
                wsTarget.Cells(i, 2).Value = totalCalls
                wsTarget.Cells(i, 3).Value = totalRPCs
                wsTarget.Cells(i, 4).Value = totalConverted
            Else
                MsgBox "Date already has data in OB & RPC. Skipping.", vbExclamation
            End If
            Exit For
        End If
    Next i
    
    ' --- ASK ABOUT INBOUND CALLS ---
    inboundCalls = 0
    If MsgBox("Do you want to add inbound calls to the RPC count?", vbYesNo, "Inbound Calls") = vbYes Then
        Dim userInput As String
        userInput = InputBox("Enter number of inbound calls:", "Inbound Calls", "0")
        If userInput <> "" Then
            If IsNumeric(userInput) Then
                inboundCalls = CLng(userInput)
            End If
        End If
    End If
    
    finalRPCCount = totalRPCs + inboundCalls
    
    ' --- UPDATE CPC & CDC SHEET ---
    Set wsInbound = wbTarget.Sheets(INBOUND_SHEET_NAME)
    
    For i = 1 To 500
        If wsInbound.Cells(i, 1).Value = callDateShort Then
            If wsInbound.Cells(i, 2).Value = "" Or wsInbound.Cells(i, 2).Value = 0 Then
                wsInbound.Cells(i, 2).Value = finalRPCCount
            End If
            Exit For
        End If
    Next i
    
    ' --- P2P DATA IMPORT ---
    On Error Resume Next
    Set wsP2PSource = wbSource.Sheets("P2Ps Set")
    On Error GoTo ErrorHandler
    
    If Not wsP2PSource Is Nothing Then
        Set wsP2P = wbTarget.Sheets(P2P_SHEET_NAME)
        
        ' Find the next empty row in target P2P sheet (check column B)
        p2pRow = 3
        Do While wsP2P.Cells(p2pRow, 2).Value <> ""
            p2pRow = p2pRow + 1
        Loop
        
        p2pCount = 0
        
        ' Loop through source P2P data (start from row 2 to skip header)
        For p2pSourceRow = 2 To 500
            ' Check if row has data (column A = Date)
            If wsP2PSource.Cells(p2pSourceRow, 1).Value <> "" Then
                ' Copy data to target sheet
                ' Source: A=Date, B=Number, C=Phone, D=P2P Date, E=Amount
                ' Target: B=Date, C=Number, D=Phone, E=P2P Date, F=Amount
                wsP2P.Cells(p2pRow, 2).Value = wsP2PSource.Cells(p2pSourceRow, 1).Value  ' Date
                wsP2P.Cells(p2pRow, 3).Value = wsP2PSource.Cells(p2pSourceRow, 2).Value  ' Number
                wsP2P.Cells(p2pRow, 4).Value = wsP2PSource.Cells(p2pSourceRow, 3).Value  ' Phone
                wsP2P.Cells(p2pRow, 5).Value = wsP2PSource.Cells(p2pSourceRow, 4).Value  ' P2P Date
                wsP2P.Cells(p2pRow, 6).Value = wsP2PSource.Cells(p2pSourceRow, 5).Value  ' Amount
                
                p2pRow = p2pRow + 1
                p2pCount = p2pCount + 1
            Else
                Exit For
            End If
        Next p2pSourceRow
    End If
    
    ' Close source workbook
    wbSource.Close SaveChanges:=False
    
    ' Final message
    Dim msg As String
    msg = "Import completed!" & vbCrLf & vbCrLf
    msg = msg & "CALL LOGGER:" & vbCrLf
    msg = msg & "Total Calls: " & totalCalls & vbCrLf
    msg = msg & "Total RPCs: " & totalRPCs & vbCrLf
    msg = msg & "Total Converted: " & totalConverted & vbCrLf
    msg = msg & "Final RPC (with IB): " & finalRPCCount & vbCrLf & vbCrLf
    msg = msg & "P2P RECORDS: " & p2pCount & " imported"
    
    MsgBox msg, vbInformation, "Import Complete"
    
    Exit Sub
ErrorHandler:
    If Not wbSource Is Nothing Then wbSource.Close SaveChanges:=False
    MsgBox "Error: " & Err.Description, vbCritical
End Sub

Answer 1

Hello @Jacob lane, 

Thank you for posting your question in the Microsoft Q&A forum. 

I totally get how frustrating inconsistent VBA behavior can be, especially when it’s importing daily data, and you need it to be reliable. 

Since I don’t have any tool to test on your VBA scripts, my recommendation is to post your issue on Stack Overflow, where many experienced developers actively support Office Scripts and VBA questions, and they may be able to help manually convert your code since that community has deeper coding expertise.   
Disclaimer: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Note: Please understand that as a forum moderator, my primary goal is to provide helpful guidance and support through general troubleshooting steps. While I don’t have access to internal systems or test devices required to resolve backend/account issues, I truly appreciate your understanding of these limitations.  

Once again, apologize for redirecting you to another forum, but I want to make sure you get the most accurate and high‑quality assistance. I’m still here to help however I can. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have any extra questions about this answer, please click "Comment."    

If you want to receive the related email notification for this thread, please follow the steps in our documentation to enable e-mail notifications.