Naming worksheets...new to VBA

Hi,

I'm pretty new to VBA and need to create a simple macro.  When the
command button it is linked to is clicked...I need it to search a
workbook for the highest numbered worksheet (ex. "G4" was the highest
sheet so the macro will create G5) and then create a new worksheet. 
This might be a simple problem but any help is appreciated.

Thanks,
Mjack


---
Message posted from http://www.ExcelForum.com/

0
1/20/2004 2:48:11 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
695 Views

Similar Articles

[PageSpeed] 58

Try this Mjack

Sub test()
Dim Shcount As Long
Shcount = Sheets.Count
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "G" & Shcount + 1
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"mjack003 >" <<mjack003.10c4u9@excelforum-nospam.com> wrote in message news:mjack003.10c4u9@excelforum-nospam.com...
> Hi,
>
> I'm pretty new to VBA and need to create a simple macro.  When the
> command button it is linked to is clicked...I need it to search a
> workbook for the highest numbered worksheet (ex. "G4" was the highest
> sheet so the macro will create G5) and then create a new worksheet.
> This might be a simple problem but any help is appreciated.
>
> Thanks,
> Mjack
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
1/20/2004 3:25:27 PM
Thanks for the help Ron! It works perfectly except for some reason it
skips G2.  I created G1 manually and it skips to G3.  If I start with
now worksheets it starts with G2?  Any suggestions?
Thanks again.

Best Regards,
Mjack


---
Message posted from http://www.ExcelForum.com/

0
1/20/2004 4:09:46 PM
When I run the macro in a workbook with one worksheet(G1)
it is working correct.

Do you have a hidden sheet in the workbook???

-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"mjack003 >" <<mjack003.10c8m8@excelforum-nospam.com> wrote in message news:mjack003.10c8m8@excelforum-nospam.com...
> Thanks for the help Ron! It works perfectly except for some reason it
> skips G2.  I created G1 manually and it skips to G3.  If I start with
> now worksheets it starts with G2?  Any suggestions?
> Thanks again.
>
> Best Regards,
> Mjack
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
1/20/2004 4:21:22 PM
I had a sheet "Map" as the initial worksheet but when I took it out and
tried it again it worked great.  My next problem is I don't know where
to put in my directory to save to.  I want to be able to insert that
worksheet into another wookbook.  If you could help that would be
great.

Thanks,
Mjack


---
Message posted from http://www.ExcelForum.com/

0
1/20/2004 6:49:00 PM
Hi

Is the workbook open or do you want the macro to open the workbook .
Or do you want to open a new workbook???

Post more information and I will help you tomorrow after work


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"mjack003 >" <<mjack003.10cfzm@excelforum-nospam.com> wrote in message news:mjack003.10cfzm@excelforum-nospam.com...
> I had a sheet "Map" as the initial worksheet but when I took it out and
> tried it again it worked great.  My next problem is I don't know where
> to put in my directory to save to.  I want to be able to insert that
> worksheet into another wookbook.  If you could help that would be
> great.
>
> Thanks,
> Mjack
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
1/20/2004 10:49:13 PM
I would need the macro to run a check to see if the workbook is alread
open.  If its not open then it would need to open the workbook, let'
call it "testbook", and create a worksheet in increments of one (G1
G2, G3 etc.) when an object in the first workbook is single clicked.
If possible I'd also like to keep the focus on the first workbook.
Thank you for the help.

Regards,
Mjac

--
Message posted from http://www.ExcelForum.com

0
1/21/2004 3:18:07 PM
Hi Mjack

Copy this code in a normal module
change the path to the testbook.xls

Sub File_Open_test()
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    Dim Shcount As Long
    Set WB1 = ThisWorkbook
    Application.ScreenUpdating = False

    If bIsBookOpen("testbook.xls") = False Then
        Set WB2 = Workbooks.Open("C:\Data\testbook.xls")
    Else
        Set WB2 = Workbooks("testbook.xls")
    End If
    WB2.Activate
    Shcount = WB2.Sheets.Count
    WB2.Sheets.Add after:=WB2.Sheets(Shcount)
    ActiveSheet.Name = "G" & Shcount + 1
    WB1.Activate
    Application.ScreenUpdating = True
End Sub

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function



-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2000-2003)
www.rondebruin.nl



"mjack003 >" <<mjack003.10e0w5@excelforum-nospam.com> wrote in message news:mjack003.10e0w5@excelforum-nospam.com...
> I would need the macro to run a check to see if the workbook is already
> open.  If its not open then it would need to open the workbook, let's
> call it "testbook", and create a worksheet in increments of one (G1,
> G2, G3 etc.) when an object in the first workbook is single clicked.
> If possible I'd also like to keep the focus on the first workbook.
> Thank you for the help.
>
> Regards,
> Mjack
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
rondebruin (3790)
1/21/2004 6:50:17 PM
Thanks Ron. Works like a charm.

Best Regards, 
Mjack


---
Message posted from http://www.ExcelForum.com/

0
1/22/2004 2:53:04 PM
Reply:

Similar Artilces:

Referring to a range that has been copied (Excel-VBA)
Hi, Can anyone confirm if there is a way in VBA to refer to a range that has just been copied but that it is not the selected range? For example: I select Range A1:A2, I press Ctrl+C and dotted moving lines appear around A1:A2. Now, I move to any other cell, say B4. At this stage, A1:A2 remain highlighted with the dotted lines, so I understand that the Application "knows" of them, therefore how can I return their address through code? Selection.address will obviously not work as it is no longer the selected range...I looked within the Application object but could not fin...

summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other w...

Show email address instead of contact name
How can I make Outlook 2000 show the typed email address instead of resolving to the contact name in printouts of the contacts list and in the address boxes of email ? For example, I have a list of contacts in a custom list view with the email fields showing. Several of the entries in the email fields have resolved to the name of the contact. I want to see their email address not their name again. Also, a second example. I have three email addresses for a person, in the Email1, Email2, and Email3 fields. I go to send an email and instead of selecting the default from the addressbook/contact ...

Problem re-installing on new Macbook
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: intel Hi There, I just purchased a new Macbook Pro and am trying to re-install all the old apps from my earlier powerbook. I have a problem with installing Office v.X because after all these years I only have the v.X upgrade CD - I can't locate the Office 98 Upgrade and original Word for Mac CDs/Disks because its been such a long time! When I do the install it asks for the location of the earlier install. Is there any way to work around this? I have receipts for the purchase of Office v.X upgrade and Office 98 upgrade for...

new "you must be connected to the internet to sign in online" erro
Hi. I've got the Microsoft Money Plus trial and have only had it for about 2 weeks. It was working fine last time I ran it a few days ago and today when I ran it, it won't log me in. It says, "You must be connected to the internet to sign in online." Nothing interesting has changed on my computer other than: - A new version of zone alarm, which I've tried turning off and running MsMoney, but I still get the same error. - Whatever random updates Microsoft has decided to do. Is anyone else seeing this? Anyone know how to fix it? Thanks, mike In microsoft.public.m...

Problem with vba directory function
Hi We have an access application that has a button when clicked opens an excel worksheet showing all exported data based on code The access sql system is in Citrix with a single front end copy. Here is the code part that is throwing the error for just one user. Rest all are working fine Dim strSourceFileName As String Dim WorkBookName As String strSourceFileName = "c:\CarLogOutPut\CarLogExportTemplate.xls" WorkBookName = "c:\CarLogOutPut\CarLogExport.xls" If Dir(strSourceFileName) = "" Then MsgBox "CarLogExportTemplate.xls doe...

Entering Data Into Text Boxes and Worksheet Protection
I'm wondering if there's a way to enter data into a text box once a worksheet has been protected. Currently, once the sheet is protected, there's no way to enter data into a text box. Any thoughts appreciated. Thanks. Stephan There are two different textboxes--one from the drawing toolbar and one from the control toolbox toolbar. But you can rightclick on each and choose: format control (or Format CheckBox There's a tab call protection. Each has a Lock option. (the drawing toolbar has Locked and Lock Text) After I unchecked each of them, I could use them (when I protect...

Count how many ROWS ago a new high was made
I need to count how many rows ago a new high was made in the last 6 days. In this case 2 days ago a new high was made. How to I get excel to count that number of rows ago without manually doing this? Guessing a bit of the data cells, but it would be something like =MATCH(MAX(A2:A20),A2:A20,0) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "rhhince" <rhhince@shaw.ca> wrote in message news:1168725590.045657.70170@51g2000cwl.googlegroups.com... >I need to count how many rows ago a new high was made in the last 6 > days. In this case 2 days ago a new hi...

Type Name in the To: field
I moved several people from one exchange server to the next and everthing is fine if you select them. If I type the name in it still has the old information in it. How do I clear this. Thanks for your help! We have Outlook 2000 and 2003 in 2003, use the arrow key to select the old address and press delete. if it also happens in 2000, find and delete the *.nick file. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips...

Tab Names #3
Hi, I am looking for a code that can change the sheet tab name to correspond with a name in a given cell on the same worksheet Many thanks in advance John Hi John, VBA event code Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address(False, False) = "A1" Then Me.Name = Target.Value End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'cod...

copy sheet with range names
I'm making 2 copies of an existing worksheet within a workbook. The original sheet has lots of range names. Now, when I make my copies, the range names are screwed up and are referring to the new sheet instead of the original one. What's up with that? I don't see any options related to range names. Deleting and resetting the names would take forever, as would "re-defining". I shouldn't have to do that. Any ideas? -- RMC,CPA I think if you look at your range names, you'll find that xl localized the copies. When it copied the worksheet, it had to do something ...

Crack protection passwork worksheet Visio 2000
Hi, Please help me to unprotect a worksheet via Visio 2000 since I forgot my password. Thanks. My ...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

New Shapes?
Any word on when MS will release new Shapes for Visio? If MS will not venture into this avenue, can anyone recommend a few good resources for the best shapes on the Net? I am interested in the highest quality stuff out there. Thanks in advance. -a There will be some new shapes with Visio 2007, but there are a number of sources on the web for shapes. I have tried to create a catalogue of what I have found at http://visio.mvps.org/3rdparty.htm. So what sort of shapes are you looking for? John... Visio MVP Need stencils or ideas? http://visio.mvps.org/3rdparty.htm Need VBA exampl...

Call up computer name
[Excel 2003] Is there a way to retrieve the computer identity/name and display it in a listbox on a userform? regards, Roger uName =3D Environ("computername") Me.ListBox1.AddItem uName On Dec 3, 9:30=A0am, Roger on Excel <RogeronEx...@discussions.microsoft.com> wrote: > [Excel 2003] > > Is there a way to retrieve the computer identity/name and display it in a > listbox on a userform? > > regards, > > Roger Thanks - works nicely Roger "muddan madhu" wrote: > > uName = Environ("computern...

Changing name from Arabic to English
I, m registering all new recruitment employees so always I'm writing the employee name by Arabic and English. So there is any code to change automatically the person name for Arabic to English ...

Ability to merge windows into tabs and move a tab into a new windo
I often find myself wishing that I could do two things with IE: 1) Take two IE windows, each with one or more tabs, and merge them together into a single window with the tabs from both. This would help reduce clutter in my taskbar. 2) Take a tab and convert it into an IE window of its own as often times I want to be able to see two web sites at the same time (side by side), which I can't seem to do if they are tabs in the same window. Either that or some sort of 2 page view where you can see two tabs side by side and can navigate each separately. Would like to se...

Excel worksheet merge
Hello all, I have searched google, but to no avail. Here goes. I have one set of excel worksheets (one is an exact link to the other) that I copied four times for a total of five. I moved the 1st worksheet of each to an individual folder. So what I am left with are the 5 link worksheets. Can I merge the links together? I have an Access db that I will use to process the info. I have linked the links to an Access db and it works. Except I have 5 dbs instead of the one that I really need. The fields are the same in all linked dbs. My question is- should or can I merge before access and i...

Changing info from one worksheet to the next
Hi all. I have linked my 55 sheets to one which is great. What i need to know now if possible. Each sheet has the same question over 11 columns Each row is dated and a numeric number from 1 - 10 in each row Now on the master sheet where everything is linked, is there a way that If i changed the date on the master sheet it would reflect the answers from the row with that date? At present the answers showing is for 01/03/10, but i would like to look at the totals for 08/03/10 and show the answers from each sheet for that date. I could have a sheet for each week, but im hoping there i...

Fonts name using CDC
Hello, I am drawing text using CDC (MFC control). How can I know all the Fonts available? does anyone know the font list available (if so please, and it is not so long, please write it, or at least tell me how to get it) thanks! Mathieu "Mathieu Fregeau" <mathieu.fregeau@polymtl.ca> wrote in message news:fAg0b.670$HB4.154408@news20.bellglobal.com... > Hello, > > I am drawing text using CDC (MFC control). How can I know all the Fonts > available? does anyone know the font list available (if so please, and it is > not so long, please write it, or at least te...

My synchronized emails disappeared after adding a new account
This is a multi-part message in MIME format. ------=_NextPart_000_004F_01CB5135.56C7EAD0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I created first an account and I synchronized everything on my local = machine, meaning all messages not only the headers. After doing this I checked if those messages were in my local computer = and they were all there. I added a new live account and I did not even synchronized it. Just = logged in it. To my surprise when I clicked on the first account which was a Hotmail = one, I...

Unable to add a new contact in CRM 4.0
Hi all Unable to add a new contact in CRM 4.0 as it errors out saying "Object reference not set to an instance of an object". Here is the event log information. Web Service Plug-in failed in OrganizationId: 01f0210d-6bf6-4c27-8919-00eccab73e2d; SdkMessageProcessingStepId: ab0fa3e5-3ceb-dc11-8840-0003ffbb159c; EntityName: contact; Stage: 50; MessageName: Create; AssemblyName: AlertCRM40Plugin.CRM40PostPlugIn, AlertCRM40Plugin, Version=1.0.0.0, Culture=en-US, PublicKeyToken=4406fa73b5445f20; ClassName: AlertCRM40Plugin.CRM40PostPlugIn; Exception: Unhandled Exception: System....

VBA Help #4
Hi, I have a list of clients with their associated outstanding dollar amount and their return (%). I would like to know if there is code that would allow me to break up these clients by return (%) groupings. For example, I would like to group below 10%, 10% to 17%, and above 17%. So at the client on the list that is just before 10% I would like to add two rows and put a dollar subtotal in the immediate row and sum the dollar amounts. Can anyone help? Thanks, Marino Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim LastR...

no option to add display name
I am running outlook xp on a both win98 and xp machines. In neither case can I find or set a display name for my email account (ie so others will see my display name). I have seen various suggestions about going to email accounts/change but there is just no option there for 'display name'. Is it something to do with the 'mode' of install? any advice appreciated david marcus David Marcus <dmarcus@netspeed.NO-SPAM.com.au> wrote: > I am running outlook xp on a both win98 and xp machines. In neither > case can I find or set a display name for my email account ...

Entourage: Calendar User Name Incorrect-Correct elsewhere.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi There, <br><br>It appears that whenever office was installed, the users name was misspelled. Now when he goes to setup a meeting, his own user name is misspelled. I've verified his own contact is set as default for himself. I've also deleted the office.pd and rerun the setup assistant and verified his name. <br><br>Is there a way to change that name? <br><br>Jaime On 2010-03-10 15:01:16 -0500, jaime_sf@officeformac.com said: > Hi Th...