Macro to copy data next to first blank cell

[gbeaune]

Hi all,

I need some assistance and I am fairly new to VBA. I am trying to create a macro that will open a workbook search a column for the first blank cell and copy the data in the cell next to it.

Example:
IF Range(B1:B100) = blank then
Copy data from Cell.offset(0,-1)
Paste into a specific cell in another workbook
After it does that I need it to then copy data from specific cells and paste them into the same row it found the blank space it copied from. I am this makes sense and thank you for the help!!!

 

[Audioa84]

Sub Test()
Dim i As Integer

'for every row 1 to 100, find the first blank cell and keep the row number
For i = 1 To 100
   If IsEmpty(Cells(i, 2).Value) Or Cells(i, 2).Value = "" Then
       Exit For
   End If
Next
Cells(i, 2).Offset(0, -1).Copy Destination:=Workbooks("Book2").Range("C1")   'Change C1 to be the specific cell in another workbook
Range("A" & i&"C"&i).Copy Destination:=Range("D1")  'Change the range to be the specific cells you wish to copy.
End Sub

 

[gbeaune]

how do I designate that I want to search Column B?

Sub Test()
Dim i As Integer

'for every row 1 to 100, find the first blank cell and keep the row number
For i = 1 To 100
   If IsEmpty(Cells(i, 2).Value) Or Cells(i, 2).Value = "" Then
       Exit For
   End If
Next
Cells(i, 2).Offset(0, -1).Copy Destination:=Workbooks("Book2").Range("C1")   'Change C1 to be the specific cell in another workbook
Range("A" & i&"C"&i).Copy Destination:=Range("D1")  'Change the range to be the specific cells you wish to copy.
End Sub

 

[Audioa84]

The column B is designated in the Cells(i,2) part. The Cells function has the following pattern: Cells(rowNumber, columnNumber). So Cells(1,2) = cell B1.

 

[gbeaune]

Thank you. This line of the code isn't working. (Range("A" & i&"C"&i).Copy Destination:=Range("D1") 'Change the range to be the specific cells you wish to copy.) I am trying to get it to copy the cell next to the first blank cell it came across. Any suggestions?

 

[Audioa84]

That should be happening on the line: Cells(i, 2).Offset(0, -1).Copy Destination:=Workbooks("Book2").Range("C1"). Lets dissect the code and assume that the first row with a blank is 12 (so i = 12).
Cells(i,2): Cells(12,2) would be cell B12.
Offset(0,-1): The Offset function has the pattern Offset(rowsAdd, columnsAdd). So Offset(0,-1) give us the same cell, one column to the left. The result would give us cell A12.
Copy: This one's self-evident. Copy cell A12.
Destination:=Workbooks("Book2").Range("C1"): This is where we are pasting the results.

So the whole statement together should give you the action of copy cell A12 in the active workbook and paste it in cell C1 in the second workbook.

After looking at my code, I would change Workbooks("Book2").Range("C1") to Workbooks("Book2").Sheets("Sheet1").Range("C1") where you change Sheet1 to be the name of the sheet you are pasting to in the second workbook.

One other fix is that I missed a colon in the Range("A" & i&"C"&i).Copy Destination:=Range("D1") code. It should be:

[COLOR=#333333]Range("A" & i&":C"&i).Copy Destination:=Range("D1")[/COLOR]

 

[gbeaune]

Thank you for the fix. Part of the final line of the code does what I need. It might just be because I don't understand. What I need is for example: Copy Cell C4 from book 2 into the first blank cell in book one and so on until I fill most of the cells across the row. Again thank you for your help any ideas?

 

[Audioa84]

You could do this but you would need to have a pattern to what cells you wanted to copy in book2. For example, if cell B4 is blank in Book1, copy cell C4 from Book2 into the blank cell. Another example would be to have C1 copied into the first blank, C2 into the second blank, etc. You just have to be able to calculate what cell you want from Book2.