Copy and Paste Cell From Column A if Cell In Column B Has No Data In It

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
I am stuck on this and can not work it out as I CAN NOT get it to copy the cell over from sheet2 to sheet1

What it should Do.
In sheet2 there are 2 columns with data. A & B. Column A has the data in it and B has the word "Done". The code should check Column B to see if it is EMPTY. Then Copy and paste the FIRST cell in A over to Sheet1 B2
1613303904004.png


The code should copy the word "BookShops" over to Sheet1 B2, once the copy and paste has take place it will place the word "Done" into Sheet2 column B to indicate that this cell has been done.
Therefore the NEXT time the code it run it will copy over the word "Shoe Shops"
1613303967361.png


The list in Sheet2 Column A will be DYNAMIC
VBA Code:
Application.ScreenUpdating = False
    Dim ws As Worksheet
        Set ws = Sheets("Sheet2") '''Sheet2 Column A has Search criteria + column B has the word "Done"
            Dim c  As Range
                For Each c In Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row) ''' if column B = "Done" the go to next blank cell
                    If c.Value = "" Then c.Value = c.Offset(, -1).Value ''' if Blank then select cell in Column A
                    ws.Range(ws.Range("A"), ws.Range("A").End(xlUp)).Copy '''Copy Search criteria in A
          Set ws = Sheets("Sheet1") '''Sheet1 Cell B2 is where the coppied data needs to be pasted into from Sheet 2"
                With Sheets("Sheet1") ''' Set sheet1 and pasted coppied data into Sheet1 B2
                    .Select
                    .Range("b2").Select
                    .Paste
                End With
         'Add word "Done" to sheet2 column B, so next time code is run, it will GO TO THE NEXT item as
         'it is looking for the next BLANK cell in column B to copy over.
         ThisWorkbook.Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Offset(RowOffset:=1).Value = "Done"
Application.ScreenUpdating = True
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
Sorry my fault I thought I mentioned that I have this on a GoTo, so once it has copied and pasted the result and searched the criteria for an X amount of times, it will go the start of the code and run the process again coping the next criteria over. All that works fine. However when it has done the last item in Sheet2 Column A although this process as stopped, the next bit of code

VBA Code:
GoTo StartCode:

1613319528136.png



VBA Code:
Private Sub CommandButton2_Click()
    Sheet1.Range("F1").Value = "Stop"
End Sub

1613319640613.png


I currently I have to stop it manually, I would like it to stop once Sheet2 Column A as nothing more to copy over.

Hope this makes sence
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,877
Office Version
  1. 365
Platform
  1. Windows
In that case use
VBA Code:
   With Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
      If .Value <> "" Then
         .Copy Sheets("Sheet1").[B2]
         .Offset(, 1).Value = "Done"
      Else
         Exit Sub
      End If
   End With
 
Solution

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,057
Office Version
  1. 2016
Platform
  1. Windows
Super Stuff Fluff a big thanks to Osvaldo and yourself for your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,877
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,008
Messages
5,744,324
Members
423,862
Latest member
ExcelUserX

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