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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change all specific row references to next row available in filldown formula
thanx!
|
Reply |
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 |