Copy and paste next none empty cell from sheet2 to sheet1

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
 Sheets("Sheet1").Range("b1").End(xlDown).Offset(1, 0).Paste
1612131180342.png


1612131234301.png


VBA Code:
[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]
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
414
Office Version
  1. 2016
Platform
  1. Windows
With the changes I marked your macro will not error any more but I'm not sure it's what you are asking for.
VBA Code:
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
Why is this putting the data into sheet1 A1 and NOT Sheet1 B1
VBA Code:
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

Active Member
Joined
Jul 30, 2012
Messages
414
Office Version
  1. 2016
Platform
  1. Windows
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).
VBA Code:
...
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
...
 
Last edited:
Solution

Watch MrExcel Video

Forum statistics

Threads
1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top