Insert Sheet Code

J

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
 
J

J.W. Aldridge

stevebriz said:
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.
 
S

stevebriz

J.W. Aldridge said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top