Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workbook
I receive a workbook with three worksheets that have cells in them that are
changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workbook
This assumes that both "received.xls" amnd "master.xls" are open:
Sub marine() Dim sh As Worksheet, rr As Range Dim wb1 As Workbook, wb2 As Workbook Set wb1 = Workbooks("master.xls") Set wb2 = Workbooks("received.xls") For Each sh In wb2.Sheets For Each r In sh.UsedRange If r.Interior.ColorIndex = 4 Then r.Copy wb1.Sheets(sh.Name).Range(r.Address) End If Next Next End Sub -- Gary''s Student - gsnu200907 "HarryisTrying" wrote: I receive a workbook with three worksheets that have cells in them that are changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workb
Wow!
I thought that would be really complicated. I had search the web and patched some code together that was much, much longer and I didn't have it working. I hope to learn to write code as one line but for now it takes me 10 lines or so to do what you did in one! Thanks. I usually try to modify what is provided to do something different. That way I begin to learn how the code provided really works I appreciate your help very much. -- Thank You "Gary''s Student" wrote: This assumes that both "received.xls" amnd "master.xls" are open: Sub marine() Dim sh As Worksheet, rr As Range Dim wb1 As Workbook, wb2 As Workbook Set wb1 = Workbooks("master.xls") Set wb2 = Workbooks("received.xls") For Each sh In wb2.Sheets For Each r In sh.UsedRange If r.Interior.ColorIndex = 4 Then r.Copy wb1.Sheets(sh.Name).Range(r.Address) End If Next Next End Sub -- Gary''s Student - gsnu200907 "HarryisTrying" wrote: I receive a workbook with three worksheets that have cells in them that are changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workb
I need something that does a row (Column A) match between files before doing
any updates. What I didn't explain well is the columns are the exact same thing but the master has a 1000 rows and the recieved may have 50 rows (which have updated cells). So, I need to look at rec'd read the name in A column then go to Master find the Name and then update the cells that changed (green in the received). Also, if a Name is in received but not in Master I want to right that record to the end of Master and do a Pop Up thats says "New Record Added " & Name Sorry, it sure works if the files are exact except for changes but the rec'd file is a subset of Master -- Thank You "Gary''s Student" wrote: This assumes that both "received.xls" amnd "master.xls" are open: Sub marine() Dim sh As Worksheet, rr As Range Dim wb1 As Workbook, wb2 As Workbook Set wb1 = Workbooks("master.xls") Set wb2 = Workbooks("received.xls") For Each sh In wb2.Sheets For Each r In sh.UsedRange If r.Interior.ColorIndex = 4 Then r.Copy wb1.Sheets(sh.Name).Range(r.Address) End If Next Next End Sub -- Gary''s Student - gsnu200907 "HarryisTrying" wrote: I receive a workbook with three worksheets that have cells in them that are changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workb
Hi
This code is supposed to be pasted into the Master file macro sheet. Sub CopyGreenCells() Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim sbB As Worksheet Dim FileToOpen As String Dim FirstRow As Long Dim LastRow As Long Set wbA = ThisWorkbook Set shA = wbA.Worksheets("Sheet1") FileToOpen = Application.GetOpenFilename Set wbB = Workbooks.Open(FileToOpen) Set shb = wbB.Worksheets("Sheet1") FirstRow = 2 ' Headings in row 1 LastRow = shb.Range("A" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow ID = shb.Range("A" & r).Value Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole) If Not f Is Nothing Then DestRow = f.Row For col = 2 To 37 'Col B to col AK If shb.Cells(r, col).Interior.ColorIndex = 4 Then shb.Cells(r, col).Copy shA.Cells(DestRow, col) End If Next Set f = Nothing Else shb.Range("A" & r).EntireRow.Copy shA.Range("A1").End(xlDown).Offset(1, 0) msg = MsgBox("New record added" & ID, vbInformation + vbOKOnly, "File update") End If Next End Sub Regards, Per "HarryisTrying" skrev i meddelelsen ... I need something that does a row (Column A) match between files before doing any updates. What I didn't explain well is the columns are the exact same thing but the master has a 1000 rows and the recieved may have 50 rows (which have updated cells). So, I need to look at rec'd read the name in A column then go to Master find the Name and then update the cells that changed (green in the received). Also, if a Name is in received but not in Master I want to right that record to the end of Master and do a Pop Up thats says "New Record Added " & Name Sorry, it sure works if the files are exact except for changes but the rec'd file is a subset of Master -- Thank You "Gary''s Student" wrote: This assumes that both "received.xls" amnd "master.xls" are open: Sub marine() Dim sh As Worksheet, rr As Range Dim wb1 As Workbook, wb2 As Workbook Set wb1 = Workbooks("master.xls") Set wb2 = Workbooks("received.xls") For Each sh In wb2.Sheets For Each r In sh.UsedRange If r.Interior.ColorIndex = 4 Then r.Copy wb1.Sheets(sh.Name).Range(r.Address) End If Next Next End Sub -- Gary''s Student - gsnu200907 "HarryisTrying" wrote: I receive a workbook with three worksheets that have cells in them that are changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workb
Once again you have done what I was trying to do. I can do some simple things
but how did you learn this advanced method? Are there books that can help? I read Mr. Excel and J. Walkenback books, and they are great but your code seems above that level. I do have a couple of questions. I would like to copy only the values and tried to modifiy the shb.Cells(r, col).Copy shA.Cels .(DestRow, col) to end with .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False but that didn't seem to be right since I got the text in Red in VBE I did this because I have formatting and formulas in cells and apparently the received file didn't match the Master and I got a Pop Up asking if I wanted to use the destination and I said Yes and it seem to work fine. Trying to prevent from getting those messages. Other question the line below Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"), is just going to the end of the rows for Excel 2003. I only have about 1 thousand rows so I changed it to A1:A5000 which gives me plenty of growth room -- Thank You "Per Jessen" wrote: Hi This code is supposed to be pasted into the Master file macro sheet. Sub CopyGreenCells() Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim sbB As Worksheet Dim FileToOpen As String Dim FirstRow As Long Dim LastRow As Long Set wbA = ThisWorkbook Set shA = wbA.Worksheets("Sheet1") FileToOpen = Application.GetOpenFilename Set wbB = Workbooks.Open(FileToOpen) Set shb = wbB.Worksheets("Sheet1") FirstRow = 2 ' Headings in row 1 LastRow = shb.Range("A" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow ID = shb.Range("A" & r).Value Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole) If Not f Is Nothing Then DestRow = f.Row For col = 2 To 37 'Col B to col AK If shb.Cells(r, col).Interior.ColorIndex = 4 Then shb.Cells(r, col).Copy shA.Cells(DestRow, col) End If Next Set f = Nothing Else shb.Range("A" & r).EntireRow.Copy shA.Range("A1").End(xlDown).Offset(1, 0) msg = MsgBox("New record added" & ID, vbInformation + vbOKOnly, "File update") End If Next End Sub Regards, Per "HarryisTrying" skrev i meddelelsen ... I need something that does a row (Column A) match between files before doing any updates. What I didn't explain well is the columns are the exact same thing but the master has a 1000 rows and the recieved may have 50 rows (which have updated cells). So, I need to look at rec'd read the name in A column then go to Master find the Name and then update the cells that changed (green in the received). Also, if a Name is in received but not in Master I want to right that record to the end of Master and do a Pop Up thats says "New Record Added " & Name Sorry, it sure works if the files are exact except for changes but the rec'd file is a subset of Master -- Thank You "Gary''s Student" wrote: This assumes that both "received.xls" amnd "master.xls" are open: Sub marine() Dim sh As Worksheet, rr As Range Dim wb1 As Workbook, wb2 As Workbook Set wb1 = Workbooks("master.xls") Set wb2 = Workbooks("received.xls") For Each sh In wb2.Sheets For Each r In sh.UsedRange If r.Interior.ColorIndex = 4 Then r.Copy wb1.Sheets(sh.Name).Range(r.Address) End If Next Next End Sub -- Gary''s Student - gsnu200907 "HarryisTrying" wrote: I receive a workbook with three worksheets that have cells in them that are changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Changes in one workbook to another identical Master Workb
Thanks for your reply.
I only read one book: 'Excel 2000 VBA programmer's reference' by 'John Green, Stephen Bullen, Felipe Martins', and then I have followed this news group for some years. Have also used the MVP' tutorials etc. like what you find on http://www.cpearson.com/excel/topic.aspx. Also VBA help feature in Excel 2000 is very usefull. The secret is to use objects as reference (ie Range/Worksheet objects), and get a lot of programming experience by following / contributing to newsgroups like this. When you use PasteSpecial, the pastespecial statement can not be on same line like a normal Copy/Paste statement. shb.Range("A" & r).EntireRow.Copy shA.Range("A1").End(xlDown).Offset(1, 0).PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False To just search cells with data in column A, use this: Set f = shA.Range("A1", shA.Range("A1").End(xlDown)).Find(what:=ID, After:=shA.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole) Regards, Per "HarryisTrying" skrev i meddelelsen ... Once again you have done what I was trying to do. I can do some simple things but how did you learn this advanced method? Are there books that can help? I read Mr. Excel and J. Walkenback books, and they are great but your code seems above that level. I do have a couple of questions. I would like to copy only the values and tried to modifiy the shb.Cells(r, col).Copy shA.Cels .(DestRow, col) to end with .PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=False but that didn't seem to be right since I got the text in Red in VBE I did this because I have formatting and formulas in cells and apparently the received file didn't match the Master and I got a Pop Up asking if I wanted to use the destination and I said Yes and it seem to work fine. Trying to prevent from getting those messages. Other question the line below Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"), is just going to the end of the rows for Excel 2003. I only have about 1 thousand rows so I changed it to A1:A5000 which gives me plenty of growth room -- Thank You "Per Jessen" wrote: Hi This code is supposed to be pasted into the Master file macro sheet. Sub CopyGreenCells() Dim wbA As Workbook Dim wbB As Workbook Dim shA As Worksheet Dim sbB As Worksheet Dim FileToOpen As String Dim FirstRow As Long Dim LastRow As Long Set wbA = ThisWorkbook Set shA = wbA.Worksheets("Sheet1") FileToOpen = Application.GetOpenFilename Set wbB = Workbooks.Open(FileToOpen) Set shb = wbB.Worksheets("Sheet1") FirstRow = 2 ' Headings in row 1 LastRow = shb.Range("A" & Rows.Count).End(xlUp).Row For r = FirstRow To LastRow ID = shb.Range("A" & r).Value Set f = shA.Range("A1:A65536").Find(what:=ID, After:=shA.Range("A1"), LookIn:=xlValues, Lookat:=xlWhole) If Not f Is Nothing Then DestRow = f.Row For col = 2 To 37 'Col B to col AK If shb.Cells(r, col).Interior.ColorIndex = 4 Then shb.Cells(r, col).Copy shA.Cells(DestRow, col) End If Next Set f = Nothing Else shb.Range("A" & r).EntireRow.Copy shA.Range("A1").End(xlDown).Offset(1, 0) msg = MsgBox("New record added" & ID, vbInformation + vbOKOnly, "File update") End If Next End Sub Regards, Per "HarryisTrying" skrev i meddelelsen ... I need something that does a row (Column A) match between files before doing any updates. What I didn't explain well is the columns are the exact same thing but the master has a 1000 rows and the recieved may have 50 rows (which have updated cells). So, I need to look at rec'd read the name in A column then go to Master find the Name and then update the cells that changed (green in the received). Also, if a Name is in received but not in Master I want to right that record to the end of Master and do a Pop Up thats says "New Record Added " & Name Sorry, it sure works if the files are exact except for changes but the rec'd file is a subset of Master -- Thank You "Gary''s Student" wrote: This assumes that both "received.xls" amnd "master.xls" are open: Sub marine() Dim sh As Worksheet, rr As Range Dim wb1 As Workbook, wb2 As Workbook Set wb1 = Workbooks("master.xls") Set wb2 = Workbooks("received.xls") For Each sh In wb2.Sheets For Each r In sh.UsedRange If r.Interior.ColorIndex = 4 Then r.Copy wb1.Sheets(sh.Name).Range(r.Address) End If Next Next End Sub -- Gary''s Student - gsnu200907 "HarryisTrying" wrote: I receive a workbook with three worksheets that have cells in them that are changed. the worksheets are Servers, Network and Storage The cells have a colorindex = 4 if changed. I put the received in a folder C:/site changes and the Master in C:/Master Inventory I would like to have a Macro that would match column A between worksheets and if the received file has any change cells (green) move the value of that cell to the corresponding cell in the master worksheet. The worksheets have up to 37 columns and could have a thousand rows or so. The received worksheets typically have 5 to 75 changed rows. -- Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy specific data from master workbook to another workbook | Excel Programming | |||
Copy worksheet from one workbook to a master workbook | Excel Worksheet Functions | |||
Copy Worksheets Into Master Workbook | Excel Programming | |||
copy data from one worksheet to identical sheet in different workbook | Excel Discussion (Misc queries) | |||
Replacing workbook with master copy | Excel Programming |