Offset in VBA Code not working?

[Allan91]

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.

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

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!

 

[6StringJazzer]

You need a dot before Rows.

            Set rngSrc = .Range("F5", .Range("G" & .Rows.Count).End(xlUp))

The reason is that Rows must refer to the same worksheet that your Range refers to. Rows is a property of Worksheet. If you do not explicitly say which worksheet it is, then it will default to the active sheet (if this is a standard module) or the sheet containing the code (if this is a worksheet module).

 

[Norie]

Have you changed anything in the 'month' sheets?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Offset.Resize in VBA Code not working
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Allan91]

I didn't change anything on the code. It's really weird.

Have you changed anything in the 'month' sheets?

 

[Norie]

It wasn't the code I was asking about.

I was wondering if you had changed anything in the sheets.

 

[Allan91]

I didn't change anything on the code. It's really weird.

Oh I'm sorry. But no. Everything is still the same. When I use the formula as is it still works perfectly.