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

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

change all specific row references to next row available in fill down formula
 

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" wrote in message
...
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

change all specific row references to next row available in filldown formula
 

thanx!