Copy Formula from one worksheet to another and fill down

G

Guest

Thank you to everyone for there help in the past. I have found this forum
very helpful and have recommended it to dozens of people.

My latest project requires my to do a simple cut/paste to multiple
worksheets and fill down.

Here are the details.
The first 8 rows of each sheet would be header information
Row 9 -->??? contains the actual data.
This data is contained in columns A:M

Starting in Column P9:Z9, I have created formulas that retrieve data from
the header and body.

P9 --> =$F$1
Q9 --> =$F$4
R9 --> =$F$3
S9 --> = $C$9
T9 --> = trim(C9)
U9 --> = D9
V9 --> = E9
W9 --> = $j$9
X9 --> = trim(J9)
Y9 --> = K9
Z9 --> = L9

No problem so far. When these formulas are fill down the $ stay constant.

So, after all of that here is what I need to do:

I would like to take the formulas found in p9:Z9 and copy to each worksheet
for all rows that contain data (after row 9)
Each workbook could contain any number of worksheets
and each worksheet can contain any number of records (rows).

I would like to create a macro to perform this task.
Any help is appreciated.

Thanks
Scott
 
G

Guest

Here is my code so far:
Sub Macro1()
'
' Macro1 Macro
'

'
Range("P9").Select
ActiveCell.FormulaR1C1 = "=R1C6"
Range("Q9").Select
ActiveCell.FormulaR1C1 = "=R4C6"
Range("R9").Select
ActiveCell.FormulaR1C1 = "=R3C6"
Range("S9").Select
ActiveCell.FormulaR1C1 = "=R9C3"
Range("T9").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-17])"
Range("U9").Select
ActiveCell.FormulaR1C1 = "=RC[-17]"
Range("V9").Select
ActiveCell.FormulaR1C1 = "=RC[-17]"
Range("W9").Select
ActiveCell.FormulaR1C1 = "=R9C10"
Range("X9").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-14])"
Range("Y9").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("Z9").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("P9:Z5391").Select
Selection.FillDown
Range("P9").Select
End Sub

This macro needs to be changed to accomplish these two things:
1) Range("p9:Z5391") needs to be changed so that Z5391 is Z+last row based
on Column C.

2) Apply this macro to all sheets in the workbook.

Thanks in advance.
 
D

Dave Peterson

Option Explicit
Sub Macro1()

Dim LastRow as long
dim Wks as worksheet

for each wks in activeworkbook.worksheets

with wks
Lastrow = .cells(.rows.count,"C").end(xlup).row

.range("p9:p" & lastrow).FormulaR1C1 = "=R1C6"
.range("q9:q" & lastrow).FormulaR1C1 = "=R4C6"
.range("r9:r" & lastrow).FormulaR1C1 = "=R3C6"
.range("s9:s" & lastrow).FormulaR1C1 = "=R9C3"
.range("t9:t" & lastrow).FormulaR1C1 = "=TRIM(RC[-17])"
.range("U9:u" & lastrow).FormulaR1C1 = "=RC[-17]"
.range("V9:v" & lastrow).FormulaR1C1 = "=RC[-17]"
.range("w9:w" & lastrow).formulaR1C1 = "=R9C10"
.range("x9:x" & lastrow).FormulaR1C1 = "=TRIM(RC[-14])"
.range("y9:Y" & lastrow).FormulaR1C1 = "=RC[-14]"
.range("z9:z" & lastrow).formulaR1C1 = "=RC[-14]"
end with
next wks
End Sub

You can plop the formula into all the cells in each column--instead of filling
down.

Untested--so watch out for typos.

Scott said:
Here is my code so far:
Sub Macro1()
'
' Macro1 Macro
'

'
Range("P9").Select
ActiveCell.FormulaR1C1 = "=R1C6"
Range("Q9").Select
ActiveCell.FormulaR1C1 = "=R4C6"
Range("R9").Select
ActiveCell.FormulaR1C1 = "=R3C6"
Range("S9").Select
ActiveCell.FormulaR1C1 = "=R9C3"
Range("T9").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-17])"
Range("U9").Select
ActiveCell.FormulaR1C1 = "=RC[-17]"
Range("V9").Select
ActiveCell.FormulaR1C1 = "=RC[-17]"
Range("W9").Select
ActiveCell.FormulaR1C1 = "=R9C10"
Range("X9").Select
ActiveCell.FormulaR1C1 = "=TRIM(RC[-14])"
Range("Y9").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("Z9").Select
ActiveCell.FormulaR1C1 = "=RC[-14]"
Range("P9:Z5391").Select
Selection.FillDown
Range("P9").Select
End Sub

This macro needs to be changed to accomplish these two things:
1) Range("p9:Z5391") needs to be changed so that Z5391 is Z+last row based
on Column C.

2) Apply this macro to all sheets in the workbook.

Thanks in advance.

Scott Campbell said:
Thank you to everyone for there help in the past. I have found this forum
very helpful and have recommended it to dozens of people.

My latest project requires my to do a simple cut/paste to multiple
worksheets and fill down.

Here are the details.
The first 8 rows of each sheet would be header information
Row 9 -->??? contains the actual data.
This data is contained in columns A:M

Starting in Column P9:Z9, I have created formulas that retrieve data from
the header and body.

P9 --> =$F$1
Q9 --> =$F$4
R9 --> =$F$3
S9 --> = $C$9
T9 --> = trim(C9)
U9 --> = D9
V9 --> = E9
W9 --> = $j$9
X9 --> = trim(J9)
Y9 --> = K9
Z9 --> = L9

No problem so far. When these formulas are fill down the $ stay constant.

So, after all of that here is what I need to do:

I would like to take the formulas found in p9:Z9 and copy to each worksheet
for all rows that contain data (after row 9)
Each workbook could contain any number of worksheets
and each worksheet can contain any number of records (rows).

I would like to create a macro to perform this task.
Any help is appreciated.

Thanks
Scott
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

shorten IF Statements 3
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Copying template from one worksheet to another 2
Copy and Paste from one sheet to another 3
Copy rows to another worksheet 9
Copy information from one table to another table 1
Copy/Move Worksheet from one Workbook to another 3
MACRO for moving rows from one worksheet to another 3

Top