dyhoerium
09-11-2009, 11:32 AM
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!
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!