Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup copy and update master workbook
Hello,
I have a bit of a problem. Once a week I receive updates from several sources that need to be input into my master workbook. I have twelve columns of data including two date formatted columns and one hidden column. The master workbook has anywhere from 1200 to 1500 rows. When I open the update workbooks with updated information all updates are in red. Is there a way using vba that after opening the updated workbook, I can have a macro in my personal workbook that would by the click of a button, open the password protected master workbook, lookup the id code located in column 4 and if the same id code column 4 of the update workbook has red font in columns 5 through 12 in the updated workbook, have it copy that entire row as it and paste it to the master workbook close and save the workbook. I will have as many as 200 to 300 changes form each source to update. If I could get help with this it would make life much easier for me and I would greatly appreciate it. By the way I have row one as the column header. All workbooks are formatted the same with the same type of data in all columns. Thanks in advance for your assistance and direction. -- By persisting in your path, though you forfeit the little, you gain the great. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup copy and update master workbook
Try this code. Change the location of the master workbook and the password.
Sub update_master() Masterbk = "c:\temp\protected.xls" Passwd = "123" Set Updatesht = ActiveWorkbook.ActiveSheet Set mstbk = Workbooks.Open(Filename:=Masterbk) mstbk.Unprotect Password:=Passwd Set mstsht = mstbk.Sheets("Sheet1") mstsht.Unprotect Password:=Passwd With Updatesht RowCount = 2 'skip header Do While .Range("E" & RowCount) < "" If .Range("E" & RowCount).Font.ColorIndex = 3 Then IdCode = .Range("D" & RowCount) Set c = mstsht.Columns(4).Find(what:=IdCode, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find Id Code : " & IdCode) Else .Rows(RowCount).Copy _ Destination:=Rows(c.Row) End If End If RowCount = RowCount + 1 Loop End With mstsht.Protect Password:=Passwd mstbk.Protect Password:=Passwd mstbk.Close SaveChanges:=True End Sub "DavidH56" wrote: Hello, I have a bit of a problem. Once a week I receive updates from several sources that need to be input into my master workbook. I have twelve columns of data including two date formatted columns and one hidden column. The master workbook has anywhere from 1200 to 1500 rows. When I open the update workbooks with updated information all updates are in red. Is there a way using vba that after opening the updated workbook, I can have a macro in my personal workbook that would by the click of a button, open the password protected master workbook, lookup the id code located in column 4 and if the same id code column 4 of the update workbook has red font in columns 5 through 12 in the updated workbook, have it copy that entire row as it and paste it to the master workbook close and save the workbook. I will have as many as 200 to 300 changes form each source to update. If I could get help with this it would make life much easier for me and I would greatly appreciate it. By the way I have row one as the column header. All workbooks are formatted the same with the same type of data in all columns. Thanks in advance for your assistance and direction. -- By persisting in your path, though you forfeit the little, you gain the great. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup copy and update master workbook
Thanks Joel for the quick response,
I tried using the code but I keep getting type mismatch error at this point: Masterbk = "J:\Temp\Master SVR.xls" -- By persisting in your path, though you forfeit the little, you gain the great. "Joel" wrote: Try this code. Change the location of the master workbook and the password. Sub update_master() Masterbk = "c:\temp\protected.xls" Passwd = "123" Set Updatesht = ActiveWorkbook.ActiveSheet Set mstbk = Workbooks.Open(Filename:=Masterbk) mstbk.Unprotect Password:=Passwd Set mstsht = mstbk.Sheets("Sheet1") mstsht.Unprotect Password:=Passwd With Updatesht RowCount = 2 'skip header Do While .Range("E" & RowCount) < "" If .Range("E" & RowCount).Font.ColorIndex = 3 Then IdCode = .Range("D" & RowCount) Set c = mstsht.Columns(4).Find(what:=IdCode, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find Id Code : " & IdCode) Else .Rows(RowCount).Copy _ Destination:=Rows(c.Row) End If End If RowCount = RowCount + 1 Loop End With mstsht.Protect Password:=Passwd mstbk.Protect Password:=Passwd mstbk.Close SaveChanges:=True End Sub "DavidH56" wrote: Hello, I have a bit of a problem. Once a week I receive updates from several sources that need to be input into my master workbook. I have twelve columns of data including two date formatted columns and one hidden column. The master workbook has anywhere from 1200 to 1500 rows. When I open the update workbooks with updated information all updates are in red. Is there a way using vba that after opening the updated workbook, I can have a macro in my personal workbook that would by the click of a button, open the password protected master workbook, lookup the id code located in column 4 and if the same id code column 4 of the update workbook has red font in columns 5 through 12 in the updated workbook, have it copy that entire row as it and paste it to the master workbook close and save the workbook. I will have as many as 200 to 300 changes form each source to update. If I could get help with this it would make life much easier for me and I would greatly appreciate it. By the way I have row one as the column header. All workbooks are formatted the same with the same type of data in all columns. Thanks in advance for your assistance and direction. -- By persisting in your path, though you forfeit the little, you gain the great. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup copy and update master workbook
Joel, Thanks again Joel for your assistance.
I was able to get the code to run by retyping the location of the master workbook once again. However, I'm prompted for the password although I've loaded it into the code. Also most importantly, columns 7 through 17 has the potential to have changes coded in red font so any of these rows need to be copied to replace the one in the master workbook with red font displayed as it is in the update workbook. Sorry I miscalculated the number of utilized columns at first. Thanks again so much for your assistance. -- By persisting in your path, though you forfeit the little, you gain the great. "DavidH56" wrote: Thanks Joel for the quick response, I tried using the code but I keep getting type mismatch error at this point: Masterbk = "J:\Temp\Master SVR.xls" -- By persisting in your path, though you forfeit the little, you gain the great. "Joel" wrote: Try this code. Change the location of the master workbook and the password. Sub update_master() Masterbk = "c:\temp\protected.xls" Passwd = "123" Set Updatesht = ActiveWorkbook.ActiveSheet Set mstbk = Workbooks.Open(Filename:=Masterbk) mstbk.Unprotect Password:=Passwd Set mstsht = mstbk.Sheets("Sheet1") mstsht.Unprotect Password:=Passwd With Updatesht RowCount = 2 'skip header Do While .Range("E" & RowCount) < "" If .Range("E" & RowCount).Font.ColorIndex = 3 Then IdCode = .Range("D" & RowCount) Set c = mstsht.Columns(4).Find(what:=IdCode, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find Id Code : " & IdCode) Else .Rows(RowCount).Copy _ Destination:=Rows(c.Row) End If End If RowCount = RowCount + 1 Loop End With mstsht.Protect Password:=Passwd mstbk.Protect Password:=Passwd mstbk.Close SaveChanges:=True End Sub "DavidH56" wrote: Hello, I have a bit of a problem. Once a week I receive updates from several sources that need to be input into my master workbook. I have twelve columns of data including two date formatted columns and one hidden column. The master workbook has anywhere from 1200 to 1500 rows. When I open the update workbooks with updated information all updates are in red. Is there a way using vba that after opening the updated workbook, I can have a macro in my personal workbook that would by the click of a button, open the password protected master workbook, lookup the id code located in column 4 and if the same id code column 4 of the update workbook has red font in columns 5 through 12 in the updated workbook, have it copy that entire row as it and paste it to the master workbook close and save the workbook. I will have as many as 200 to 300 changes form each source to update. If I could get help with this it would make life much easier for me and I would greatly appreciate it. By the way I have row one as the column header. All workbooks are formatted the same with the same type of data in all columns. Thanks in advance for your assistance and direction. -- By persisting in your path, though you forfeit the little, you gain the great. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup copy and update master workbook
I don't know what is wrong with the password asking for conformation. I
didn't have the problem. You may need to change the variable FirstCol. Your original posting was using column E (5th) now you are asking for G (7th). Sub update_master() Masterbk = "c:\temp\protected.xls" Passwd = "123" FirstCol = Range("G1").Column LastCol = Range("Q1").Column Set Updatesht = ActiveWorkbook.ActiveSheet Set mstbk = Workbooks.Open(Filename:=Masterbk) mstbk.Unprotect Password:=Passwd Set mstsht = mstbk.Sheets("Sheet1") mstsht.Unprotect Password:=Passwd FirstCol = Range("G1").Column LastCol = Range("Q1").Column With Updatesht RowCount = 2 'skip header Do While .cells(RowCount, FirstCol) < "" FoundChange = False For ColCount = FirstCol To LastCol If .Cells(RowCount, ColCount).Font.ColorIndex = 3 Then IdCode = .Range("D" & RowCount) FoundChange = True Exit For End If Next ColCount If FoundChange = True Then Set c = mstsht.Columns(4).Find(what:=IdCode, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find Id Code : " & IdCode) Else .Rows(RowCount).Copy _ Destination:=Rows(c.Row) End If End If RowCount = RowCount + 1 Loop End With mstsht.Protect Password:=Passwd mstbk.Protect Password:=Passwd mstbk.Close SaveChanges:=True End Sub "DavidH56" wrote: Joel, Thanks again Joel for your assistance. I was able to get the code to run by retyping the location of the master workbook once again. However, I'm prompted for the password although I've loaded it into the code. Also most importantly, columns 7 through 17 has the potential to have changes coded in red font so any of these rows need to be copied to replace the one in the master workbook with red font displayed as it is in the update workbook. Sorry I miscalculated the number of utilized columns at first. Thanks again so much for your assistance. -- By persisting in your path, though you forfeit the little, you gain the great. "DavidH56" wrote: Thanks Joel for the quick response, I tried using the code but I keep getting type mismatch error at this point: Masterbk = "J:\Temp\Master SVR.xls" -- By persisting in your path, though you forfeit the little, you gain the great. "Joel" wrote: Try this code. Change the location of the master workbook and the password. Sub update_master() Masterbk = "c:\temp\protected.xls" Passwd = "123" Set Updatesht = ActiveWorkbook.ActiveSheet Set mstbk = Workbooks.Open(Filename:=Masterbk) mstbk.Unprotect Password:=Passwd Set mstsht = mstbk.Sheets("Sheet1") mstsht.Unprotect Password:=Passwd With Updatesht RowCount = 2 'skip header Do While .Range("E" & RowCount) < "" If .Range("E" & RowCount).Font.ColorIndex = 3 Then IdCode = .Range("D" & RowCount) Set c = mstsht.Columns(4).Find(what:=IdCode, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then MsgBox ("Cannot find Id Code : " & IdCode) Else .Rows(RowCount).Copy _ Destination:=Rows(c.Row) End If End If RowCount = RowCount + 1 Loop End With mstsht.Protect Password:=Passwd mstbk.Protect Password:=Passwd mstbk.Close SaveChanges:=True End Sub "DavidH56" wrote: Hello, I have a bit of a problem. Once a week I receive updates from several sources that need to be input into my master workbook. I have twelve columns of data including two date formatted columns and one hidden column. The master workbook has anywhere from 1200 to 1500 rows. When I open the update workbooks with updated information all updates are in red. Is there a way using vba that after opening the updated workbook, I can have a macro in my personal workbook that would by the click of a button, open the password protected master workbook, lookup the id code located in column 4 and if the same id code column 4 of the update workbook has red font in columns 5 through 12 in the updated workbook, have it copy that entire row as it and paste it to the master workbook close and save the workbook. I will have as many as 200 to 300 changes form each source to update. If I could get help with this it would make life much easier for me and I would greatly appreciate it. By the way I have row one as the column header. All workbooks are formatted the same with the same type of data in all columns. Thanks in advance for your assistance and direction. -- By persisting in your path, though you forfeit the little, you gain the great. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheet from one workbook to a master workbook | Excel Worksheet Functions | |||
Update worksheet from Master workbook | Excel Discussion (Misc queries) | |||
Update worksheet from Master workbook | Excel Discussion (Misc queries) | |||
How to update a master workbook from 3 different slave workbooks? | Excel Discussion (Misc queries) | |||
Update master workbook from individual workbooks | Excel Discussion (Misc queries) |