Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to fill to next field with data
I recorded the macro below to copy "Director" down to the next not null
field. Then it copies each of the values in the next column using the same logic, copy to the next ''not null'' field or through the last null field. The macro recorded the cell values. Is there a way to tell it to go to the last null cell in that column? I'd like to use the macro each month and the number of rows will be different. Thank you, Mary Sub Director() ' ' Director Macro ' ' Keyboard Shortcut: Ctrl+l ' Cells.Find(What:="Director", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A1226").Select ActiveSheet.Paste Selection.End(xlUp).Select ActiveCell.Offset(1, 1).Range("A1").Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A93").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A8").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A75").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A6").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A93").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A7").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A30").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A5").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A20").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A10").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A25").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A37").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A49").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A63").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A8").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A71").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A2").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A4").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A6").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A5").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A48").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A18").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A2").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A9").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A28").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A8").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A71").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A11").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A94").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A20").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A75").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A47").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A11").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A9").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A16").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A6").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A6").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A6").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A27").Select ActiveSheet.Paste Selection.End(xlDown).Select Application.CutCopyMode = False Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Range("A1:A5").Select ActiveSheet.Paste Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select Cells.Find(What:="Director Total", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select Selection.TextToColumns Destination:=ActiveCell, DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to fill to next field with data
This is something similar (as I understand it). I have some users
who, after manipulating copies of pivot tables, want to fill the empty cells under each grouping. I welcome any critique to this approach Sub Copy_down2() 'Copy cell above to current cell and empty cells below ' make the range wider by adjusting the column offset, where 0 = current Dim rng_frm As Range, rng_to As Range, intCO As Integer, intLC As Long intCO = 0 ' column offset intLC = ActiveSheet.Rows.Count Do While ActiveCell.Row < intLC If IsEmpty(ActiveCell) Then Set rng_frm = Range(Cells(ActiveCell.Row - 1, ActiveCell.Column).Address _ & ":" & Cells(ActiveCell.Row - 1, ActiveCell.Column + intCO).Address) Set rng_to = Range(Cells(ActiveCell.Row, ActiveCell.Column).Address _ & ":" & IIf(IsEmpty(ActiveCell(2)), (Cells (ActiveCell.End(xlDown).Row - 1, ActiveCell.Column).Address), (Cells (ActiveCell.Row, ActiveCell.Column).Address))) rng_frm.Copy rng_to Cells(ActiveCell.End(xlDown).Row, ActiveCell.Column).Select End If ActiveCell(2).Select If ActiveCell.End(xlDown).Row = intLC Then Exit Sub End If Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Take part data from two fields and auto fill into 3rd field | Excel Worksheet Functions | |||
Using Macro to fill up data in coulmn | Excel Programming | |||
Fill in field | Excel Discussion (Misc queries) | |||
Macro to Copy data from e-mail and paste it into a field on a website | Excel Programming | |||
Macro Auto-Fill Field. | Excel Programming |