VBA copy data from the cell next to first blank and paste

[gwa]

Hi there,

I have been trying to put togtherer a VBA that looks for the first blank cell in a column, then copies the value of the cell on the right (next to the blank cell), and pastes it on the first blank cell in another column on another tab.
I would be thankful if you suggest what the above code might be, or an excel/VBA workaround that can achieve the same outcome. Thank you.

 

[mumps]

What are the names of the two sheets? In which column on the first sheet are you searching for the first blank cell? In which column in the second sheet are you pasting the value?

 

[gwa]

What are the names of the two sheets? In which column on the first sheet are you searching for the first blank cell? In which column in the second sheet are you pasting the value?

"Deliveries" and "Info", looking for the first blank cell in Sheets("Info"), column "I", then offsetting to the cell on the right in column "J", coping the value from "J", then pasting in the first blank in column "I" on Sheets("Deliveries).

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, x As Long
    x = Range("A2:A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
    Set srcWS = Sheets("Info")
    Set desWS = Sheets("Deliveries")
    x = srcWS.Range("I2:I" & srcWS.Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row
    With desWS
        .Cells(.Rows.Count, "I").End(xlUp).Offset(1) = srcWS.Range("J" & x)
    End With
    Application.ScreenUpdating = True
End Sub

 

[gwa]

Great, it worked...it has been days..really appreciate!
Just one question, sometimes new lines of code affect what is appearing on some of the drop downs (I have a form with drop downs referencing to another sheet). The above was not in the begining, think I might have done something, so instead of drop down looking for content from 3 cells, it looks at 2 etc.

 

[mumps]

I'm not sure what you mean. It would be helpful if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).