Complicated One!

[Mary]

Hi I have two spreadsheets

Any of the account numbers in column A, spreadsheet name
1.xls found in spreadsheet 2.xls in column B should have
their entire row removed and added to a new sheet in 2.xls

I cant work out how to cycle through the rows in coulumn
A in 1.xls each at a time checking through all the rows
in 2.xls coulumn B spitting them out into a new sheet if
they match (This would include any part numbers picked up
in the search), Then moving on to the next in 1.xls
column A.

Can anyone help me on this?

Thanks

Mary

 

[Jef Gorbach]

Each month, several reports are exported from a database into new excel
workbooks which are then combined into a single workbook as seperate tabs
for futher processing by a series of macros which are manually copied from
the proceeding month's file to keep the files "stand-alone" and independent
of a given system's personal.xls.

Is it possible, for vba to copy all/certain modules from Reports_Jan05.xls
to Reports_Feb05.xls ??
This macro could either be within personal.xls or called from the prior
month's file.

 

[Dick Kusleika]

Mary

Loop through the cells in column A of 1.xls and use the Find method to see
if there's a match in column B of 2.xls. If there is, copy it, move it,
whatever you want. It might look like this

Sub MoveMatches()

Dim wsSrc As Worksheet
Dim wsFind As Worksheet
Dim wsDest As Worksheet
Dim rCell As Range
Dim rFound As Range

Set wsSrc = Workbooks("1.xls").Sheets(1)
Set wsFind = Workbooks("2.xls").Sheets(1)
Set wsDest = Workbooks("2.xls").Sheets(2)

For Each rCell In wsSrc.Columns(1).Cells
Set rFound = wsFind.Columns(2).Find(rCell.Value)

If Not rFound Is Nothing Then
rCell.EntireRow.Copy wsDest.Range("A65536").End(xlUp).Offset(1,
0)
End If
Next rCell

End Sub

 

[Tom Ogilvy]

http://www.cpearson.com/excel/vbe.htm

on Chip Pearson's site should address this. You would have to export it
from Jan and import it into Feb