Copy Formula from one worksheet to another and fill down
 

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

Copy Formula from one worksheet to another and fill down
 

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" wrote:

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

Copy Formula from one worksheet to another and fill down
 

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 Campbell wrote:

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" wrote:

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


--

Dave Peterson