Complicated One!
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 |
macro to copy other macros between workbooks?
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. |
Complicated One!
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 -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-blog.com Mary wrote: 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 |
macro to copy other macros between workbooks?
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 -- Regards, Tom Ogilvy "Jef Gorbach" wrote in message ... 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. |
All times are GMT +1. The time now is 04:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com