Worksheet place in workbook
 

I have found the vb code to allow me to insert sheets based on names
from a list. Next, I found the code that will allow me to add the same
cell values from each sheet: { SUM(START:END!C5) ]. My problem is, when
I add sheets to a workbook from the list, I want the sheet labled "end"
to remain in its place at the end so that my formulas can calculate
from "start" (add all the sheets up until) "end".

Is there any way possible to do this?

Worksheet place in workbook
 

The trick is that the sheet that is inserted must be placed between the start
and end sheets. If it were me I would hide both the start and end sheets so
that no one tampers with them or moves them around. Can you post your insert
sheet code so that we can get an idea of what exactly you are up to...
--
HTH...

Jim Thomlinson


"J.W. Aldridge" wrote:

I have found the vb code to allow me to insert sheets based on names
from a list. Next, I found the code that will allow me to add the same
cell values from each sheet: { SUM(START:END!C5) ]. My problem is, when
I add sheets to a workbook from the list, I want the sheet labled "end"
to remain in its place at the end so that my formulas can calculate
from "start" (add all the sheets up until) "end".

Is there any way possible to do this?

Worksheet place in workbook
 

worksheets.Add Befo=worksheets("End")

or if you are copying a "template" sheet

worksheets("Template").Copy Befo=worksheets("End")

--
Regards,
Tom Ogilvy



"J.W. Aldridge" wrote in message
oups.com...
I have found the vb code to allow me to insert sheets based on names
from a list. Next, I found the code that will allow me to add the same
cell values from each sheet: { SUM(START:END!C5) ]. My problem is, when
I add sheets to a workbook from the list, I want the sheet labled "end"
to remain in its place at the end so that my formulas can calculate
from "start" (add all the sheets up until) "end".

Is there any way possible to do this?

Worksheet place in workbook
 

Thanx. I believe that the following code will do the trick but I need a
little help on exactly where it should go. Also, any way to add an code
for AFTER (sheet name "start")?

worksheets.Add Befo=worksheets("End")

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