Copy/Paste variable number rows (autofill)

Question

I want to copy and paste a few things in one code, difficultly for me: I cannot select a variable row count. I will paste the entire code, but same problem appears 3 times. I want to select a cell, pastevalue it (first part @ "M25") than autofill to the last row where there is fill in the table (reference is for example the lenght of fills in the "L" column), so it should count those filled rows in the table and not stop @ M34 in my code now, as the rows can be more than that.

I know i should use something from this topic: Autofill with a dynamic range

but i cannot figure out how to start at the 25th row and then onwards to where the fills in K stop..

Range("B14").Select
Selection.Copy
Range("M25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("M25").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range**("M25:M34")**
Range("M25:M34").Select
Range("N25").Select
ActiveCell.FormulaR1C1 = _
    "=INDEX(CLIENTS!C[-8],MATCH(CONTROL!R14C2,CLIENTS!C1,0))"
Range("N25").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("N25:N34")
Range("N25:N34").Select
Range("O25").Select
ActiveCell.FormulaR1C1 = _
    "=INDEX(CLIENTS!C[-8],MATCH(CONTROL!R14C2,CLIENTS!C1,0))"
Range("O25").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("O25:O34")
Range("A25:O25").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

code probably looks ugly, as i just started VBA and try to do much with the record function

thanks

part of new code:

Range("B14").Select
Selection.Copy
Range("M25").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("M25").Select
Cells(25, 13).Resize(Cells(Rows.Count, 14).End(xlUp).Row - 25).Value = _
Range("M25:M" & Cells(Rows.Count, 14).End(xlUp).Row).Value
Range("N25").Select

score 2 · Accepted Answer · 2016-04-27T22:19:14.257

2

Use something like this.

Cells(25, 13).Resize(Cells(Rows.Count, 14).End(xlUp).Row - 25).Value = _
Range("N25:N" & Cells(Rows.Count, 14).End(xlUp).Row).Value

That will copy down to the last row for you.

To do this with a range of formulas, use this.

Cells(25, 13).Resize(Cells(Rows.Count, 14).End(xlUp).Row - 25).Formula = _
    Range("N25:N" & Cells(Rows.Count, 14).End(xlUp).Row).Formula

edited Apr 27 '16 at 22:19

answered Apr 27 '16 at 12:26

thanks, one q: where should i put your input? Selection.AutoFill Destination:=Range("M25:M34") in stead of the range("M25:M34") ? – bart1701 Apr 27 '16 at 13:08
Put it is place of select copy and paste lines, this one line will select and past the values into the desired range. See my updated answer for formulas. – Apr 27 '16 at 22:20
sorry to bother again, but still doesnt work? what am i doing wrong? pls see my original post for my new code – bart1701 Apr 28 '16 at 09:51

Copy/Paste variable number rows (autofill)

1 Answers1