Macro to fill to next field with data

M

Mary

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
 
O

OssieMac

Hi Mary,

Not sure what you mean by " Is there a way to tell it to go to the last null
cell in that column?" This would be the last cell in the column but I am sure
that is not what you mean.

Which row of the code are you referring to? Place some asterisks or some
method of identifying it above and below it like this?

***********************************
Range(Selection, Selection.End(xlDown)).Select
*************************************
 
M

Mary

The first and second columns have a lot of blank cells. I am trying to find a
quick way to autofill so every cell has a value. I don't want the macro to
copy to the bottom of the worksheet, only to the last cell that has no value,
similar to when I hold the control and shift key down. When I recorded the
macro, I put my cursor in the first blank cell, held the shift and control
keys and pressed the arrow down key. This took me to the next cell in that
column with a value. I arrow up one and paste. I'd like a macro to take
those actions for me because I have to do it so many times in the second
column.

I highlighted two cell references below where the recorded macro grabbed the
actual cell reference rather than code to grab the last blank cell.
 
O

OssieMac

Hi Mary,

I don't want to get involved in re-writing your recorded code as I would do
it but I think that replacing 2 lines of the macro with a single line should
work.

Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:****A93****").Select

Use following single line of code in lieu of the above 2 lines

Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).Select

The above line of code selects from the current ActiveCell which appears to
be your blank cell then down from the ActiveCell to the first cell with data
but offsets that back up one row.

As a little added extra, don't confuse ActiveCell with Selection. If only
one cell is selected then it is also the ActiveCell. If a range is Selected
then the ActiveCell is the one in the Selected block that is not highlighted
(usually the first).
 
T

timmg

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

File Name changes with dates and Deleting Rows of Zeros 0
Excel Macro to use cells as a autofilter 0
On error goto specified Line 6
Help me5 1
Loop to End 0
how can a macro be paused for input and then resume executio 2
variable number of rows. 2
VB Problem - Help! 3

Top