cpmurray1985
New Member
- Joined
- Mar 10, 2022
- Messages
- 15
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
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