Forum Discussion
Automatically transferring, organizing, and analyzing sets of data from one csv file into an xlsm
wicked1591 Have you tried Power Query? Specifically the Combine Binaries option? You can save your .csv files to a single folder, then use PQ to import and append the data to the reporting workbook. As you add new files, just refresh, and PQ will update only the newest additions. PQ will also append the filename to each dataset, so that will allow you to make your differentiation between them.
It will eliminate copy/pasting, and you can create summaries with PivotTables and charts. If you add a slicer for dataset, then you can quickly switch views.
HTH
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?
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
