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.
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.
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.
This macro takes every 76th cell, starting with row 1, in column “B” of worksheet
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
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
Just like in Question 1, the variable
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,
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
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.