Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 12th 08, 07:13 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 138
Default Insert new Worksheet with name, and insert the sheet into itsalphabetical / numerical location

Hello all,

I have a Macros that creates a copy of a Template worksheet, prompts
the user for a Workorder number, then inserts a sheet with the name as
the Workorder number (ie. 123456).

What I would like to be able to do is insert this sheet into its
correct position. ie. if a user has already inserted two worksheets
named 1234567 and 8901234, if they then try and insert a new worksheet
titled 4567890, I would like it to insert itself in between the other
two workorder numbers. This will make it easier to find.

Currently is is simply inserted after a sheet titled "Materials." I'd
like to still have the sheet inserted after the Materials sheet, but
be inserted in its proper numerical position after that sheet.

Here is my code currently. Thank you for any help!

Sub InsertSheetWUniqueName()
WorkorderNumber = InputBox("This will create an auto-generating
Workorder Tracking Sheet for all children of a Parent Workorder.
Please enter Parent Workorder Number", "CREATE WORKORDER TRACKING
SHEET")
If WorkorderNumber = "" Then
MsgBox "Nothing Entered", vbOKOnly, "Cancelling..."
Else
On Error Resume Next
Sheets("Template").Copy After:=Sheets("Materials")
ActiveSheet.Name = WorkorderNumber
Range("B2").Select
ActiveCell.FormulaR1C1 = WorkorderNumber
Range("E2").Select
If Err.Number 0 Then
MsgBox "Please Enter a Different Workorder Number", vbOKOnly,
"Sheet Already Exists"
Err.Clear
Application.DisplayAlerts = False
ActiveSheet.Delete
Sheets("Admin").Select
Application.DisplayAlerts = True
End If
On Error GoTo 0
End If
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot insert worksheet in exel - not available in insert menu pedro39 Excel Worksheet Functions 1 July 24th 08 12:09 PM
Copy Chart and Insert in new Sheet as picture over the same location TFriis Excel Programming 1 January 22nd 08 04:27 PM
How do I insert new work sheets in numerical sequence? ray/c Excel Worksheet Functions 2 July 7th 06 01:26 PM
Can I auto insert a worksheet when I insert a value in a cell. iainc Excel Worksheet Functions 0 April 27th 06 08:37 AM
How can I insert numerical bullets in a cell ex: 1a) ? Daisy Excel Discussion (Misc queries) 3 July 8th 05 04:28 AM


All times are GMT +1. The time now is 10:54 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017