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" <(E-Mail Removed)> wrote in message
news:a20c952d-adfe-4827-9820-(E-Mail Removed)...
> 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
|
Similar Threads
