Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull data from one worksheet and place in a new worksheet | Excel Worksheet Functions | |||
Workbook Opening in Certain Place | Excel Discussion (Misc queries) | |||
Opening Workbook at Certain Place | Excel Discussion (Misc queries) | |||
1 workbook, 4 worksheets, want to add rows in same place in all WS | Excel Discussion (Misc queries) | |||
Place code from current workbook to new workbook | Excel Programming |