Forum Discussion

wicked1591's avatar
wicked1591
Copper Contributor
Aug 21, 2019

Smitty Smith  Thanks for the reply. I don't think power query is right for me because all of my data is coming from one csv file, and I don't really have a time efficient way to separate the data sets into different files.

I have done some thinking to simplify the problem though, and this is what I really need:

 

I want to take 68 cells by 3 columns from the csv, copy that, and paste it in a xls file every 4th column.

So, it would take cells A1:C69 and copy them to A1 in another sheet. Then, cells A70:C137 would copy to A4 in the other sheet. The problem I have is that this example only transfers 2 data sets. I know how to copy these specific cells, but what I don't know how to do and what to do is have it function for an indefinite number of cells. So every 68x3 cells, copy into the 4th column over in the same row.

 

Is there a VBA function that would take X cells and cut/copy them to every Yth column?

Smitty Smith's avatar
Smitty Smith
Iron Contributor
Aug 21, 2019

wicked1591 You might need to play with the offsets a bit, but this should get you started:

 

Sub foo()
'   Copy from
    '   A1:C69
    '   A70: C137, etc
    
    Dim i As Long
    Dim x As Long
    
    Dim lr As Long
    Dim lc As Long
    
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    x = 69
    
    For i = 1 To lr Step 69
        lc = Cells(1, Columns.Count).End(xlToLeft).Offset(, 2).Column
        Range("A" & i & ":C" & x).Copy Cells(1, lc)
        x = x + 69
    Next i
        
End Sub

 

Resources