How do I execute looping copies in Excel VBA?

Question

I'm a real novice with VBA and I'm trying to create a copy/paste loop to automate a task at work. I've been trying this for a few days and been unsuccessful so far, so need a little bit of help!

So the fundamentals are as follows. I've got two static lists , one containing 6 strings (static1) and one containing 37 strings (static2). I need these displayed in a specific format to allow an import into another program with a fixed import spec. I need static1 to repeat itself in blocks of 6 which I do with the following code:

Sheet3.Range("I1:I6").Copy
    For i = 0 To totalChannels
    Cells(1 + 6 * i, 2).Select
ActiveSheet.Paste

I understand fully how this works, static1 is held in cells I1:I6, and it's simply copied and pasted again and again until it reaches the maximum amount specified by totalChannels.

static2 is a little more complicated. static2 is contained in cells G1:G37 I need to create a loop within a loop to copy cell G1 and paste it into 312 sequential rows, then copy cell G2 and paste it into 312 sequential rows and so on, until it gets to G37.

I've been gradually educating myself on vba but this is ahead of where I'm currently at and I can't find a source online to explain this to me in a simple fashion - hoping someone on here can help!!

I hope I've explained everything thorough enough,

thanks.

You should take a look at this post on **[How to avoid using .Select](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select)** — simpLE MAn, Mar 26 '14 at 16:21

score 4 · Accepted Answer · answered Mar 26 '14 at 16:47

4

Sequential actions are more of an exercise in imagination than anything else. There are many ways to do what you want.

For example, if you want to copy A1:A5 to B1:B25 in sequence, the following works simply enough without a for-loop.

Sub RapidFireOne()

    Dim SrcRng As Range
    Set SrcRng = Sheet1.Range("A1:A5")

    'Copy in one go to Column B.
    SrcRng.Copy Sheet1.Range("B1:B25")

End Sub

Result:

enter image description here

If you really want to do it via loop and you want to do it across a sequence of ranges, use the Step property, like so:

Sub RapidFireTwo()

    Dim SrcRng As Range
    Set SrcRng = Sheet1.Range("A1:A5")

    'Copy repeatedly to Column B with one space between.
    For Iter = 1 To 25 Step 5
        SrcRng.Copy Sheet1.Range("B" & Iter)
    Next

End Sub

Result is same as the first one.

If you want to copy each cell to batch ranges like what you're doing with static2, the following method works, just modify it (basically changing 5 to 317 or somesuch):

Sub RapidFireThree()

    'This one copies each cell sequentially.
    Dim SrcRng As Range
    Set SrcRng = Sheet1.Range("A1:A5")

    'Copy each cell in order to Column B.
    Set TargetRng = Sheet1.Range("B1:B5")
    For Each Cell In SrcRng
        Cell.Copy TargetRng
        Set TargetRng = TargetRng.Offset(5, 0).Resize(5, 1)
    Next

End Sub

Result:

enter image description here

Let us know if this helps.

answered Mar 26 '14 at 16:47

WGS

13,969
4
48
51

To add more flexibility, in your second example you could change `For Iter = 1 To 25 Step 5` by `For Iter = 1 To 25 Step SrcRng.Rows.Count`. And btw, great answer (+1). – simpLE MAn Mar 26 '14 at 17:25
that looks absolutely perfect - thanks. haven't got a chance to implement it into my code now as I'm just heading out of the door but I'll give it a bash in the morning - thanks!! – roastbeeef Mar 26 '14 at 17:30
+1 for the Step command - I wasn't familiar with that one - that's great! – Mar 26 '14 at 19:44
1

+1 (actually I've already upvoted this answer), there is a little issue I've discovered recently: this code pastes values only in used part of sheet: `SrcRng.Copy Sheet1.Range("B1:B25")` actually it's gives you the same result as `SrcRng.Copy Intersect(Sheet1.Range("B1:B25"),Sheet1.UsedRange)`. You can see how I resolved this issue here: http://stackoverflow.com/questions/22510230/copying-few-values-in-a-row-and-pasting-it-multiple-times-in-the-same-row-in-exc/22510920#22510920 – Dmitry Pavliv Mar 26 '14 at 22:33
@simoco: I seem to remember this. It goes hand in hand with another issue of `UsedRange` being limited to a certain number of rows as well. I think you also raised that issue. Anyway, good to note, as OP seems to have a large amount of range to perform actions on. – WGS Mar 26 '14 at 22:37
@Nanashi I've used it with great success, however, how would I define (using the existing code) how many times to repeat what I'm copy/pasting? For example, in your second answer, A1 to A5 is repeating itself a total of 5 times, if i wanted it to repeat it 10 times how would i adapt this code? been trying this for two hours and cant solve it myself!! many thanks, beef – roastbeeef Apr 10 '14 at 14:59
By 10 times, do you mean extend further than row 25? Then just change the end number `25` to whatever is the last row you want to copy it to. In the second answer's case, if I want to copy ten times to column B, I'd change it to `For Iter = 1 to 50 Step 5`. Obviously, the end value should be your `Step * No. of times to repeat`. If this seems complicated, let me know. – WGS Apr 10 '14 at 15:12
@Nanashi sorry I meant for rapidfirethree() - there isnt a step function. – roastbeeef Apr 10 '14 at 15:21
Hm. So you want to repeat each value in `A1:A5` 10 times instead of 5? Change this line `Set TargetRng = TargetRng.Offset(5, 0).Resize(5, 1)` to `Set TargetRng = TargetRng.Offset(10, 0).Resize(10, 1)`. – WGS Apr 10 '14 at 15:30
you know what, i was looking at something completely different. I had confused myself and was trying to combine solution 2 with solution 3, it actually turns out that all i needed was your second solution. thanks again. – roastbeeef Apr 10 '14 at 15:36

score 1 · Answer 2 · answered Mar 26 '14 at 16:44

This code should help you accomplish this task. Doing a loop within another loop is fairly simple but you should try and avoid it where you can. This code for example takes O(n^2) timing (http://en.wikipedia.org/wiki/Big_O_notation) which means that if either static 1 or static 2 grows in size then the performance will degrade twice as quickly. In programming you always want to shoot for O(1) or static timing (meaning the process will run the same regardless of the amount of data you feed through it.

Also please take notice of the comments I listed. You can accomplish this task in much less code because VBA is a scripting language that lets you cheat a lot on syntax. But developing good coding practices will save you an enormous amount of pain in the long run.

Good Luck! VBA is a great language to begin learning programming!

Sub Test()
'Always explicitly define your variables
Dim oStatic2 As Range
Dim oWksht As Excel.Worksheet
Dim srow As Integer
Dim ct As Integer
Dim pstrow As Integer
Dim column As String

'Use 'SET' when setting object instances; primitive variables don't require it
Set oWksht = ThisWorkbook.Sheets("Sheet1")
Set static2 = oWksht.Range("G1:G37")

'set placemark
pstrow = 1

'set column to paste into
column = "A"

'Loop through each cell in static2
For srow = 1 To 37
    'Set individual cell to be copied
    Set static2 = oWksht.Range("G" & srow)
    static2.Copy
    'Loop through 312 sequential rows for each single row in the outer loop
    For ct = pstrow To (pstrow + 312)
        oWksht.Range(column & ct).PasteSpecial
        pstrow = pstrow + 1
    Next
Next

'Always release memory for objects after finished using
Set oWksht = Nothing
Set static2 = Nothing

End Sub

How do I execute looping copies in Excel VBA?

2 Answers2

Linked