Hi,
I was hoping someone could help with some VB code. I'm sure it will be really simple for all the experts out there, but VB is a mystery to me.
I would like to make a table that lists data from the tab named "PasteInternetDataHere" into the "DistrictContacts" tab. You can see from the example I have values in row 2 and 3, along with formulas in row 4.
What I would like is to have a macro:
1. Copy the formulas from A4:H4 to A5:H5.
2. Paste the formulas as values in row 4, leaving the formulas in row 5.
The next time I run the macro, I would like the formulas be copied to row 6 while pasting the formulas as values in row 5. Then row 7, and so on...
Basically, wherever we find the last row of data, add the formulas to the row below it and make the previous row as values.
Thanks for your help,
Pete
DistrictContacts
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 103px"><COL style="WIDTH: 71px"><COL style="WIDTH: 68px"><COL style="WIDTH: 38px"><COL style="WIDTH: 42px"><COL style="WIDTH: 190px"><COL style="WIDTH: 48px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>District</TD><TD>Name</TD><TD>Address</TD><TD>City</TD><TD>State</TD><TD>ZIP</TD><TD>Email</TD><TD>Format</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Madison</TD><TD>Bill Smith</TD><TD>PO Box 1</TD><TD>Madison</TD><TD>WI</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">fake@email.com</TD><TD>pdf</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Milwaukee</TD><TD>John Doe</TD><TD>PO Box 2</TD><TD>Milwaukee</TD><TD>WI</TD><TD style="TEXT-ALIGN: right">54321</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">abc@email.com</TD><TD>pdf</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Green Bay</TD><TD>Dave Jones</TD><TD>PO Box 3</TD><TD>Phillips</TD><TD>WI</TD><TD style="TEXT-ALIGN: right">54555</TD><TD>aaa@aaa.com</TD><TD>pdf</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A4</TD><TD>=PasteInternetPageHere!$J$14</TD></TR><TR><TD>B4</TD><TD>=PasteInternetPageHere!$J$4</TD></TR><TR><TD>C4</TD><TD>=PasteInternetPageHere!$J$6</TD></TR><TR><TD>D4</TD><TD>=PasteInternetPageHere!$J$8</TD></TR><TR><TD>E4</TD><TD>=PasteInternetPageHere!$J$10</TD></TR><TR><TD>F4</TD><TD>=PasteInternetPageHere!$L$10</TD></TR><TR><TD>G4</TD><TD>=PasteInternetPageHere!$J$12</TD></TR><TR><TD>H4</TD><TD>=PasteInternetPageHere!$J$16</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
I was hoping someone could help with some VB code. I'm sure it will be really simple for all the experts out there, but VB is a mystery to me.
I would like to make a table that lists data from the tab named "PasteInternetDataHere" into the "DistrictContacts" tab. You can see from the example I have values in row 2 and 3, along with formulas in row 4.
What I would like is to have a macro:
1. Copy the formulas from A4:H4 to A5:H5.
2. Paste the formulas as values in row 4, leaving the formulas in row 5.
The next time I run the macro, I would like the formulas be copied to row 6 while pasting the formulas as values in row 5. Then row 7, and so on...
Basically, wherever we find the last row of data, add the formulas to the row below it and make the previous row as values.
Thanks for your help,
Pete
DistrictContacts
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 104px"><COL style="WIDTH: 103px"><COL style="WIDTH: 71px"><COL style="WIDTH: 68px"><COL style="WIDTH: 38px"><COL style="WIDTH: 42px"><COL style="WIDTH: 190px"><COL style="WIDTH: 48px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>District</TD><TD>Name</TD><TD>Address</TD><TD>City</TD><TD>State</TD><TD>ZIP</TD><TD>Email</TD><TD>Format</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Madison</TD><TD>Bill Smith</TD><TD>PO Box 1</TD><TD>Madison</TD><TD>WI</TD><TD style="TEXT-ALIGN: right">12345</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">fake@email.com</TD><TD>pdf</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Milwaukee</TD><TD>John Doe</TD><TD>PO Box 2</TD><TD>Milwaukee</TD><TD>WI</TD><TD style="TEXT-ALIGN: right">54321</TD><TD style="COLOR: #0000ff; TEXT-DECORATION: underline">abc@email.com</TD><TD>pdf</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Green Bay</TD><TD>Dave Jones</TD><TD>PO Box 3</TD><TD>Phillips</TD><TD>WI</TD><TD style="TEXT-ALIGN: right">54555</TD><TD>aaa@aaa.com</TD><TD>pdf</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A4</TD><TD>=PasteInternetPageHere!$J$14</TD></TR><TR><TD>B4</TD><TD>=PasteInternetPageHere!$J$4</TD></TR><TR><TD>C4</TD><TD>=PasteInternetPageHere!$J$6</TD></TR><TR><TD>D4</TD><TD>=PasteInternetPageHere!$J$8</TD></TR><TR><TD>E4</TD><TD>=PasteInternetPageHere!$J$10</TD></TR><TR><TD>F4</TD><TD>=PasteInternetPageHere!$L$10</TD></TR><TR><TD>G4</TD><TD>=PasteInternetPageHere!$J$12</TD></TR><TR><TD>H4</TD><TD>=PasteInternetPageHere!$J$16</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>