Cut and paste columns to the bottom of a different column with VB

[lmoran89]

There is a monthly report run, where the number of columns are the same each month, but the amount of data (rows) is different

Columns A - AI are from the monthly report run
A and B are Key fields that will need to be copied to each group of columns
Beginning with column C, every 3 columns are a group (e.g.columns C-E)
Columns A-B need to be copied and inserted before each group of columns (e.g. before column F, then before the next group, which shifts, so it would be column L, and so on). I've got this working in VB

Then each column group, which is now 5 columns (A-E, F-J, etc) needs to be cut and pasted at the last row of data in columns A-E. Because the amount of data (rows) is different each month this is causing me trouble creating a script

Any assistance is appreciated

 

[JackDanIce]

Why not stack the columns first into C:E, then copy A:B to each section?

 

[lmoran89]

Why not stack the columns first into C:E, then copy A:B to each section?

Either way. I still have the issue with cutting and pasting the columns

 

[Joe4]

Keep in mind the questions I am asking below refer to your virgin data before you do anything to it.
You say that the number of rows for each month is unknown, but are columns A and B ALWAYS populated for every record?
What I am getting at, is if we look at the last populated cell in column A, will that always tell us the last row?
Column A wouldn't be blank for any populated row of data, right?

Also, since you said you already did the inserting of all your columns, you could save me the trouble of figuring out what the new last column is after the insertion.
Which column is it?

 

[lmoran89]

Thanks in advance

Yes, column A and B are always populated. And yes, your assumption is correct, the last populated cell in column A will always be the last row.

The new last column is AR

 

[Joe4]

Columns A - AI are from the monthly report run
A and B are Key fields that will need to be copied to each group of columns
Beginning with column C, every 3 columns are a group (e.g.columns C-E)
Columns A-B need to be copied and inserted before each group of columns (e.g. before column F, then before the next group, which shifts, so it would be column L, and so on). I've got this working in VB

The new last column is AR

That didn't seem quite right to me, so I doubled check, and if I insert two column after every block of three columns, I come up with column BC as the new last column.

Also, I forgot to ask. Does your data start on row 1, or do you have any headers or anything?
I am trying to figure out the size of the data to copy, so we need to know what row to start copying from.

 

[lmoran89]

You're correct. I was looking at an older file. No headers, data begins on row 1

 

[Joe4]

See if this does what you want (this is to run after your first macro to insert all the columns, which you said you already did):

Sub MyDataMove()

    Dim er As Long
    Dim i As Long
  
    Application.ScreenUpdating = False
  
'   Find last row in column A with data
    er = Cells(Rows.Count, "A").End(xlUp).Row
      
'   Loop through each block
    For i = 1 To 10
'       Copy block of data
        Range(Cells(1, (i * 5) + 1), Cells(er, (i * 5) + 5)).Cut
        Cells((i * er) + 1, "A").Select
        ActiveSheet.Paste
    Next i
          
    Application.ScreenUpdating = True
  
    MsgBox "Data move complete!"
  
End Sub

 

[JackDanIce]

Either way. I still have the issue with cutting and pasting the columns

WRT to @Joe4, this assumes you haven't run your first macro that inserts columns. Instead, it stacks data into columns C:E and then copies A:B contents and assumption used is row 1 is not a header.

Save a backup of your file, then test and try :

Sub MyDataMove_1()
    
    Dim y   As Long
    Dim LR  As Long
    Dim i   As Long
        
    LR = Cells(Rows.Count, 1).End(xlUp).row
    y = Cells(1, Columns.Count).End(xlToLeft).Column
    i = LR + 1
    
    Application.ScreenUpdating = False
    
    For x = 6 To y Step 3
        With Cells(i, 1)
            .Offset(, 2).Resize(LR, 3).Value = Cells(1, x).Resize(LR, 3).Value
            .Resize(LR, 2).Value = Cells(1, 1).Resize(LR, 2).Value
        End With
        i = Cells(Rows.Count, 3).End(xlUp).row + 1
    Next x
    
    Cells(1, 6).Resize(i, y - 5).Value = ""
    
    Application.ScreenUpdating = True
    
    MsgBox "Data reshape complete", vbOKOnly + vbInformation, "Data Moved"
    
End Sub

 

[lmoran89]

Both worked flawlessly. Thank you both so much!