User Profile
lnjohnson
Copper Contributor
Joined 2 years ago
User Widgets
Recent Discussions
Re: Identify characters and split into different sheets
The formula should work without me telling it look for a specific string of data; something like the first three characters should work fine. I have over 19,000 lines with over 500 different variables. I can't spend time with a formula that i have manually tell it to look for a specific string of data. I hope that makes sense.2.3KViews0likes0CommentsRe: Identify characters and split into different sheets
NikolinoDE Thank you for responding. The idea is that the cell information starts the way you have listed in your document. Then the formula should divide the cell information into different sheets based on the first three characters of each cell. See attachment.2.3KViews0likes0CommentsRe: Identify characters and split into different sheets
NikolinoDE Maybe I'm missing something. I don't see any differences to the updated code that would split the data into different sheets. Everytime i run the macro it creates a new sheet with the entire range. Have you tried to run it with the example data i submitted, and if so, can you send me a screenshot?2.4KViews0likes5CommentsRe: Identify characters and split into different sheets
NikolinoDE Thanks for the code. So far it partly works, but it pulls all the cells in the source range to a new sheet instead of splitting them into different sheets by firstThreeChars. For example: ABC_123456 ABC_123457 ABC_123458 DEF18818 DEF18819 DEF18820 GHI207 GHI208 GHI209 GHI210 GHI211 Here's what I have entered for the code: Sub SplitCellsToSheets() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim sourceRange As Range Dim cell As Range Dim firstThreeChars As String Dim lastRow As Long ' Set the source sheet Set sourceSheet = ThisWorkbook.Sheets("ABC") ' Replace "Sheet1" with your source sheet name 'Set the source range lastRow = 19726 Set sourceRange = sourceSheet.Range("F2:F" & lastRow) ' Assuming the data is in column A ' Loop through each cell in the source range For Each cell In sourceRange ' Get the first three characters of the cell value firstThreeChars = Left(cell.Value, 3) ' Check if a sheet with the first three characters exists, create one if it doesn't On Error Resume Next Set targetSheet = ThisWorkbook.Sheets(firstThreeChars) On Error GoTo 0 If targetSheet Is Nothing Then Set targetSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) targetSheet.Name = firstThreeChars End If ' Copy the cell value to the target sheet targetSheet.Cells(targetSheet.Cells(targetSheet.Rows.Count, "F").End(xlUp).Row + 1, "F").Value = cell.Value Next cell End Sub2.5KViews0likes7Comments
Recent Blog Articles
No content to show