How to set copy range for i rows and set number of columns

Question

Right now my vba code has this snippet for copying Worksheets("Sheet1").Rows(i).Copy. I have i properly set up so that it will copy the rows I want. However, it is currently copying the entirety of all the specified rows. How can I have it continue to count rows but also only copy from say column A to Q? So a range of i rows from column A to Q

Scott Craner · Accepted Answer · 2019-09-27T14:34:05.060

1

A couple of ways:

Worksheets("Sheet1").Range(Worksheets("Sheet1").Cells(i,1),Worksheets("Sheet1").Cells(i,17)).Copy

Or

Worksheets("Sheet1").Range("A" & i & ":Q" & i).Copy

Or

Worksheets("Sheet1").Range("A" & i).Resize(,17).Copy

Depending on the size of the loop the first will be the quickest(See HERE). It can also be shortened with a With block:

With Worksheets("Sheet1")
     .Range(.Cells(i,1),.Cells(i,17)).Copy
End With

edited Sep 27 '19 at 14:34

answered Sep 27 '19 at 14:25

Scott Craner

148,073
10
49
81

Thanks, I got it now. – Forkinator9000 Sep 27 '19 at 14:31
@Forkinator9000 please consider marking as correct by clicking the check mark by the answer. – Scott Craner Sep 27 '19 at 15:26

How to set copy range for i rows and set number of columns

1 Answers1