marco to copy formulas to rows

[EmyrWilliams]

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?

 

[Rick Rothstein]

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]

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

Excel 2010
	A	B	C
1	Col A	Col B	Col C
2	199	476	94,724.00
3	114	300	34,200.00
Sheet1

Cell Formulas
Range	Formula
C2	=A2*B2

Your sheet2 BEFORE running the macro

Excel 2010
	A	B	C
1	Col A	Col B	Col C
2	1	2
3	2	3
4	3	4
5	4	5
6	5	6
Sheet2

Paste this Macro into a standard Module:

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

Excel 2010
	A	B	C
1	Col A	Col B	Col C
2	1	2	2
3	2	3	6
4	3	4	12
5	4	5	20
6	5	6	30
Sheet2

Cell Formulas
Range	Formula
C2	=A2*B2
C3	=A3*B3
C4	=A4*B4
C5	=A5*B5
C6	=A6*B6

 

[EmyrWilliams]

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]

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

Thank you very much

 

[jim may]

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]

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