Thread: Macro to fill to next field with data
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Mary Mary is offline
external usenet poster
  
Posts: 190
Default 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
Reply With QuoteReply With Quote