Solved

Compare two worksheets and insert row

Posted on 2013-01-04
8
406 Views
Last Modified: 2013-01-07
I have an excel 2010 workbook with a worksheet that pulls in data from an outside source (about 150 cost codes and descriptions).  On another worksheet in the same workbook I have data in the first two columns that was copied from the first sheet originally.  When a new query is run to update the first sheet, I would like to include code that would compare the two sheets row by row and update the second sheet by inserting rows if a new cost code is found.  

Please let me know if you need more information.
Thanks
0
Comment
Question by:acdecal
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 38745637
yes can you post a sample would be easier to apply code
gowflow
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 38747573
A before and After sample is also helpful.
and record a macro that has the steps you would use to manually create the desired result as this gives a idea of what you are trying to achieve.,
0
 

Author Comment

by:acdecal
ID: 38750666
OK, I attached a sample file and recorded a macro in module1.  The workbook is currently in a state that would result from running a new query where a row was added in the "Query Sheet" ws (row 12 in red).  The lists in columns A & B were previously identical to the lists in columns A & B in the "Related Data" ws.  I need code to run after the query to check for any changes in the "Query Sheet" and insert a corresponding row in the "Related Data" sheet.

Hope this helps clarify.
WB-Before.xlsm
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 29

Expert Comment

by:gowflow
ID: 38750794
Let me recap
You would get the workbook in this status ? like it is now like you posted ? and what you want to acheive is go thru the data in Querry Sheet and if it finds a code that does not exit in Related Data to simply insert it wher eit should be correct ???
gowflow
0
 

Author Comment

by:acdecal
ID: 38750808
Yes, but I need to insert a complete new row but only copy the code and description.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 38751134
Hi is this what you want ? I added some more missing codes to make sure we cover boundaries. Make sure your macroes are enabled and run the button and let me know if this is what you want I am coloring the code we can change that.
gowflow
WB-Before.xlsm
0
 

Author Comment

by:acdecal
ID: 38751572
OK great.  I think that will work.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38751607
Glad I could help.
gowflow
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question