Array for copying

Question

I need to copy data, but since the amount of data that needs copying is going to be pretty large, and filled in by hand, I would like to do that by using a macro.

I can't understand how Arrays work.

This gives me the right amount of copies, but only of the very last cell I need copied.

Sub copyer()

Dim fromH As Integer    'fromheight
Dim fromW As Integer    'fromwidth

Dim toH As Integer      'to height
Dim toW As Integer      'to width

Dim counter As Integer
counter = Worksheets("blad1").Range("D3").Value 'amount of filled-in data lines to copy

Dim Times As Integer    'number of times to run the loop, depending on the filled in data
Times = counter + 1

Dim tostart As Integer  'location where to start placing the data
                        'depending on how much data is already present
Dim toend As Integer    'location up to where to place

tostart = Sheets("blad2").Range("L1").Value + 1 '(+2 if theres a header)
toend = tostart + counter

Dim Copy As Integer

For Copy = 1 To Times
For toH = tostart To toend
    For toW = 1 To 2
        For fromH = 12 To 22 Step 2
            For fromW = 1 To 26 Step 25
                Sheets("blad2").Cells(toH, toW).Value = _
                Sheets("blad1").Cells(fromH, fromW).Value
            Next fromW
        Next fromH
    Next toW
Next toH
Next Copy

'this macro needs to copy the data

'from blad1
'from height 12 to 22 (steps of 2)(10 times)
'from width 1 and 26 (not the cells inbetween)

'to blad2
'to height depending on the data present (dim tostart)'till height needed (steps of 1)
'to width 1 and 2

'blad1, D3 holds the input-datacounter
'blad2, L1 holds the output-datacounter

End Sub

It's not really clear what you're trying to do. Seems overcomplicated, that you would have a 5x nested `For` loop. Have you stepped through the code using F8 to manually walk through the process and see where it's diverging from your expectations? If not, that should be your very first step in debugging. — David Zemens, Jun 03 '15 at 16:18
my answer [here](http://stackoverflow.com/questions/14035772/how-to-assign-an-excel-range-to-a-2d-array/14060707#14060707) should help you in copying a range to and from an array. — SeanC, Jun 03 '15 at 16:29

score 0 · Answer 1 · answered Jun 03 '15 at 16:51

As Zemens mentioned, you've really overcomplicated this one! To copy across an array, you only need as many for loops as there are dimensions in the array. So two in this case! I've used some simple arithmetic to translate from the one sheet to the other. It might not be perfect but will hopefully get you started!

Dim intRowLoop As Integer, intColLoop As Integer

For intRowLoop = tostart To toend
    For intColLoop = 1 To 2
        Sheets("blad2").Cells(intRowLoop, intColLoop).Value = _
            Sheets("blad1").Cells(12 + ((intRowLoop - tostart) * 2), fromW * 26).Value
    Next intColLoop
Next intRowLoop

score 0 · Answer 2 · answered Jun 08 '15 at 17:56

In the end I went with;

(the sheetnames are diffrent as the one above was a test to get it working)

(yust took the important bit)

For ToH = tostart To toend Step 2
For ToW = 2 To 8 Step 6
        Sheets("factuur2").Cells(ToH, ToW).Value = _
        Sheets("multiprijs").Cells(FromH, FromW).Value
    FromW = 28
Next ToW
FromH = FromH + 2
ToW = 2
FromW = 5
Next ToH

However the problem I have with this method is that the "width" variables here can only hold 2 values as is;

Width + something 'or
Width to something 'at a regular interval

What I would like, to make this future-proof is width; 1 , 3 , 5 , 19 , anything

for both the from-and-to width

Ty both for helping me get this right though :)

Array for copying

2 Answers2