Create/delete New worksheet

ibrahimaa · #1 01-15-2012, 04:37 AM

I have tested code to create new workbook and it worked fine. How can I edit the following code to create & delete a new worksheet? I appreciate your support. Thank you.

Code:

Sub getvalue()
Dim Path, Myrange, ShName, Wb As String
Path = "\\ABC"
Wb = "Report-1c-Server.xlsx"
Myrange = "A9:C9700" 
Workbooks.Open Filename:=Path & "\" & Wb
 With ThisWorkbook.Sheets("EXHIBIT 6-A") 
 .Activate
Select Case ThisWorkbook.Sheets("EXHIBIT 6-A").Cells(6, "G")
Case 7
I want Create a new worksheet here 
ShName = "Aramco-MP-July"
ActiveSheet.Cells(9, "A") = Application.VLookup(ActiveSheet.Cells(5, "J"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 3, False)
Case 8
ShName = Sheets("Aramco-MP-Aug")
ActiveSheet.Cells(21, "A") = Application.VLookup(ActiveSheet.Cells(9, "J"), Workbooks(Wb).Sheets(ShName).Range(Myrange), 5, False)
I want delete a new worksheet here 
Case Else
End Select
End With
Workbooks(Wb).Close , savechanges = True
End Sub

Catalin.B · #2 01-15-2012, 08:43 AM

add this line:

Code:

ThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)

or:

Code:

Worksheets.Add().Name = "MySheet"

or, if you want to add more than 1 sheet, use:

Code:

Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4

to delete a sheet use:
Sheets("Sheet1").delete
or:
ActiveSheet.delete
Make sure to turn off events if you do not want to see the confirmation pop-up message: (change back to true after you delete the sheet)

Code:

Application.DisplayAlerts = False

ibrahimaa · #3 01-16-2012, 10:08 PM

Thank a lot for the great support. It worked fine; however, I should create the new sheet to present specific details for specific users (60 users out of 200 users that will use the form on a corporate level).

I am planning to read the user Id from the system registry file to control this issue in the form. Any idea about reading the user ID would be highly appreciated. Thank you.

Catalin.B · #4 01-16-2012, 11:43 PM

To get the user name, just use
User=Environ("USERNAME")

ibrahimaa · #5 01-16-2012, 11:51 PM

I highly appreciate your support. Thank you.

Catalin.B · #6 01-16-2012, 11:53 PM

Glad to help

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Delete option greyed out when trying to delete row 1	tpcervelo	Excel	2	11-15-2011 06:48 AM
How to delete page not worksheet?	rec	Excel	6	05-20-2011 08:51 PM
delete email message via blackberry and have it delete on my pop3 and my outlook	Iamthestorm	Outlook	2	10-28-2010 12:21 AM
Can I use a formula to name a worksheet?	nolesca	Excel	3	06-07-2010 04:02 PM
Worksheet copying?	markg2	Excel	4	01-07-2010 10:08 AM