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
|