Incremental selection and data export to csv using VBA

[cathairs]

Hi guys,

I am a very new user to VBA and am trying to utilise a macro to help with a batch export from a very large dataset.

The dataset is 1872 columns wide and 1285 rows. What I am trying to achieve is to do a batch selection and export to csv of the dataset. I am trying to select columns 1:6 and export as a csv, then select 7:12 and export as a csv etc until the end of the dataset.

So the end result is that I should have 312 csv files.

I have tried to record a macro to complete the process for the first csv, but am unsure how to then increment by data selection by 6 columns and loop it until the end.

if anyone has some tips or comments it would be really appreciated.

My code is below:

Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("A:F").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ChDir "C:\Users\Glen\AppData\Roaming\Microsoft\Templates"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Glen\AppData\Roaming\Microsoft\Templates\EMN_001.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
End Sub

 

[Sektor]

Sheets(1) is the sheet with data.
Sheets(2) - must be second sheet from left.
Feel free to change names and location to suit your structure.

Sub Macro2()

    Dim i, c
    Application.ScreenUpdating = False    
    For c = 1 To 1872 Step 6
        i = i + 1
        Sheets(1).Cells(1, c).Resize(1285, 6).Copy Sheets(2).Cells(1)
        Application.CutCopyMode = False
        Sheets(2).SaveAs Filename:= _
            "C:\Users\Glen\AppData\Roaming\Microsoft\Templates\EMN_00" & i & ".csv", FileFormat:=xlCSV
    Next
    
End Sub

 

[MARK858]

A slightly different take on the code Sektor posted.
Sheet layout is the same as in Sektor's code.

Sub ToCsv2()
    Dim i As Long, c As Long, j As String, wb As Workbook, lr As Long
    Application.ScreenUpdating = False
    
    For c = 1 To Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column Step 6
        
        i = i + 1
        lr = ThisWorkbook.Sheets(1).Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
        
        ThisWorkbook.Sheets(1).Cells(1, c).Resize(lr, 6).Copy ThisWorkbook.Sheets(2).Cells(1)
        ThisWorkbook.Sheets(2).Copy

        Set wb = ActiveWorkbook
        
        With wb
            .SaveAs Filename:= _
                    "C:\Users\Glen\AppData\Roaming\Microsoft\Templates\EMN_" & Format(i, "000") & ".csv", FileFormat:=xlCSV
            .Close False
        End With
    
    Next
    Application.ScreenUpdating = True
End Sub

 

[cathairs]

Hi Sektor and MARK858,

I just wanted to thank you both very much for your code. I have tried both instances and they work perfectly.

Thanks very much for your time and help. This is such a good online community on this forum.

I'm learning a lot about VBA now and will continue working on more code!

Thanks again to both of you.