Hi Guys,

I have created a VBA code with help of a friend. The code copies contents of 2 columns on a sheet to a new sheet. But when I try to extend the code to copy more columns it just does not work. To be more clear it does not copy all of the cells below. Especially when I try to set it to 2 letter columns it straight up does not copy anything, but that's a more minor issue. Please see the code.

When I change the rngSrc "to.Range("F5", .Range("Q" & Rows.Count).End(xlUp))" for example the error occurs. I suspect that this is due to the offset function used below, but I don't really know.

Thanks for the help in advance!


Option Explicit

Sub ConsolidateExpenses()
Dim wsMonth As Worksheet
Dim wsNew As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim idxMonth As Long

    Set wsNew = Sheets.Add

    With wsNew
        .Range("C3:O3").Value = Array("Expense Type", "Expense Amount", "", "Date", "Type of Item", "Item Name", "# of Items", "Item Price", "Income", "Discount", "Discounted Income", "Customer")
        Set rngDst = .Range("C4")
    End With
   
    For idxMonth = 1 To 12
   
        Set wsMonth = Sheets(MonthName(idxMonth, False))
       
        With wsMonth
            Set rngSrc = .Range("F5", .Range("G" & Rows.Count).End(xlUp))
        End With
       
        If rngSrc.Row > 4 Then
            rngSrc.Copy rngDst
            rngDst.Offset(, -1).Resize(rngSrc.Rows.Count) = wsMonth.Name
            Set rngDst = rngDst.Offset(rngSrc.Rows.Count)
        End If
       
    Next idxMonth

    End Sub