VBA IF Statement Copying Row to New Sheet - Multiple Workbooks

[cjvenables]

Hello,

I have been having trouble finding a Macro that will find a value in a column, copy the row to the new sheet, and then go through all of the worksheets and do the same thing.

I have an Excel file with 12 tabs, 1 for each month, (January SAP, Februrary SAP, etc.). I need the IF to find a certain vendor number in Column H. If it is the right number, it copies the row and pastes to a blank sheet (Sheet2). Once it finishes with January, I need it to go to February, and so on.

I have been able to do it with 1 sheet, but have ran into trouble with other sheets. I would just throw all of the data into Access, but I have issues with certain pieces of data transferring, which is counter productive to the process.

Here is my data for each month worksheet:

Range A2:R350000
Value is in Column H
Value to find: 12345 (if it finds the value, it copy/pastes the entire row to Sheet 1). All of my other tabs have month name and SAP.

This seems pretty simple, I have not been able to merge a loop macro together with the IF macro.

Let me know if you need more data.

Thanks!

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Copy_Vendor_Rows()<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, Nextrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FindVend <SPAN style="color:#00007F">As</SPAN> Range, Vendor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsDest <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> wsDest = Sheets("Sheet2")   <SPAN style="color:#007F00">'Destination worksheet</SPAN><br>    Nextrow = 1                     <SPAN style="color:#007F00">'starting row on destination worksheet</SPAN><br>    Vendor = 12345                  <SPAN style="color:#007F00">'Find vendor number</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 12<br>        <SPAN style="color:#00007F">With</SPAN> Sheets(MonthName(i) & " SAP")<br>            <SPAN style="color:#00007F">Set</SPAN> FindVend = .Range("H:H").Find(Vendor, _<br>                                         LookIn:=xlValues, _<br>                                         LookAt:=xlWhole, _<br>                                         SearchOrder:=xlByRows, _<br>                                         SearchDirection:=xlNext, _<br>                                         MatchCase:=False)<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> FindVend <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                FindVend.EntireRow.Copy _<br>                    Destination:=wsDest.Rows(Nextrow)<br>                Nextrow = <SPAN style="color:#00007F">Next</SPAN>row + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Next i<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[cjvenables]

Sub Copy_Vendor_Rows()
Dim i As Integer, Nextrow As Long
Dim FindVend As Range, Vendor As Variant
Dim wsDest As Worksheet

Set wsDest = Sheets("Sheet2") 'Destination worksheet
Nextrow = 1 'starting row on destination worksheet
Vendor = 12345 'Find vendor number
For i = 1 To 12
With Sheets(MonthName(i) & " SAP")
Set FindVend = .Range("H:H").Find(Vendor, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FindVend Is Nothing Then
FindVend.EntireRow.Copy _
Destination:=wsDest.Rows(Nextrow)
Nextrow = Nextrow + 1
End If
End With
Next i
End Sub

AlphaFrog,

Thanks for the help.

I'm still having an issue with it being out of range when I run it. The error comes at the "with sheets" line. It says "subscript out of range." Error '9'

I did not see anything about an active workbook in the code, so I wasn't sure if the macro knows where it should start?

Again, thanks for the help. I know it's something simple I'm not doing.

 

[cjvenables]

AlphaFrog,

I got it to work, but I think I left out an important piece of information:

The tabs on my report have thousands of cells, and this one vendor I'm looking for usually comes up 3-4K times/each tab. I need the macro to return every instance when it finds the vendor. For now, I only get 12 lines, which equal the first line it finds in each of the sheets.

Also, how would the code change if I wanted to find an Array of vendors, each with different and unique numbers, in no sequential order?

Thanks for your help!

 

[cjvenables]

Help Please...Re: VBA IF Statement Copying Row to New Sheet - Multiple Workbooks

Can someone help? This is somewhat time sensitive.