kemperohlmeyer
Board Regular
- Joined
- Oct 4, 2006
- Messages
- 73
Can someone help me figure out why this code runs fine when it excludes cell.EntireColumn.Copy ActiveSheet.Range("A1") (in the middle of the code) but doesnt work when that piece is included?
The code is intended to copy and paste everything in the column for which the tab is named.
Thoughts?
ko
(code below)
Dim cell As Range
Dim newName As String, xx As String
Sub GenWStabnames2()
'Kemper Ohlmeyer based on previous code by David McRitchie
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
cell.EntireColumn.Copy ActiveSheet.Range("A1")
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
The code is intended to copy and paste everything in the column for which the tab is named.
Thoughts?
ko
(code below)
Dim cell As Range
Dim newName As String, xx As String
Sub GenWStabnames2()
'Kemper Ohlmeyer based on previous code by David McRitchie
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
cell.EntireColumn.Copy ActiveSheet.Range("A1")
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