Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding row data from one Excel file, to another, when not existing
I have been wrestling with a problem for a few days, and have had varying
success, but I have officially given up, as I have gone far afield, I fear, to my original goal. Here is what I am trying to do: - One workbook, Two Worksheets (ML and SL) - SL has a subset of data of what is one ML - Users add info to ML, and thus, we then need to create a corresponding row on SL - ML and SL have what I would call a common "key" field in Column A (call it ID). Let say ML has rows with these IDs: 001 002 003 005 009 010 SL has rows with these IDs: 001 002 003 009 I want to create a VBA routine that will open up ML and do what I call a Vlookup back to SL, using ID. If ML.ID is not in SL, then I want to add a row of data in SL, starting with the ID. So, in above case, if I ran the routine, it would add 005, 009, and 010 to SL, if all worked as I want. Basically, it is a synchronization issue (but note, all other cells in the worksheets are different so a simply copy of all data won't work). As always, thanks all...this is the best source of expert help! Patk |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding row data from one Excel file, to another, when not existing
Sub Synchronization() With Sheets("SL") LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 End With With Sheets("ML") MLRowCount = 1 Do While .Range("A" & MLRowCount) < "" ML_ID = .Range("A" & MLRowCount) With Sheets("SL") Set c = .Columns("A").Find(what:=ML_ID, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Sheets("ML").Rows(MLRowCount).Copy _ Destination:=.Rows(NewRow) NewRow = NewRow + 1 End If End With MLRowCount = MLRowCount + 1 Loop End With End Sub "PatK" wrote: I have been wrestling with a problem for a few days, and have had varying success, but I have officially given up, as I have gone far afield, I fear, to my original goal. Here is what I am trying to do: - One workbook, Two Worksheets (ML and SL) - SL has a subset of data of what is one ML - Users add info to ML, and thus, we then need to create a corresponding row on SL - ML and SL have what I would call a common "key" field in Column A (call it ID). Let say ML has rows with these IDs: 001 002 003 005 009 010 SL has rows with these IDs: 001 002 003 009 I want to create a VBA routine that will open up ML and do what I call a Vlookup back to SL, using ID. If ML.ID is not in SL, then I want to add a row of data in SL, starting with the ID. So, in above case, if I ran the routine, it would add 005, 009, and 010 to SL, if all worked as I want. Basically, it is a synchronization issue (but note, all other cells in the worksheets are different so a simply copy of all data won't work). As always, thanks all...this is the best source of expert help! Patk |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding row data from one Excel file, to another, when not exis
From what I have been learning, boy...this looks like it should work. I
shall try it tomorrow and get back to you. I had similar logic, but was getting stuck on the Set C = .Column....... line as I had no idea what the parameters meant. I also think I was trying to get too fancy with ranges :-o More to come...thanks a MILLION Joel! Patk "Joel" wrote: Sub Synchronization() With Sheets("SL") LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 End With With Sheets("ML") MLRowCount = 1 Do While .Range("A" & MLRowCount) < "" ML_ID = .Range("A" & MLRowCount) With Sheets("SL") Set c = .Columns("A").Find(what:=ML_ID, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Sheets("ML").Rows(MLRowCount).Copy _ Destination:=.Rows(NewRow) NewRow = NewRow + 1 End If End With MLRowCount = MLRowCount + 1 Loop End With End Sub "PatK" wrote: I have been wrestling with a problem for a few days, and have had varying success, but I have officially given up, as I have gone far afield, I fear, to my original goal. Here is what I am trying to do: - One workbook, Two Worksheets (ML and SL) - SL has a subset of data of what is one ML - Users add info to ML, and thus, we then need to create a corresponding row on SL - ML and SL have what I would call a common "key" field in Column A (call it ID). Let say ML has rows with these IDs: 001 002 003 005 009 010 SL has rows with these IDs: 001 002 003 009 I want to create a VBA routine that will open up ML and do what I call a Vlookup back to SL, using ID. If ML.ID is not in SL, then I want to add a row of data in SL, starting with the ID. So, in above case, if I ran the routine, it would add 005, 009, and 010 to SL, if all worked as I want. Basically, it is a synchronization issue (but note, all other cells in the worksheets are different so a simply copy of all data won't work). As always, thanks all...this is the best source of expert help! Patk |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding row data from one Excel file, to another, when not exis
Sorry..couldn't wait. I gave it a shot and it worked quite well (so much
better than the NO PROGRESS I had been making. Here is the code, thus far. Only changes were for the actual WS names, which I abbreviated in the initial post. It definitely insert rows from one page to the other. I have to reduce the amount of data, because initially, all I wanted was the ML_ID to go over (I have another function that populates cells after column A). But man...this is so great...I shall do a bit of auditing, and let ya know...thanks Joel Patk Sub Synchronization() With Sheets("Sunset-Plan") LastRow = .Range("A" & Rows.Count).End(xlUp).Row NewRow = LastRow + 1 End With With Sheets("HPSC") MLRowCount = 1 Do While .Range("A" & MLRowCount) < "" ML_ID = .Range("A" & MLRowCount) With Sheets("Sunset-Plan") Set c = .Columns("A").Find(what:=ML_ID, LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then Sheets("HPSC").Rows(MLRowCount).Copy _ Destination:=.Rows(NewRow) NewRow = NewRow + 1 End If End With MLRowCount = MLRowCount + 1 Loop End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding and then sorting data into an already existing Excel 2007 w | Excel Worksheet Functions | |||
import data from txt file to an existing excel file | Excel Discussion (Misc queries) | |||
import data into existing excel file | Excel Discussion (Misc queries) | |||
Adding date to file name when saving and over writing a existing f | Excel Programming | |||
adding data to existing data and replacing old one in excel? | Excel Programming |