Copy and paste next none empty cell from sheet2 to sheet1

[Sharid]

I am trying to copy an none empty cell from Sheet2 column A TO sheet1 cell B1 , but i am having issues. I have tried to set it to sheet2 but it gives me an error on xCell.Select. If I leave it as it is then I get an error on this row

 Sheets("Sheet1").Range("b1").End(xlDown).Offset(1, 0).Paste

[CODE=vba]
Dim xCell As Range
    Set ws = Sheets("Sheet2")
    For Each xCell In ActiveSheet.Columns(1).Cells '''' currently set to active sheet, which will be sheet1, HOWEVER NEED TO SEARCH SHEET2 COLUMN1
        If Len(xCell) <> "" Then
            xCell.Select
    
    Set ws = Sheets("Sheet2")
         ws.Range(ws.Range("a1"), ws.Range("a1").End(xlDown)).Copy
         Sheets("Sheet1").Range("b1").End(xlDown).Offset(1, 0).Paste
    End If
    Exit For

[/CODE]

 

[rollis13]

With the changes I marked your macro will not error any more but I'm not sure it's what you are asking for.

Option Explicit
Sub test()
    Dim ws     As Worksheet                       '<=
    Dim xCell      As Range
    Set ws = Sheets("Sheet2")
    For Each xCell In ActiveSheet.Columns(1).Cells 'currently set to active sheet, which will be sheet1, HOWEVER NEED TO SEARCH SHEET2 COLUMN1
        If Len(xCell) <> "" Then
            xCell.Select
            'Set ws = Sheets("Sheet2")             '<=
            ws.Range(ws.Range("A1"), ws.Range("A1").End(xlDown)).Copy
            Sheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0).Select '<=
            ActiveSheet.Paste                     '<=
        End If
        Exit For
    Next
End Sub

 

[Sharid]

Why is this putting the data into sheet1 A1 and NOT Sheet1 B1

ws.Range(ws.Range("A1"), ws.Range("A1").End(xlDown)).Copy
            Sheets("Sheet1").Range("B1").End(xlDown).Offset(1, 0).Select '<=
            'Sheets("Sheet1").Range("B1").End(xlDown).Offset(1, 1).Select '<=
            ActiveSheet.Paste

 

[rollis13]

Can't be of any other help since you haven't shown the layout of your data and I don't own a crystall ball, anyway, make these changes to be sure that sheet1 has been selected. Be aware that column B of sheet1 must have at least 2 cells populated to work correctly since you are using .End(xlDown).

...
ws.Range(ws.Range("A1"), ws.Range("A1").End(xlDown)).Copy
With Sheets("Sheet1")
    .Select
    .Range("B1").End(xlDown).Offset(1, 0).Select
    .Paste
End With
...

 

[rollis13]

I'm glad I've been of some help .