In the past week, I’ve been asked two similar questions about using VBA to copy multiple values from one sheet to another. More specifically, both people wanted to learn how to copy certain values in a particular order.

Since these are common questions, this is a great opportunity to teach you one way to do it. This Q&A session will show you how to copy every Nth cell in a range to a different sheet by using the Step keyword and a counter inside a VBA For Loop.

Quick Jump
Q1: Copy every Nth cell?
Q2: Copy values to another sheet with last value fixed in cell A100?

Q1: How do I pull data from every Nth cell of one sheet and copy it to consecutive cells in another sheet?

The questioner wanted to copy every 76th cell from one sheet to a second sheet, but he wanted the values placed in back-to-back cells on the second sheet.

The besy way to do this using a VBA macro is to use the Step keyword inside a For Loop. To make the values paste in consecutive cells, you would have a counter that increments by 1 inside the for loop.

Here’s an example of a macro that will solve the problem:

Sub CopyNthData()
Dim i As Long, icount As Long
Dim ilastrow As Long
Dim wsFrom As Worksheet, wsTo As Worksheet

Set wsFrom = Sheets("Sheet2")
Set wsTo = Sheets("Sheet1")
ilastrow = wsFrom.Range("B100000").End(xlUp).Row
icount = 1

For i = 1 To ilastrow Step 76
    wsTo.Range("B" & icount) = wsFrom.Range("B" & i) 'change formula to fit your needs
    icount = icount + 1
Next i
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

This macro takes every 76th cell, starting with row 1, in column “B” of worksheet Sheet2 and copies the values to column “B” of worksheet Sheet1. The worksheet names are stored in variables wsFrom and wsTo.

The icount variable controls what row the values get copied to. In the above example, it starts at row 1, then row 2, then row 3, and so on.

If you want to capture every 10th cell instead of every 76th, change the integer beside the Step keyword in the For loop. If you want to start copying values from row 2 instead of row 1, change the “1” in the For i = 1... line

Change the column from column “B” if you’re interested in different columns and change the source and destination sheets by modifying the variables wsFrom and wsTo.

Q2: I have values in Column "A" of Sheet1. How do I copy them to Sheet2 if I always want the last value in Sheet1 to appear in cell "A100" of Sheet2?

The user wanted the last value in Sheet1 to appear in cell “A100” of Sheet2, regardless of how many rows of data were entered on Sheet1.

Let’s say the person asking the question had data in cells A1 through A15 of Sheet1. The value in cell A15 of Sheet1 would go in cell A100 of Sheet2. Subsequently, the value in cell A14 of Sheet1 would go in cell A99 of Sheet2. Then, A13 of Sheet1 would be copied to A98 of Sheet2, and the pattern would continue until the last value in Sheet1 is copied over to Sheet2.

This a variation of Question 1, so the VBA answer will look quite similar.

Sub CopyData()
Dim i As Long, icount As Long
Dim ilastrow As Long
Dim wsFrom As Worksheet, wsTo As Worksheet

Set wsFrom = Sheets("Sheet1")
Set wsTo = Sheets("Sheet2")
ilastrow = wsFrom.Range("A100000").End(xlUp).Row
icount = 100

If ilastrow > icount Then
    MsgBox "Too many entries to do what you want."
    Exit Sub
End If

For i = ilastrow To 1 Step -1
    wsTo.Range("A" & icount) = wsFrom.Range("A" & i)
    icount = icount - 1
Next i
End Sub

How does this work? First, the macro finds the last row in worksheet wsFrom and stores the location in the variable ilastrow.

Just like in Question 1, the variable icount governs where on worksheet wsTo the values will be pasted. To answer the reader’s question, we start it at 100.

After some error checks, the macro starts at the last row and steps backward to the first cell. That’s the big difference between the two examples. Both the counter, icount, and the For loop step backward in the answer to this question.

Notice, the person asking the question was very specific about where he wanted the last value placed - cell A100. That’s why I included the error check. If there are more than 100 entries in Column “A” of Sheet1, the person won’t be able to copy ALL the values to the new sheet. A message box will popup informing the user of this limitation, and the macro will abort,

Final Thoughts

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.