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.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2022-08-22 11:00:00 +0000
Seen: 9 times
Last updated: Apr 27 '22
How can I use VBA to bring a csv file into MSAccess?
How to use VBA to make a Gantt chart that includes timestamps for day, hour, and minute?
How can new worksheets be automatically generated and saved according to the date?
Can Visual Basic be used to code the Grep command?
How can I use VBA to create a border around cells that have text or a value in them?
How to rearrange slides using Excel VBA?
How can I use Excel VBA to transfer information from an Oracle table to an Excel worksheet?