Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fill cell references from several worksheets in one? | Excel Worksheet Functions | |||
Fill down with references to worksheets | Excel Worksheet Functions | |||
Max to specific references | Excel Worksheet Functions | |||
Auto Fill with some references locked | Excel Discussion (Misc queries) | |||
Auto fill references | Excel Programming |