Insert Sheet Code

[J.W. Aldridge]

I need this code snippet to work in the insert sheet code below.

worksheets.Add Before:=worksheets("End")

Also, if I can insert a "Begin" sheet to indicate when to begin
inserting sheets, it would work wonders for me.

Thanx.!


Here's my insert sheet code.

Sub Cop_RowS_To_Sheets()
'copy rows to worksheets based on value in column A
'assume the worksheet name to paste to is the value in Col A
Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String


'start with cell A2 on Sheet1
Set CurrentCell = Worksheets("ALL ERRORS").Cells(2, 1) 'row 2 column 1



Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
Set SourceRow = CurrentCell.EntireRow


'Check if worksheet exists
On Error Resume Next
Testwksht = Worksheets(CurrentCellValue).Name
If Err.Number = 0 Then
'MsgBox CurrentCellValue & " worksheet Exists"
Else
MsgBox "Adding a new worksheet for " & CurrentCellValue
Worksheets.Add.Name = CurrentCellValue


End If


On Error GoTo 0 'reset on error to trap errors again


Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)
'note: using CurrentCell.value gave me an error if the value was
numeric


' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1
SourceRow.Copy Destination:=Targetsht.Cells(TargetRow, 1)


'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)
Loop
End Sub

 

[stevebriz]

I think will help you:
http://www.ozgrid.com/VBA/add-worksheet.htm

 

[stevebriz]

Worksheets.Add(before:=Worksheets(Worksheets.Count)).Name = "MySheet"

 

[J.W. Aldridge]

Worksheets.Add(before:=Worksheets(Worksheets.Count)).Name = "MySheet"

I believe this will work however, is there a simpler way to insert this
one into my code?

I am getting thrown off with fitting it into my code and referring to
my sheet names.
The names of my sheets are "start" & "end".
Any sheet added will need to go in between these two.

 

[stevebriz]

I believe this will work however, is there a simpler way to insert this
one into my code?

I am getting thrown off with fitting it into my code and referring to
my sheet names.
The names of my sheets are "start" & "end".
Any sheet added will need to go in between these two.

Not sure I understood you correctly but here goes....
If the "start sheet is before the "end "sheet then the below will work-
then you can use:

Worksheets.Add(before:=Sheets("end")).Name = "MySheet1"

but you need to be careful not to try and add a sheet with the same
name or you will get an error.

 

[J.W. Aldridge]

worked like a charm!!!!

Thanx!!!!