Sharid
Well-known Member
- Joined
- Apr 22, 2007
- Messages
- 1,064
- Office Version
- 2016
- Platform
- 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
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"
The list in Sheet2 Column A will be DYNAMIC
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
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"
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