Paste selection to the first blank cell in a column

[dyhoerium]

Hi, I'm learning VBA and am attempting my first coding. I've managed to get it to do everything that I want, except one thing. I'll explain what I'm working with a little bit.

I have a lot of data, 30 some columns and anywhere between 600 and 1000 rows. The purpose of the code is to search for a particular entry in column D and then paste that entire row into a different workbook. This will be done once a week and each week I want to add to the second workbook, not paste over the data from the previous week. Here is the code I have so far.

Option Compare Text

Sub SearchForString()

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute


LSearchRow = 3


LCopyToRow = 2

Sheets("Raw Data").Select

While Len(Range("A" & CStr(LSearchRow)).Value) > 0


If Range("D" & CStr(LSearchRow)).Value = "Bobl" Then


Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy


Windows("Bob.xlsx").Activate
Sheets("Raw Data").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste


LCopyToRow = LCopyToRow + 1


Windows("Worklist Tracking 7th Generation.xlsm").Activate
Sheets("Raw Data").Select

End If

LSearchRow = LSearchRow + 1

Wend


Application.CutCopyMode = False
Range("A3").Select

MsgBox "Bob's Report is finished."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


I want the code to do is search the second workbook for the first cell in column A that is blank (or any other way pasting the selection in the first blank row) and paste the row from the first workbook in that blank cell. This way each week I will be adding to the data, not simply copying on top of.

I know what needs to be changed, I just have no clue what to change it to. I have searched high and low for an answer. I have found helpful information, but I don't really know how to adjust it to suit my needs.

The part that needs to be changed is when the line "LCopyToRow = 2".

Any help would be greatly appreciated!

[rbrhodes]

Hi dy,

and welcome!

First don't init LCopyToRow. The way to find the first blank row after data is to use Range(...something...).end(xlup).row +1


Windows("Bob.xlsx").Activate
Sheets("Raw Data").Select


'First blank row in Column A
LCopyToRow = Range("A" & rows.count).end(xlup).row +1


Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

[dyhoerium]

That's great! Thank you so much for your help. I got it to work exactly like I wanted it to. Thanks again :-D