VBA to copy from one tab to another

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
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>
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
try this

Code:
Sub CopyRowFormula()
 Dim LR As Long, LC As Long, i As Long
Application.ScreenUpdating = False
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
   
    Rows(LR).Copy Destination:=Rows(LR + 1)
    Rows(LR).Copy
    Rows(LR).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top