Macro to search for input criteria and copy and paste selected cells to another sheet

[jennifercorp]

Hello,

I am very new to building macros and this is my first post (hopefully of many ). I have been able to patch together the below macro, but am not able to get it to work properly. The goal is to be able to click a button which will prompt for a loan number, once the loan number is received, it will check sheet "Retrieval Log" column "D" for a match. If a match is received I would like it to grab the data in that row and paste it to various cells on sheet "BCS Submition Form". The code that I have is below. I am currently getting a "Copy method of Range class failed error".

Thank you in advance to anyone who may be able to help!

Private Sub CommandButton9_Click()
Dim Prompt, LoanNumber As String
Prompt = "Loan Number"
LoanNumber = InputBox(Prompt)

If LoanNumber = "" Then Exit Sub

LastRow = Sheets("Retrieval Log").Range("D" & Rows.Count).End(xlUp).Row
For Each Cell In Sheets("Retrieval Log").Range("D1:D" & LastRow)
If Cell = LoanNumber Then
FoundRow = Cell.Row
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant

Set ws1 = Workbooks("BCS - TL - MASTER.xls").Sheets("BCS Submition Form")
Set ws2 = Workbooks("BCS - TL - MASTER.xls").Sheets("Retrieval Log")

' specify the cells that will contain information in sheet1
v1 = Array("C9", "C10", "C11", "C12", "C13", "I9", "I10", "I11", "I12", "I13", "C18", "G18", "M18", "C19", "E19", "I19", "M19", "D23", "H23", "K23", "M23", "H24", "K24", "M24", "D26", "H26", "K26", "M26", "H27", "K27", "M27", "D29", "H29", "K29", "M29", "D30", "H30", "K30", "M30", "D32", "H32", "K32", "M32", "D33", "H33", "K33", "M33", "D34", "H34", "K34", "M34", "D35", "H35", "K35", "M35", "D37", "H37", "K37", "M37", "D38", "H38", "K38", "M38", "D39", "H39", "K39", "M39", "D40", "H40", "K40", "M40", "D42", "H42", "K42", "M42", "D44", "H44", "K44", "M44", "C47", "E47", "I47", "L47", "C48", "E48", "I48", "L48", "D51", "I51", "D52", "D55", "D56", "B63", "C63", "F63", "I63", "K63", "B69", "B76")
' in the same order specify what columns that info would be
' placed in in sheet2
v2 = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU")
' find the next open row in sheet2 using column A

Debug.Print UBound(v1), UBound(v2)
rw = ws2.Cells([FoundRow], "A")
For i = LBound(v1) To UBound(v1)
Set r1 = ws1.Range(v1(i)) '<== added a change here
Set r2 = ws2.Cells([FoundRow], v2(i))

r2.Copy r1

r2.Value = ""

Next i
End If
Next Cell
If IsEmpty(FoundRow) Then
MsgBox "Sorry, loan number " _
& LoanNumber & " was not saved for retrieval."
End If
End Sub

 

[jennifercorp]

Thank you so much for looking at this for me.

1. Yes the loan number does appear more than once. But I need to search from the bottom up and when it finds the first loan number it can stop. I do not need it to continually loop. So this definately needs to change any specific suggestions would be appreciated.

2. The rw line of code is unnecessary and I deleted it.

3. Done, thank you.

4. The code runs fine until

r2.Copy r1

after that it actually jumps to the next sub and produces a copy method of range class failed!

r1 = ws1.Range("C9")

and

r2 = ws2.Cells(6/25/2012)

<---which is the value of A3 on ws1. So it looks like it is pulling data that I want it to. I just don't understand why it would just jump to the next sub like that! Crazy stuff.

Thank you again!

 

[JLGWhiz]

Thanks!

I removed the 1 but still get the same error "Copy Method of Range Class Failed" Right now I am at:

Debug.Print UBound(v2), UBound(v1)
rw = ws2.Cells(FoundRow, "A")
For i = LBound(v2) To UBound(v2)
Set r1 = ws1.Range(v1(i)) '<== Removed (1)
Set r2 = ws2.Cells(FoundRow, v2(i))

Thank you for the reply and please let me know if you have any other ideas.

The only thing I can see from visual analysis, is that "FoundRow" is not declared in your Dim statement.
I would declare it as a Long and see if that fixes the compiler's dilemma.

 

[Mohammad Basem]

Try this file, the macro CopyData.

 

[jennifercorp]

Mohammad, you are my hero!

Can't tell you how much I appreciate your help. Works like a charm!

 

[Mohammad Basem]

Al-hamdullah, you are most welcome and thank you for the feedback!