StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSplit ExcelSheet after separator
text
Body
copied!<p>I have an Excel file, in the first sheet I have on <code>column A</code> some text delimited by a separator, like this:</p> <pre><code>Column A -------- Text line 1.1 Text line 1.2 Text line 1.3 *** Text line 2.1 Text line 2.2 Text line 2.3 *** Text line 3.1 </code></pre> <p>I like to split the content after the <code>***</code> separator and put each piece in a separate file with only one sheet. The name of the files should be the first line of the each section. I need to be able to copy with the formatting, colors, etc.</p> <p>This is the function but is not copying the formatting...</p> <pre><code>Private Function AImport(ThisWorkbook As Workbook) As Boolean Dim height As Long Dim fileName As String Dim startLine As Long Dim endLine As Long Dim tmpWs As Worksheet Dim AnError As Boolean With ThisWorkbook.Worksheets(1) 'sheet name "Sheet1" height = .Cells(.rows.Count, 2).End(xlUp).row startLine = 6 nr = 1 For i = startLine + 1 To height If InStr(.Cells(i, 2).Value, "***") > 0 Then separate = i a = Format(nr, "00000") fileName = "File" & a endLine = separate - 1 .rows(startLine & ":" & endLine).Copy Set tmpWs = ThisWorkbook.Worksheets.Add tmpWs.Paste tmpWs.Select tmpWs.Copy Application.DisplayAlerts = False ActiveWorkbook.SaveAs fileName:=ThisWorkbook.path & "\Output\" & fileName & " .xls", FileFormat:=xlExcel8, CreateBackup:=False 'xlOpenXMLWorkbookMacroEnabled ActiveWorkbook.Close tmpWs.Delete 'update next start line startLine = separate + 1 nr = nr + 1 End If Next i End With If AnError Then MsgBox "Errors detected in " & ThisWorkbook.Name & "! Check LogFile.txt file for details. Execution stopped!", vbExclamation, inputWb.Name AImport = False Else: Application.StatusBar = "Workbook check succesfully completed. Executing macro..." AImport = True End If ThisWorkbook.Close End Function </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload