VBA won't copy third value of a range from multiple sheets

[cpmurray1985]

Hello,

I have this macro code that can copy a range of cells from multiple worksheets into one worksheet(Archive) and display it in a list. However, if I have the range set from D2:T4, it will only get D2:T3, for maybe the first few sheets, then D2:T3 for the rest, skipping all of row 3 for some worksheets. It will also skip row 4, so I would have to mark it as D2:T5, to at least get row 4 up to T4. I have it post in column D, which fills to column T, but it skips row 3 at times (at first I thought it was because some cells in row 3 in the worksheets were empty, so I filled them in with text, but it still does it regardless as if it was never there) and will skip row 4 unless you put "D2:T5".

Thank you.

Public Sub m()
    Dim lRow As Long
    Dim sh As Worksheet
    Dim shArc As Worksheet
    Set shArc = ThisWorkbook.Worksheets("Archive")
    For Each sh In ThisWorkbook.Worksheets
        Select Case sh.Name
            Case Is <> "Archive"
                lRow = shArc.Range("D" & Rows.Count).End(xlUp).Row
                sh.Range("D2:T5").Copy _
                    Destination:=shArc.Range("D" & lRow)
        End Select
    Next
    Set shArc = Nothing
    Set sh = Nothing
End Sub

 

[Joe4]

Note that you are finding the last row on the "Archive" sheet, and then pasting over it.
I think you want to find the last row, then post to the row BELOW that.

You can do that by changing this line:

                    Destination:=shArc.Range("D" & lRow)

to this:

                    Destination:=shArc.Range("D" & lRow + 1)

 

[cpmurray1985]

Note that you are finding the last row on the "Archive" sheet, and then pasting over it.
I think you want to find the last row, then post to the row BELOW that.

You can do that by changing this line:

                    Destination:=shArc.Range("D" & lRow)

to this:

                    Destination:=shArc.Range("D" & lRow + 1)

Thank you so much, that was exactly what I was looking for and it now works!

 

[Joe4]

You are welcome.
Glad I was able to help.