Sunday, 8 September 2013

How do I get this loop to grab from every worksheet?

How do I get this loop to grab from every worksheet?

I'm grabbing data in columns A-K from each worksheet and putting it into a
single sheet. My code as it stands is grabbing the first sheet correctly,
but it isn't getting anything after that. I honestly have no idea why.
Here is my code as it stands now:
Sub compile()
Dim srcRange, destRange As Range
Dim wkSheet As Worksheet
Dim wksheet_number, lastRow As Long
wksheet_number = 1
For Each wkSheet In ThisWorkbook.Worksheets
If wksheet_number > 1 Then
lastRow =
ThisWorkbook.Worksheets(wksheet_number).Cells(Rows.Count,
"A").End(xlUp).Row
Set srcRange =
ThisWorkbook.Worksheets(wksheet_number).Range("A2:K" & lastRow)
Set destRange = ThisWorkbook.Worksheets(1).Range("A2")
If destRange.Value = "" Then
srcRange.copy destRange
Else
Set destRange = srcRange.End(xlDown)
Set destRange = srcRange.Offset(1, 0)
'destRange.Select
srcRange.copy destRange
End If
wksheet_number = wksheet_number + 1
Else
wksheet_number = wksheet_number + 1
End If
Next wkSheet
End Sub
Apologies for the lack commenting, it is something I should probably add
next time. So anyone know where I went wrong?

No comments:

Post a Comment