CODEWITHSUNDEEP
vbaexcel
Jake
Jake

Reputation: 105

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?

Upvotes: 0

Views: 55

Answers (2)

rwisch45
rwisch45

Reputation: 3702

I agree with @Andy G. Here is an alternative, shorter version of your procedure. Since you are looping through each worksheet in the workbook, the use of the wksheet_number variable is unnecessary. You can just use the wkSheet object directly in each iteration of the loop. 

Sub compile()

    Dim srcRange, destRange As Range
    Dim wkSheet, sheetDestination As Worksheet
    Dim columnCount, lastRow As Long

    rowCount = 2 'Starting at A2

    Set sheetDestination = ThisWorkbook.Worksheets(1) 'Also could use the name like ThisWorkbook.Worksheets("MyReport")

    For Each wkSheet In ThisWorkbook.Worksheets

        If Not wkSheet = sheetDestination Then

            lastRow = wkSheet.Cells(Rows.Count, "A").End(xlUp).Row
            Set srcRange = wkSheet.Range("A2:K" & lastRow)
            Set destRange = sheetDestination.Range("A" & rowCount)

            srcRange.Copy destRange

            rowCount = rowCount + lastRow + 1

        End If

    Next wkSheet


    End Sub

Upvotes: 1

Andy G
Andy G

Reputation: 19367

You need to make the following corrections to your code:

Set destRange = destRange.End(xlDown)
Set destRange = destRange.Offset(1, 0)

You were referring to srcRange instead of destRange, so it was just over-copying the same range (in the other worksheets).

Upvotes: 1

Related Questions