michaelehoudek
New Member
- Joined
- Jan 15, 2016
- Messages
- 21
I have the following code. It works fine and does what I want, but then is continuing past the list of names. After the last named sheet is created, it throws an error after it creates the next new worksheet, but can't name it as it has created and named the last worksheet I wanted it to. The debug take me to the line bolded below. I'm thinking the error is caused by the Set MyRange line, but don't know what to do. Any ideas?
(code)
Dim MyCell As Range, MyRange As Range
Set MyRange = Selection
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Application.DisplayAlerts = False
For Each MyCell In MyRange
Sheets("BASE").Select
Columns("A:BT").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("a2").Value = MyCell.Value
Range("a2").Select
Next MyCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
(End code)
(code)
Dim MyCell As Range, MyRange As Range
Set MyRange = Selection
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Application.DisplayAlerts = False
For Each MyCell In MyRange
Sheets("BASE").Select
Columns("A:BT").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("a2").Value = MyCell.Value
Range("a2").Select
Next MyCell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
(End code)
Last edited: