VBA Loop to copy/paste blocks of columns into another worksheet with dynamic range

  • Hi everyone,

    I usually find my answer here but I've been stuck on this one for several days now.

    I'm working on 2 sheets, one with new data, and one with historical data.

    On each sheet I have 'blocks' of columns, those blocks are always 4 columns wide, with a blank column in-between. And have different row length.

    For example, there are data on column A to D, then a blank column, then data in column F to I, etc. as below:

    item 1
    item 2
    title 1
    title 2
    title 3
    title 4
    title 1
    title 2
    title 3

    Goal: Need to check if the item number (ex: Cell A1 for the first block) is equal to the item number on the destination sheet (same cell A1).

    Need to copy data from row 3 (first row with actual data) until the last row, for the first block of column, onto the 2nd sheet after the last row containing data. And create a loop for each block of columns until the last column of the source sheet.

    Issue: The blocks of columns have different row length. Same thing on the destination sheet, each block have different row length. Which is why I need to use a dynamic range, for each block.

    My code is here below, I managed to have it working for the first block of column by using the column letters, and try to modify it to use a dynamic range, but it's deleting all the data from row 3 on the destination sheet, and copying '#N/A' from "E3" until the very last column of the excel sheet (it does take into account the number of rows of the first block of column on the source sheet).

    There might be a better/easier way of doing it, than moving a block of columns at a time, but that's the only way I could think about.
    I'm fairly new to VBA, and would really appreciate any comments/feedback you may have.
    Thank you very much in advance! :)

  • Possibly...

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Hi dangelor ! Sorry for my late reply, I wasn't able to test the code until now. Thank you so much for your feedback, it does exactly what I needed to :). Do you reckon there would be a way to optimized the code without activate and select? Because when I run the macro on my full data set, it's a bit slow. I've tried using With sheet 1 instead of Sheet 1.Activate, but it doesn't seem to be working..

  • Hi dangelor actually I've just noticed something. I have to do the same operations on 2 sheets (both with 2 different source sheets). It works perfectly on one of them, that has the exact same format as the example I've shared.

    But on the other sheet, I noticed there are, for some columns, data on the first row for example C1, L1. So the copy/paste doesn't work properly. It paste the first block correctly, then paste the same data again below the data that has just been pasted 2 columns on the right, etc. and I'm not sure how to fix that issue.
    I attached an updated workbook example.Example.xlsm

  • I worked on the code and changed it as below, it seems to be working on my data. Let me know what you think

    I actually didn't know about the CurrentRegion and wasn't sure how to use Offset, so thank you so much for that

Participate now!

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