Hello everyone,
I would greatly appreciate some assistance with a simple code I cobbled together and works well. It consists of two macros. The first one copies single columns to a new sheet in the workbook. The second macro exports the created sheets as separate csv files. I would like to extend this so that I can export every three columns to a new sheet, so that I can create separate csv files containing these three columns. Here's an image of a worksheet with the column format.
Thanks,
Ian
I would greatly appreciate some assistance with a simple code I cobbled together and works well. It consists of two macros. The first one copies single columns to a new sheet in the workbook. The second macro exports the created sheets as separate csv files. I would like to extend this so that I can export every three columns to a new sheet, so that I can create separate csv files containing these three columns. Here's an image of a worksheet with the column format.
VBA Code:
Sub ColsToSheets()
Dim sh As Worksheet, sh1 As String, i As Long
sh1 = ActiveSheet.Name
Application.ScreenUpdating = False
For i = 1 To 263 '<---- currently for first 263 columns. Change as required
If Not Evaluate("isref(" & Sheets(sh1).Cells(1, i).Value & "!A1)") Then Sheets.Add(, Sheets(Sheets.Count)).Name = Sheets(sh1).Cells(1, i).Value
With Sheets(sh1)
.Range(.Cells(1, i), .Cells(.Cells(.Rows.Count, i).End(xlUp).Row, i)).Copy ActiveSheet.Range("A1")
End With
Next i
Sheets(sh1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xcsvFile = ThisWorkbook.Path & "\" & xWs.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub
Thanks,
Ian