copying several specific rows from different sheets into a single column in another sheet.

[jpk6789]

Hi all, I'm trying so detect the existence of a column of data by checking to see if the first cell in all possible columns contains data. If so, I want to copy the data from all ranges found this way into a single master column in a separate sheet. Currently the code appears to loop, detect, and copy correctly. I'm having trouble getting it to recognize the first unused cell in the master column and copying newly found ranges there. Instead it pastes each newly found range on top of the old one in a2. Any help would be appreciated.



Sub securityList()

Dim W As Worksheet
Dim Rng As Range
Dim Cel As Range

Dim RwCount As Integer


RwCount = 1


For Each W In ThisWorkbook.Worksheets
If W.Index < 6 Then

Set Rng = W.Range("a1:io1")

For Each Cel In Rng
If IsEmpty(Cel) = False Then
Range(Cel, Cel.End(xlDown)).Copy
Sheets("list").Activate

'I'm sure this line is the culprit but I'm not seeing my error

ActiveSheet.Cells(RwCount, 1).PasteSpecial Paste:=xlPasteValues

' or this one...

RwCount = Sheets("list").Range("a1").End(xlDown).Rows.Count + 1

End If
Next Cel

End If
Next W
End Sub

 

[Rijnsent]

Hi jpk,
something like this:

Sub securityList()

Dim W As Worksheet
Dim Rng As Range
Dim Cel As Range
Dim PasteRw As Long

Set ShtDest = Worksheets("Sheet1")

For Each W In ThisWorkbook.Worksheets
    If W.Index < 6 Then
        Set Rng = W.Range("a1:io1")
        For Each Cel In Rng
            If IsEmpty(Cel) = False Then
                PasteRw = ShtDest.Range("A" & Cells.Rows.Count).End(xlUp).Row + 1
                W.Range(Cel, Cel.End(xlDown)).Copy
                ShtDest.Cells(PasteRw, 1).PasteSpecial Paste:=xlPasteValues
            End If
        Next Cel
    End If
Next W

Set ShtDest = Nothing

End Sub

I hope you get where the code differs from yours? The line PasteRw = ShtDest.Range("A" & Cells.Rows.Count).End(xlUp).Row + 1 is a more practical way to find the last row then .end(xldown). That command will give back the last row of a sheet in case you encounter a column with only a value in row 1 (so from row 2 on it's empty), which will cause your macro to crash, as all those cells can't be pasted in the main sheet. Please use [ CODE ] brackets the time you post code, that makes it lots more readable.
Cheers,
Koen