Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change from Numeric to Alpha
Hello from Steved
Below works as it should. Our IT section made changes, hence I need to ask your help please. At the moment it looks in Col A and uses the first numeric say 1, it moves all data associated with 1 to sheet1 which it created 2 to Sheet2 and so on. Ok I am not a code specialist so I require your help. Ok I will present 2 situations 1, Can the below be change to pickup the first 2 Char ie Sh, Sw which stands for "Shore", "Swanson" 2, I have cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke. Can this be done with the below please. Thankyou. Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, "Sheet" & Left (cell.Text, 1)) cell.EntireRow.Copy wksT.Columns("A").Cells(cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If (wks Is Nothing) Then Set wks = wkbW.Worksheets.Add(After:=Worksheets ("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change from Numeric to Alpha
Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, _ "Sheet" & Left(cell.Text, 2)) cell.EntireRow.Copy wksT.Columns("A").Cells(cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells _ (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If wks Is Nothing Then Set wks = wkbW.Worksheets.Add( _ After:=Worksheets("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below works as it should. Our IT section made changes, hence I need to ask your help please. At the moment it looks in Col A and uses the first numeric say 1, it moves all data associated with 1 to sheet1 which it created 2 to Sheet2 and so on. Ok I am not a code specialist so I require your help. Ok I will present 2 situations 1, Can the below be change to pickup the first 2 Char ie Sh, Sw which stands for "Shore", "Swanson" 2, I have cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke. Can this be done with the below please. Thankyou. Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, "Sheet" & Left (cell.Text, 1)) cell.EntireRow.Copy wksT.Columns("A").Cells(cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If (wks Is Nothing) Then Set wks = wkbW.Worksheets.Add(After:=Worksheets ("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change from Numeric to Alpha
Hello from Steved
Thankyou. Tom -----Original Message----- Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, _ "Sheet" & Left(cell.Text, 2)) cell.EntireRow.Copy wksT.Columns("A").Cells (cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells _ (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If wks Is Nothing Then Set wks = wkbW.Worksheets.Add( _ After:=Worksheets("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function -- Regards, Tom Ogilvy "Steved" wrote in message ... Hello from Steved Below works as it should. Our IT section made changes, hence I need to ask your help please. At the moment it looks in Col A and uses the first numeric say 1, it moves all data associated with 1 to sheet1 which it created 2 to Sheet2 and so on. Ok I am not a code specialist so I require your help. Ok I will present 2 situations 1, Can the below be change to pickup the first 2 Char ie Sh, Sw which stands for "Shore", "Swanson" 2, I have cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke. Can this be done with the below please. Thankyou. Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, "Sheet" & Left (cell.Text, 1)) cell.EntireRow.Copy wksT.Columns("A").Cells (cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If (wks Is Nothing) Then Set wks = wkbW.Worksheets.Add(After:=Worksheets ("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change from Numeric to Alpha
This statement - "Sheet" & Left(cell.Text, 1) - from the code makes a new
word using the word "Sheet" plus the first letter on the left of the text in the cell. A new sheet is created for each unique word created. If the cell has the word "Waiheke" in it, the statement above would make the string "SheetW". If you were to change it to this: "Sheet" & Left(cell.Text, 2) it would make a string using the word "Sheet" plus the first TWO characters on the left of the string in the cell. If the cell has the word "Waiheke" in it, the statement would make the string "SheetWa". Left(X, Y) returns Y characters from the left end of the string X. The "&" (ampersand) connects many words into one: "Sheet" & "Foo" becomes "SheetFoo", Sheet" & "_Foo" & "_Bar" become "Sheet_Foo_Bar" , etc. If the cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke were in the first column of the data worksheet, the sheets named SheetC, SheetR, SheetP, SheetW, SheetS, SheetO would be created and the data sorted accordingly. If you change Left(cell.Text, 1) to Left(cell.Text, 2), sheets named SheetCi, SheetRo, SheetPa, SheetWi, SheetSh, SheetOr, SheetSw, SheetWa would be created and the data sorted accordingly. If you replaced "Sheet" & Left(cell.Text, 1) with cell.Text, sheets named City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke would be created and each city would have its own sheet and only that city's data would appear on the sheet. Does this help? Bob "Steved" wrote in message ... Hello from Steved Below works as it should. Our IT section made changes, hence I need to ask your help please. At the moment it looks in Col A and uses the first numeric say 1, it moves all data associated with 1 to sheet1 which it created 2 to Sheet2 and so on. Ok I am not a code specialist so I require your help. Ok I will present 2 situations 1, Can the below be change to pickup the first 2 Char ie Sh, Sw which stands for "Shore", "Swanson" 2, I have cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke. Can this be done with the below please. Thankyou. Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, "Sheet" & Left (cell.Text, 1)) cell.EntireRow.Copy wksT.Columns("A").Cells(cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If (wks Is Nothing) Then Set wks = wkbW.Worksheets.Add(After:=Worksheets ("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change from Numeric to Alpha
Hello Bob from Steved
Thankyou it helps -----Original Message----- This statement - "Sheet" & Left(cell.Text, 1) - from the code makes a new word using the word "Sheet" plus the first letter on the left of the text in the cell. A new sheet is created for each unique word created. If the cell has the word "Waiheke" in it, the statement above would make the string "SheetW". If you were to change it to this: "Sheet" & Left(cell.Text, 2) it would make a string using the word "Sheet" plus the first TWO characters on the left of the string in the cell. If the cell has the word "Waiheke" in it, the statement would make the string "SheetWa". Left(X, Y) returns Y characters from the left end of the string X. The "&" (ampersand) connects many words into one: "Sheet" & "Foo" becomes "SheetFoo", Sheet" & "_Foo" & "_Bar" become "Sheet_Foo_Bar" , etc. If the cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke were in the first column of the data worksheet, the sheets named SheetC, SheetR, SheetP, SheetW, SheetS, SheetO would be created and the data sorted accordingly. If you change Left(cell.Text, 1) to Left(cell.Text, 2), sheets named SheetCi, SheetRo, SheetPa, SheetWi, SheetSh, SheetOr, SheetSw, SheetWa would be created and the data sorted accordingly. If you replaced "Sheet" & Left(cell.Text, 1) with cell.Text, sheets named City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke would be created and each city would have its own sheet and only that city's data would appear on the sheet. Does this help? Bob "Steved" wrote in message ... Hello from Steved Below works as it should. Our IT section made changes, hence I need to ask your help please. At the moment it looks in Col A and uses the first numeric say 1, it moves all data associated with 1 to sheet1 which it created 2 to Sheet2 and so on. Ok I am not a code specialist so I require your help. Ok I will present 2 situations 1, Can the below be change to pickup the first 2 Char ie Sh, Sw which stands for "Shore", "Swanson" 2, I have cities City, Roskill, Papakura, Wiri, Shore, Orewa, Swanson, Waiheke. Can this be done with the below please. Thankyou. Option Explicit Public Sub CopyRowsToSheetN() 'copies rows of data from sheet named 'Data' to sheet 'named 'Sheetn' where n is the first character of the text 'in the first cell if any. Creates Sheetn if necessary. Application.ScreenUpdating = False Dim cell As Range Dim rng As Range, oldSelection As Range Dim wks As Worksheet, wksT As Worksheet Set oldSelection = Selection Set wks = ThisWorkbook.Worksheets("Data") Set rng = Intersect(wks.Columns("A"), wks.UsedRange) 'copies the row to the new sheet at the current row For Each cell In rng.Cells If Len(cell.Text) 0 Then Set wksT = GetWorksheet(wks.Parent, "Sheet" & Left (cell.Text, 1)) cell.EntireRow.Copy wksT.Columns("A").Cells (cell.Row) End If Next cell 'compresses each list to the top On Error Resume Next For Each wksT In wks.Parent.Worksheets wksT.Columns("A").SpecialCells (xlCellTypeBlanks).EntireRow.Delete xlUp Next Application.Goto oldSelection Application.ScreenUpdating = True End Sub Private Function GetWorksheet(wkbW As Workbook, _ strName As String) As Worksheet 'Returns the wkbW worksheet named. 'Adds it, if it doesn't exist. Dim wks As Worksheet On Error Resume Next Set wks = wkbW.Worksheets(strName) On Error GoTo 0 If (wks Is Nothing) Then Set wks = wkbW.Worksheets.Add(After:=Worksheets ("Data")) wks.Name = strName End If Set GetWorksheet = wks Set wks = Nothing End Function . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change my column designator from Numeric to Alpha? | Setting up and Configuration of Excel | |||
How do I change numeric column designations to alpha? | Excel Discussion (Misc queries) | |||
How can I change my column references from Alpha to Numeric? | Excel Worksheet Functions | |||
HOW DO I CHANGE COLUMNS FROM NUMERIC TO ALPHA HEADINGS? | Excel Discussion (Misc queries) | |||
Columns are now numeric, not alpha. how to change back? | Excel Discussion (Misc queries) |