Copying cells down

[thepharcyde]

Right, compiling a basic macro.

Basically part of the procedure, copies a cell and then pastes it into A1 for example. Data to the right is present, so when you autofill down it fills it to the last visable data in column B all very good manually but......

When I write a macro to do this, when you fill down, the macro automactically populates to the fixed autofill when I wrote the macro data was present in B1:B12 for example using the autofill has worked no probs. But if I run the macro again and the spreadsheet has data in B1:B13, it only autofills to B12 as per the macro.

Is there anyway of changing the below so it fills as per whats on the sheet and not per fixed cells in the macro?:

Selection.AutoFill Destination:=Range("C18:C43")

Thank you

 

[koala]

You could try

= Range(Selection, Selection.End(xlDown)).Select

providing there are no blank cells

Koala

 

[thepharcyde]

basically if you look at this sheet, I need to autofill down from B18 (always starts here) and then down to B51, but taking into account that the rows are not always the same, so next sheet could have more transactions and go down to B53 for example. When you record the macro and do autofill it seems to lock to the recorded macros cell range

 

[Hazen]

I'm sure this forum is about to teach me a better way, but here is what I do for cases just like yours:

have VB go to the formula that you preset for autofill, B18 in your case
have VB insert a relative formula at B19 to do a counta from A18 to row 65536
now take that formula as a variable
now use that variable in the autofill - note that the relative formula will be over written, so auto cleanup
works nicely because you can use that variable for other functions like copy, select, etc

 

[thepharcyde]

it is not a formula. I need to insert a column in B copy account number in G4 and paste this in B18, then copy this down dependant on the number of rows

 

[Legacy 1363]

[B18].AutoFill Destination:=Range([B18], [A18].End(xlDown)(1, 2))

Or to copy and fill :-

[G4].Copy Range([B18], [A18].End(xlDown)(1, 2))

 

[Hazen]

oh, if not a formula then you don't even need autofill - try this:

Cells(1, 1).Select
Cells.Find(What:="Account", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
Acct = ActiveCell.Cells(2, 1)
Cells.Find(What:="Transaction ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Activate
ActiveCell.Cells(2, 1).FormulaR1C1 = "=COUNT(RC[-1]:R[65518]C[-1])"
DataCount = ActiveCell.Cells(2, 1) 'assumes no empty rows
If DataCount = 0 Then End 'to be safe - could also add a msg to tell user
Range(ActiveCell.Cells(2, 1), ActiveCell.Cells(DataCount + 1)) = Acct

 

[thepharcyde]

[B18].AutoFill Destination:=Range([B18], [A18].End(xlDown)(1, 2))

Or to copy and fill :-

[G4].Copy Range([B18], [A18].End(xlDown)(1, 2))

Mate you are a star exactly what I wanted to do.

Thank you everyone, great help.