change all specific row references to next row available in fill downformula

[J.W. Aldridge]

Was using this code to fill down each column with formulas in G4:I4.
(Headers in G5:I5)

Need to change because there may be existing data in columns G6:I6
down.

Want to change code to fill down formulas starting at next row
avaialble (G:I)

To sum it all up, I want to change all row 6 references in code to
next row available in that column.

Sub filldowndata()
'
' Macro1 Macro
' Macro recorded 3/17/2009 by FTN

Sheets("data").Select
Range("G4:l4").Select
Selection.Copy
Range("G6").Select
ActiveSheet.Paste

With ThisWorkbook.Worksheets("data")
Set rngData = .Range("f6:f" & .Cells(.Rows.Count, "f").End
(xlUp).Row)
Set rngFormula = .Range("g6")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With

With ThisWorkbook.Worksheets("data")
Set rngData = .Range("g6:g" & .Cells(.Rows.Count, "g").End
(xlUp).Row)
Set rngFormula = .Range("h6")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With

With ThisWorkbook.Worksheets("data")
Set rngData = .Range("h6:h" & .Cells(.Rows.Count, "h").End
(xlUp).Row)
Set rngFormula = .Range("i6")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With

With ThisWorkbook.Worksheets("data")
Set rngData = .Range("i6:i" & .Cells(.Rows.Count, "i").End
(xlUp).Row)
Set rngFormula = .Range("j6")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With
With ThisWorkbook.Worksheets("data")
Set rngData = .Range("j6:j" & .Cells(.Rows.Count, "j").End
(xlUp).Row)
Set rngFormula = .Range("k6")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With
With ThisWorkbook.Worksheets("data")
Set rngData = .Range("k6:k" & .Cells(.Rows.Count, "k").End
(xlUp).Row)
Set rngFormula = .Range("l6")
rngFormula.AutoFill _
Destination:=.Range(rngFormula, _
.Cells(rngData.Rows(rngData.Rows.Count).Row,
rngFormula.Column))
End With

Range("G6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Application.Goto Selection.Cells(1)

'Range("a6").Select
'Range(Selection, Selection.End(xlDown)).Select
Sheets("rollup").Select
Application.Run "AllWorksheetPivots"
Application.Run "Memo1"


End Sub

 

[Bernie Deitrick]

This will copy the formulas in G4:I4 to the bottom of G:I, and then conver the formulas to values

Sub Filldowndata2()
With Sheets("data")
.Range("G4:I4").Copy .Cells(Rows.Count, 7).End(xlUp)(2)
With .Cells(Rows.Count, 7).End(xlUp).Resize(1, 3)
.Value = .Value
End With
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[J.W. Aldridge]

thanx!