You can split an Excel file with a single column containing many records by using the Text-to-Columns feature. Here's how:
Alternatively, if you want to split the column into separate worksheets by each record, you can use the Macro recorder:
Copy and paste the following macro code:
Sub SplitByRecord()
Dim lastRow As Long, i As Long, j As Long Dim currentRecord As String Dim currentSheet As Worksheet
'Get the last row in the current column lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Loop through each row and split into separate sheets For i = 1 To lastRow 'Get the current record currentRecord = Cells(i, 1).Value
'Add a new sheet for the current record
Set currentSheet = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
currentSheet.Name = currentRecord
'Copy the record to the new sheet
Range("A" & i).Copy currentSheet.Range("A1")
Next i
End Sub
Press F5 to run the macro.
This macro will create a new sheet for each record in your column and copy the record to the new sheet.
Asked: 2022-08-22 11:00:00 +0000
Seen: 9 times
Last updated: Apr 27 '22