Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Macro to add a new sheet in Excel


(!)

Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
02-Aug-2004, 10:12 AM #1
Macro to add a new sheet in Excel
I need to create a macro that when run accomplishes the following:

Creates a new sheet and names it the same as the value of the currently active cell in the current worksheet.

The macro should only work when Sheet1 is active, and only if the active cell is within a range in column A e.g. > A5.

If a sheet of the intended name already exists then it should display an Error Message.

The new sheet should actually be a copy of sheet 2.

Example.

Sheet 1 = 'Test List'
Sheet 2 = 'Blank Test Form'

If I enter '010' in Col A, Row 10 on sheet 1, and then run the macro (via a button) it should copy Sheet 2 into the workbook giving it a name of 'Test010'.

If poss, I'd like it to create the new sheet and place it alphabetically along the sSheet names at the bottom of the workbook!

One for XLGURU please?

Thanks
Hew

PS I am about to start a VBA course so this will help me.
Rollin_Again's Avatar
Member with 4,689 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
02-Aug-2004, 10:51 AM #2
Heres some code to start with. There are a couple of issues to consider when alphabatizing the sheets. Do you want to keep Sheet1 (Test List) and Sheet2 (Blank Test Form) as the first sheets in the workbook or do you want them alphabatized along with the other newly added sheets? Since my code references the sheets by the relative position or index number in the worksheet, the code will need to reference the sheet names if they are moved around.

Code:
Public Sub AddSheets()

If ActiveSheet.Index = 1 And ActiveCell.Column = 1 Then

vNewSheet = Sheets(1).Range(ActiveCell.Address)

For Each ws In Worksheets
   If ws.Name = vNewSheet Then
        MsgBox "Sheet already exists", vbInformation
            Exit Sub
   End If

Next ws
  
Sheets(2).Copy After:=Sheets(2)
ActiveSheet.Name = vNewSheet
Sheets(1).Select

End If

End Sub

Rollin

Last edited by Rollin_Again; 02-Aug-2004 at 11:18 AM..
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
02-Aug-2004, 11:34 AM #3
Great Rollin, that's woked straight away.

Thinking about the sequencing of the sheets , that's not important because they will be created in sequence anyway.

However, could I extend your solution for the following please:

Instead of the sheet subscript no to copy, can you this by name?
Can the copy go to the end rather than after sheet 2?
If the sheet being copied is hidden, how do I unhide the copied sheet?

(i.e. instead of Sheets(2).Copy After:=Sheets(2),
could it be something like
Sheets(hidden sheet name).copy After:=Sheets(last sheet).

Many thanks
Hew
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
02-Aug-2004, 12:09 PM #4
Rollin,

Also,

just realised, instead of getting the error msg when trying to add a duplicate sheet, I'm getting a MS VB box with error 400 in it!

Could you advise,

Thanks
Rollin_Again's Avatar
Member with 4,689 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
02-Aug-2004, 01:15 PM #5
Quote:
Originally Posted by HEWANM
How do I unhide the copied sheet?
Instead of the sheet subscript no to copy, can you this by name?
Can the copy go to the end rather than after sheet 2?
You must unhide the hidden sheet before copying it or the copied sheet will also be hidden.

You can use the actual sheet name instead of the index by enclosing the name in double quotes.

Use Sheets.Count to move the copy to the end of the workbook. Then hide the original sheet again.

Code:
Sheets("Blank Test Form").Visible = True
Sheets("Blank Test Form").Copy After:=Sheets(Sheets.Count)
Sheets("Blank Test Form").Visible = False
I am still trying to figure out why you are getting the VB Error 400. I will try to figure it out and post back.



Rollin
XL Guru's Avatar
Senior Member with 2,702 posts.
 
Join Date: Aug 2003
Location: nr. Cambridge, England.
Experience: there's no substitute for it, apparently
02-Aug-2004, 01:29 PM #6
Hi HEWANM ; I'm actually still on VBA Lesson 1. /

Rollin, ya beat me too-it. I also found the "Sheet 2's hidden property gets copied" thing. Maybe you can figure a way to evaluate the new name as valid (e.g. <> "History") in the first instance ; all I could kludge (tho' it worked OK, for the worksheet mod) was:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Row < 6 Then Exit Sub
If Selection.Cells.Count > 1 Then Exit Sub
On Error GoTo ErrorHandler
Sheets("Sheet2").Visible = True
n = ActiveWorkbook.Sheets.Count
Sheets("Sheet2").Copy After:=Sheets(n)
Sheets("Sheet2").Visible = False
ActiveSheet.Name = Target.Value
ErrorHandler:
MsgBox "Invalid sheet name, prolly."
Application.DisplayAlerts = False
ActiveSheet.Delete
Sheets("Sheet1").Select
Application.DisplayAlerts = True
End Sub


Rgds,
Andy
Rollin_Again's Avatar
Member with 4,689 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
02-Aug-2004, 01:35 PM #7
Quote:
Originally Posted by HEWANM
just realised, instead of getting the error msg when trying to add a duplicate sheet, I'm getting a MS VB box with error 400 in it!

Could you advise,

Thanks
Please post your code. My code ends the sub-procedure if a duplicate name is detected BEFORE the copy takes place.....it never gets a chance to try to create a duplicate sheet.

Rollin
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
02-Aug-2004, 03:56 PM #8
Thanks Rollin (and welcome Andy).

I've been off-line for a few hours and am now not at the machine I was using.

I copied and pasted Rillin's code from the Post so it was exactly the same,; I can't double check until tomorrow.

What I was getting with the attempt to duplicate a sheet name, was 1st the error 400 came up, if I replied OK then I got a copy of Sheet 2 with it's name (2).

I'll pay some more tommorrow and incorporate you latest advise.

Many thanks
Hew

PS Andy, If you're still on lesson 1 then it'll take me some time to reach even level 0.
Rollin_Again's Avatar
Member with 4,689 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
02-Aug-2004, 06:25 PM #9
That's very strange.....I am using the exact same code and I do not generate any errors. What version of Excel are you using?

Rollin
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
03-Aug-2004, 03:04 AM #10
Rollin,

Here's the code taht's giving me the error 400, to arise this I select a cell with say value 10 in it, run the macro which creates sheet 10, then try it again - it doesn't give me the expected "Sheet already exist" msg!:


Public Sub AddSheets()

If ActiveSheet.Index = 1 And ActiveCell.Column = 1 Then

vNewSheet = Sheets(1).Range(ActiveCell.Address)

For Each ws In Worksheets
If ws.Name = vNewSheet Then
MsgBox "Sheet already exists", vbInformation
Exit Sub
End If

Next ws

Sheets(2).Copy After:=Sheets(2)
ActiveSheet.Name = vNewSheet
Sheets(1).Select

End If

End Sub



I'm using WinXP with Excel 2002.

Many thanks

Hew
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
03-Aug-2004, 03:38 AM #11
Rollin,

I've made some of the changes to add new sheets to the end but I'm still getting 2 errors:
1. Error 400 instead of the "Sheet already exists" msg
2. Subscript range error if the sheet name I am trying to add is not numeric.

I'm attaching my s/s so you could run yourself, the BlamkTest is currently not hidden, because one other aspect of the above errors is that it still creates the copied sheet but with a name BlankTest(2) or (3).... [which would have been hidden].

Much appreciate your advise,

thanks

Hew

Just seen that I can't attach xls files, can I send you some other way?

Last edited by Yorkshire Guy; 03-Aug-2004 at 03:44 AM.. Reason: Attachment not uploaded
Rollin_Again's Avatar
Member with 4,689 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
03-Aug-2004, 01:38 PM #12
Make the following change to the code.

vNewSheet = Activecell.Text

This should take care of some of the problems. If you need to, either ZIP the file and attach it or just change the file extension to .txt and attach it and I'll change it back to .xls when I download to my computer.



Rollin

Last edited by Rollin_Again; 03-Aug-2004 at 02:03 PM..
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
04-Aug-2004, 03:34 AM #13
Rollin,

I've attached my latest version of the s/s with VBA zipped up.

I played around yesterday and have got something almost there (by trial and error).

I determined that my 400 error was when attempting to create an existing sheet with a numeric name - the UCase seemed to fix that as well as cater for Test No's such as 'TEST' and 'test'.

I still seem to have problems when the BlankTest sheet is hidden occassionally at random but can't see why.

My (hopefully) outstanding problem I think is simple, but at this stage of my VB knowledge (next to nil) I don't know the syntax to do it:

after creating the sheet I want to put the Test No (i.e. sheet name) in cell B5, this already had focus in the BlankTest sheet so I've just assumed that I'm in the right cell , but seems a flaky assupmtion.
Then, how do I put focus into 2 cells below i.e B7?

Thanks agaimn for all your help, isn't there any help within Exdel VBA that explains not just the syntax of the commands but what they do?

Cheers

Hew
Attached Files
File Type: zip TestLogTSG.zip (24.2 KB, 28 views)
Rollin_Again's Avatar
Member with 4,689 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
04-Aug-2004, 10:53 AM #14
Why is your code attached to the worksheet module instead of a seperate module or even the workbook module? By attaching to the worksheet module you are not able to manipulate your workbook as easily because you are forced to make specific reference to other sheets when calling certain methods.

When the macro first kicks off, the variable vNewSheet is being set with the following line.

vNewSheet = UCase(Sheets("Log").Range(ActiveCell.Address))

Since the cell you are evaluating is already selected, you can use the line vNewSheet = Activecell.Text instead. This calls the .Text property of the cell which assigns the variable vNewSheet a text value instead of the numeric value shown in the cell.

If you use ActiveCell.Text, the function UCase is not needed unless you want the Sheet name to appear in all CAPS if it is non-numeric. If you want to include it anyways, you can always use vNewSheet = UCase(Activecell.Text)

After setting the vNewSheet variable you can go ahead and set a new variable equal to the test name/description by using either of the following lines:

vTest = ActiveCell.Offset(0, 1).Text
or
vTest = UCase(ActiveCell.Offset(0, 1).Text) <----For all CAPS

Since you now have set a variable value equal to the test name, you do not need to use your VLOOKUP function. Simple place the values in whichever cells you want by referencing this variable.

Sheets(vNewSheet).Range("B5").Value = vNewSheet
Sheets(vNewSheet).Range("B6").Value = vTest


if you want to actually select the cell you can use

Sheets(vNewSheet).Range("C6").Select


Normally you can just say Range("C6").select to select C6 cell on the active sheet but since your code is bound to that specific worksheet and not a seperate module, you have to call the sheet name first as I have done above.





Rollin

Last edited by Rollin_Again; 04-Aug-2004 at 11:08 AM..
Yorkshire Guy's Avatar
Yorkshire Guy Yorkshire Guy is offline
Member with 563 posts.
THREAD STARTER
 
Join Date: Dec 2003
Location: Yorkshire, UK
04-Aug-2004, 11:43 AM #15
Thanks again Rollin,

Thew answer to your first question is simply because I don't know no better!

I don't know what the difference is as to where to put the macro, as I said I got this far by trial and error and so far I haven't found any explanation of such things (such as what a Public Sub is?)

I'm using a VLOOKUPin case the Test Title changes.

Anyway, thanks for your advise, I've done some more changes and have a working solution now so we can close this thread.

I'll probably be back as I progress further.

Hew
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑