VBA: Copy to new sheet with a twist poss Array required

  • Need to copy from one sheet to another with a twist –

    Always Sheet 1 Column A to Sheet 2 Columns A B and C


    Sheet 1 A9, A21, A33, A45 to Sheet 2 A1, A2, A3, A4
    Sheet 1 A13, A25, A37, A49 to Sheet 2 B1, B2, B3, B4
    Sheet 1 A17, A29, A41, A53 to Sheet 2 C1, C2, C3, C4

    The cells are many rows say 500 to 5000, so need to repeat to end of data in Sheet1 and transfer to sheet 2, I would prefer VBA solution if possible. I can do via formula and VBA input formulas but looking for routine to transfer the cells over if at all possible! As speed will be un-usally critical for me on this one - thus the post.

    The data falls into say same locations so the same cells address will follow sequence IE

    Next will be:

    Sheet 1 A45, A57 to Sheet 2 A5, A6
    Sheet 1 A61, A73 to Sheet 2 B5, B6
    Sheet 1 A65, A77 to Sheet 2 C5, C6

    And so on to end of data range, I hope I have explained OK to understand this one

    Many thanks guys

    Jack in the UK

  • Jack,

    I started work on a solution that places the values into a two dimensional array, but then I had to go to work :barf: .

    Hopefully will have something for you a bit later after I get home (if I can get the damn thing working).


  • Hi Jack

    Have a look at the attached - I hope its close to what you want.


    The code is

    Sub CopySpecial()

    Dim c
    Dim d

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each c In Selection


    d = (c.Row + 3) Mod 12
    If d = 0 Then
    Sheets("Sheet2").Range("A" & Int((c.Row / 12)) + 1).Value = c.Value
    End If

    d = (c.Row - 1) Mod 12
    If d = 0 Then
    Sheets("Sheet2").Range("b" & Int((c.Row / 12))).Value = c.Value
    End If

    d = (c.Row - 5) Mod 12
    If d = 0 Then
    Sheets("Sheet2").Range("c" & Int((c.Row / 12))).Value = c.Value
    End If

    Next c

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False

    End Sub

    There are three types of people in this world.
    Those who can count and those who can't.

  • Hi there Jack, Had a bit of time this afternoon so thought I'd have a go:

    Hope this is something like what your after, see attached sample.

    (Think sample is too large too attache, made over 5000 rows of data but it works pretty quick)

  • Hi Guys

    Well what do i say?

    It is in times like this i sit and wonder how on earth such help is available out there [here] on OzGrid, I’m a happy man. I have 100% success from the array codes i requested.

    All I can say is a very big thank you to both friends Pesky Weasel and Insomniac – thank you guys, than you very much.

    Kindest possible regards

    Jack in the UK

  • Sorry Jack,

    I had misread the post but the code adapts easily enough. Try the following entering 13 in the input box as the data series is reapeated every 13th cell.


  • Sorry Guys,

    I was using a really dumb set of test data which only looked like it was getting the job done. :0ops:

    This should do it....maybe........ :dance3:


    Edit: I now get that you only want to group every 4th row (mind like a steel trap & quicker than greased lightning - me), which insomniacs code does admirably, whereas mine will group all rows. Great job insomniac - still not a completely wasted effort though - I can see what I have written coming in handy some day (just not today :biggrin: ).

  • Pesky Weasel, that's pretty nice solution, although you are transposing all the data it could be modified to transfer only the rows Jack want's.
    Both ways are lightning fast, but yours is fractionally faster than mine!
    I'm sure Jack would be keen for a modified version.

  • Insomniac,

    I cant get my head around this. The only way I can think of displaying the data required is to then pass the individual columns from the 2D Array into 3 seperate 1D Arrays, which is essentailly what you have done only without the step of creating the 2D Array in the first place. As a consequence displaying less data will make my code take longer.
    Do you see another way around this- Im only fairly new to arrays having only declared one for the first time a couple of weeks ago- and Im still coming to terms with them (but WOW aint they quicker!!!!)


  • Pesky Weasel,

    Modified your code to work the same as mine, the slow part of my code was applying the array back to sheet2, your line:
    Sheets(2).Range("a1").Resize(iDiv, iElem).Value = data2 is a far better way.

  • Great Job Insomniac, should satisfy Jacks speed requirement. I really must get more into this area, I cant believe the speed advantages.


  • Hi guys

    Many thanks for all your efforts – I must admit I post very few questions this is one I thought would be extremely interesting to many OzGrid members. For my part Arrays I understand why we use then and the speed advantages, but sadly I can not get my head around that at all, thus the post. Speed is never an issue with me this one was. The reply’s here are rich and worthy of great thanks and the re try are so important as well so we can all learn, again my thanks

    Yes extremely fast and I have a working version so now more play and test all the updates so kindly offered here – thank you.

    Kindest possible regards


  • Hi guys,

    Interesting thread and a good solution as well :)

    I just want to add that when we need to redimension (which is not good but necessary) arrays XL seems to handle arrays faster if the arrays are first dimensioned like data2(1,3) and then
    ReDim data2(iDiv, 3).

    For the really real arrays-neard the data-variabel should of course be sliced instead of looping...

    Kind regards,

  • Hiya Dennis,

    Hows things up North today?

    Just on your post:

    So if you give the array some dummy dimensions first before ReDimming it calculates faster?



    For the really real arrays-neard the data-variabel should of course be sliced instead of looping...

    What is slicing? and how would it apply to this example?


  • Hi Weasel,

    We got the first snow last week so now we can skii until end of april/may next year :biggrin:

    Q1: Yes, at least what I have found out.

    Q2: Oops, it was not my intention to swing around words no one is familiar with...

    Here is a link to some really nice array-slicings (remember it´s only for real array-neards) :biggrin:


    Take care and all the best from,


  • Good Skiing!!!!!!!!

    Geez, your not wrong about the array nerds bit! I need to seriously upgrade my pocket protector and maybe even need to repair my glasses with sticky plaster to read this stuff......

    But I will muddle through it as it sounds like a solution (albeit a complicated one) to the ultimate answer that I wanted to achieve on this post - which was to fill a two D array and then return subsets of the data based on either of the dimensions.

    Thanks Dennis

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!