marco to copy formulas to rows

EmyrWilliams

New Member
Joined
Jul 3, 2014
Messages
4
Hey hey everyone,

I was wondering if anyone could help please, I'm trying to write a macro which will copy formulas from one sheet and copy into rows into another sheet.

Now here is where I'm having problems, I want to paste into a number of rows which have data in. But the number of rows can vary. Can anyone help please?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,317
Office Version
  1. 2010
Platform
  1. Windows
I was wondering if anyone could help please, I'm trying to write a macro which will copy formulas from one sheet and copy into rows into another sheet.

Now here is where I'm having problems, I want to paste into a number of rows which have data in. But the number of rows can vary. Can anyone help please?

You will need to explain in more detail what your setup is and what you are trying to do with it. I am particularly concerned about what you expect to happen with the data that is already in the cells you want to paste the formulas into, but some detail about the formula itself would be helpful (what sheet is it on, what is the formula, etc.).
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
This is a very limited example; It is a stand-alone example of what I think you are asking about. The Macro should fill Cells C2:C6 on Sheet2. See results at bottom of this screen. Hope this helps -- Good Luck Jim

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col A</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col B</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col C</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">199</td><td style="text-align: right;border-top: 1px solid black;;">476</td><td style="text-align: right;border-top: 1px solid black;;"> 94,724.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">114</td><td style="text-align: right;;">300</td><td style="text-align: right;;"> 34,200.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=A2*B2</td></tr></tbody></table></td></tr></table><br />

Your sheet2 BEFORE running the macro

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col A</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col B</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col C</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br />

Paste this Macro into a standard Module:

Code:
Sub Foo()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LR As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
With ws1
    .Range("C2").Copy
End With
With ws2
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("C2:C" & LR).PasteSpecial xlPasteFormulas
End With
Application.CutCopyMode = False
End Sub

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col A</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col B</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Col C</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">30</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=A2*B2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=A3*B3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=A4*B4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=A5*B5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=A6*B6</td></tr></tbody></table></td></tr></table><br />
 

EmyrWilliams

New Member
Joined
Jul 3, 2014
Messages
4
Hey Rick, sorry I'll try to go into a bit more detail without over complicating things:

I have one sheet with data (names and addresses and a few more columns) from coloumn AA onwards. which goes on for 5,000 ish rows
In another sheet I have different data (names and addresses and a few more columns) from coloumn AA onwards. which goes on for 3,000 ish rows.
I then have an other sheet with formulas (because want different combinations of full name and house name, full name - without initials - and housename etc, which I have already done) - so I can then do vlookups on another sheet.

So I want to copy and paste my formulas into the right number of rows. The complication comes as I want to use the file as a template where I can copy new data in (which will have different numbers of rows)

I did think think of just saying copy to 20,000 rows but that will slow things down too much.

Thanks for any help
 

EmyrWilliams

New Member
Joined
Jul 3, 2014
Messages
4

ADVERTISEMENT

Jim... you are a beautiful, beautiful man.

Thank you very much :)
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471
Thanks Emyr..

You are very kind. My illustration is a bit crude, but I had hoped it would help you along and get you closer to what you wanted... Jim
 

EmyrWilliams

New Member
Joined
Jul 3, 2014
Messages
4
Just want I wanted thanks, I'm new to all this, but I've managed to alter it to do what I needed.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,109,127
Messages
5,526,996
Members
409,733
Latest member
revender17

This Week's Hot Topics

Top