Simple VBA Macro

[aeddipa]

Hey guys,

I don't know if its the lack of sleep or lack of coffee in my system, but I can't figure out this relatively easy macro. Any help would be greatly appreciated.

I have two sheets of data on stocks. The second sheet contains some of the same stocks as on the first sheet. I have to bring the market value from the second sheet over to the first and add it with the market value on the 1st sheet. This will get me the sum of market values.

I have done this successfully, however, after doing this, I want to copy paste special the formulas to values and then delete the rows in the 2nd sheet that were used in sheet 1.

This will leave me with any data that is sepearate from sheet 1. I then want to sort the remaining stocks so that they are in order, copy and paste them into the bottom of sheet 1 in the format of sheet 1.

Here are some pictures to help.

Sheet 1:

Excel Workbook
	A	B	C	D	E
1	Underlying Stocks / Account Holdings	Ticker	Morningstar Mkt Value ($)	Advent Mkt Value	Sum of Mkt Value
2	Microsoft Corporation	MSFT	560,543.60	763,756.00	$1,324,299.60
3	J.P. Morgan Chase & Co.	JPM	500,892.08	442,857.00	$943,749.08
4	ExxonMobil Corporation	XOM	857,048.80	0	$857,048.80
5	Wells Fargo Company	WFC	419,440.05	322,617.00	$742,057.05
morningstar

Sheet 2:

advent

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 299px"><COL style="WIDTH: 95px"><COL style="WIDTH: 64px"></COLGROUP><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD></TD><TD>Security</TD><TD>Market</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Security</TD><TD>Symbol</TD><TD>Value</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>--------------------</TD><TD>------------</TD><TD>----------------</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD>AGILENT TECHNOLOGIES INC COM</TD><TD>a</TD><TD style="TEXT-ALIGN: right">195733</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>APPLE INC COM</TD><TD>aapl</TD><TD style="TEXT-ALIGN: right">241718</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>ABBOTT LABS COM</TD><TD>abt</TD><TD style="TEXT-ALIGN: right">102039</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>BARRICK GOLD CORP COM</TD><TD>abx</TD><TD style="TEXT-ALIGN: right">518418</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>ADOBE SYS INC COM</TD><TD>adbe</TD><TD style="TEXT-ALIGN: right">124172</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD>ANALOG DEVICES INC COM</TD><TD>adi</TD><TD style="TEXT-ALIGN: right">61670</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>AGNICO EAGLE MINES LTD COM</TD><TD>aem</TD><TD style="TEXT-ALIGN: right">31857</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>AES CORP COM</TD><TD>aes</TD><TD style="TEXT-ALIGN: right">40600</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>AETNA INC NEW COM</TD><TD>aet</TD><TD style="TEXT-ALIGN: right">238488</TD></TR></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



If someone could help me out with this it would be greatly appreciated. I just can't figure out the link in VBA to delete the rows in sheet 2 once they have been used on sheet 1.

Thanks!



EDIT:

Obviously in this example no symbol matches, but in the real data there are matches.