Conditional Copy and Paste

[mpearson]

Hi I'm trying to write a VBA macro that will search a sheet for a specific number, find all instances of that number and then copy each of the rows corresponding to that number into a sheet in a new workbook. As of now the macro can find the number and open the new workbook but it will not copy and paste the rows into the new workbook.

Sub stagehours()
Dim jobrequest As String
Dim jobsearch As String
Dim test As String
Dim i As Integer, j As Integer



i = 2
j = 2


jobrequest = InputBox("Please Enter a job number.") 'input job number'

test = Range("B:B").Find(What:=jobrequest)

If test = jobrequest Then
MsgBox "Job Found"



Set NewBook = Workbooks.Add
With NewBook
.SaveAs "jobreport.xls"
End With

NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count


For x = i To NumRows
If Cells(i, 2).Value = jobrequest Then
Rows("i:i").Select
Selection.Copy
Workbooks("jobreport.xls").Activate
ActiveSheet.Rows(j).Paste
j = j + 1
End If

Next

[AB33]

MPearson,
Please wrap your code with code tags. It is forum's rule to use code tags.
I have slightly amended the bit with find function. I have not touched the creating and saving workbook.

Please Login or Register  to view this content.

[mpearson]

Hey sorry for the wrap code. I modified the code a bit. If I change "test" to "set test" it is no longer defined as a string and will not find the jobrequest. The loop also does not find the jobrequest. The main goal is to take the input jobrequest and then find and select every row containing the input jobrequest and paste those rows to the new workbook without overwriting(hense the j counter). I have done programming in Fortran but I am new to excel so I'm not too savy with VBA functions. I really appreciate the help though, thank you.

[AB33]

Could you please upload your workbook with out confidential info. Go in to advance and then attachment.

[mpearson]

Unfortunately I cannot upload the workbook for various reasons. To give a better idea of what I'm trying to do let me rephrase my question. I am recieving an input from an input box, then searching the active sheet for that value in column "B". If the input matches the value in column "B" then I would like to paste the entire row from that sheet into the next available row in a new workbook and sheet. My code still does not copy and paste but I feel I'm getting closer. Thank you for the help.

Please Login or Register  to view this content.

[AB33]

Assuming that you want to copy to sheet 1

Please Login or Register  to view this content.