CODEWITHSUNDEEP
vbaexcel
bart1701
bart1701

Reputation: 65

Copy/Paste variable number rows (autofill)

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

Upvotes: 0

Views: 190

Answers (1)

user6249873
user6249873

Reputation:

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

Upvotes: 2

Related Questions