Naming worksheets...new to VBA

  • Thread starter Thread starter mjack003
  • Start date Start date
M

mjack003

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
 
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
 
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
 
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???
 
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
 
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
 
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
 
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
 
Back
Top