kemperohlmeyer
Board Regular
- Joined
- Oct 4, 2006
- Messages
- 73
Hi,
I am trying to take data from one spreadsheet and do a couple of things. First, i need to create a tab for each column and name the tab after the name in row 1. (this code i already have, it is below)
Second, and here is where i need help, i want to put the tab name and corresponding data (all in the same column on the first sheet) onto each tab. Essentially i am just divying up each column on my master sheet into its own tab so that i can do further data analysis.
Please help.
KO
Sub GenWStabnames2()
'Kemper Ohlmeyer based on code from David McRitchie
Dim cell As Range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Worksheets("Manager list").Range("fund.names")
Sheets.Add After:=Sheets(Sheets.Count)
If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub
I am trying to take data from one spreadsheet and do a couple of things. First, i need to create a tab for each column and name the tab after the name in row 1. (this code i already have, it is below)
Second, and here is where i need help, i want to put the tab name and corresponding data (all in the same column on the first sheet) onto each tab. Essentially i am just divying up each column on my master sheet into its own tab so that i can do further data analysis.
Please help.
KO
Sub GenWStabnames2()
'Kemper Ohlmeyer based on code from David McRitchie
Dim cell As Range
Dim newName As String, xx As String
Err.Description = ""
On Error Resume Next
'--cells with numbers, including dates, will be ignored,
For Each cell In Worksheets("Manager list").Range("fund.names")
Sheets.Add After:=Sheets(Sheets.Count)
If Err.Description <> "" Then Exit Sub
Err.Description = ""
newName = cell.Text
ActiveSheet.Name = newName
If Err.Description <> "" Then
'--failed to rename, probably sheetname already exists...
xx = MsgBox("Failed to rename inserted worksheet " & _
vbLf & _
ActiveSheet.Name & " to " & newName & vbLf & _
Err.Number & " " & Err.Description, vbOKCancel, _
"Failed to Rename Worksheet, it will be deleted:")
'--eliminate already created sheet that failed to be renamed...
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
'--check for immediate cancellation...
If xx = vbCancel Then Exit Sub
Err.Description = ""
End If
Next cell
End Sub