Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Hi,
I want to add sheet and call it a name. But when I record it a problem occurs. Everytime I add a sheet it is given a new name i.e. sheet 2, sheet 3, sheet 4 etc. This happens before I get a chance to rename which means the macro always fails. Any thoughts? I want to add a new sheet to at lot of workbooks - the sheet is called "Raabalance" /Heine |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Try an inputbox (or replace the inputboxline with a fixed name) Sub SheetInsert() Dim strNameSheet As String Sheets.Add strNameSheet = InputBox("give sheet name") ActiveSheet.Name = strNameSheet End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
worksheets.Add.name="Bob"
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Heine" wrote in message ups.com... Hi, I want to add sheet and call it a name. But when I record it a problem occurs. Everytime I add a sheet it is given a new name i.e. sheet 2, sheet 3, sheet 4 etc. This happens before I get a chance to rename which means the macro always fails. Any thoughts? I want to add a new sheet to at lot of workbooks - the sheet is called "Raabalance" /Heine |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
I thought to add a worksheet one used the workbooks.add method.
Confused.. Jim "Bob Phillips" wrote in message : worksheets.Add.name="Bob" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Heine" wrote in message ups.com... Hi, I want to add sheet and call it a name. But when I record it a problem occurs. Everytime I add a sheet it is given a new name i.e. sheet 2, sheet 3, sheet 4 etc. This happens before I get a chance to rename which means the macro always fails. Any thoughts? I want to add a new sheet to at lot of workbooks - the sheet is called "Raabalance" /Heine |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Thanks - that works great.
Problem is now that if I run the macro more than once an error occurs because I already added the sheet once. Is there an easy way to get around that small problem? /Heine Gert wrote: Try an inputbox (or replace the inputboxline with a fixed name) Sub SheetInsert() Dim strNameSheet As String Sheets.Add strNameSheet = InputBox("give sheet name") ActiveSheet.Name = strNameSheet End Sub |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Never mind; just having a "senior-moment".. daaaa
"Jim May" wrote: I thought to add a worksheet one used the workbooks.add method. Confused.. Jim "Bob Phillips" wrote in message : worksheets.Add.name="Bob" -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Heine" wrote in message ups.com... Hi, I want to add sheet and call it a name. But when I record it a problem occurs. Everytime I add a sheet it is given a new name i.e. sheet 2, sheet 3, sheet 4 etc. This happens before I get a chance to rename which means the macro always fails. Any thoughts? I want to add a new sheet to at lot of workbooks - the sheet is called "Raabalance" /Heine |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
the extended version Sub SheetInsert() Dim strNameSheet As String Dim boolFound As Boolean Dim MySheets As Worksheet Sheets.Add boolFound = False strNameSheet = InputBox("give sheet name") For Each MySheets In Worksheets If MySheets.Name = strNameSheet Then boolFound = True Next If boolFound Then MsgBox ("this sheet already exists") Else ActiveSheet.Name = strNameSheet End If End Sub |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Thanks Gert,
that´s quite crafty. One or two minor details, though: I would like, if possible to avoid the use of an inputbox. The macro still adds sheets called sheet 5, sheet 6, sheet 7 etc - can I avoid this problem? /Heine Gert wrote: the extended version Sub SheetInsert() Dim strNameSheet As String Dim boolFound As Boolean Dim MySheets As Worksheet Sheets.Add boolFound = False strNameSheet = InputBox("give sheet name") For Each MySheets In Worksheets If MySheets.Name = strNameSheet Then boolFound = True Next If boolFound Then MsgBox ("this sheet already exists") Else ActiveSheet.Name = strNameSheet End If End Sub |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Heine,
try this one: Sub SheetInsert() Dim strNameSheet As String Dim boolFound As Boolean Dim MySheets As Worksheet Sheets.Add boolFound = False strNameSheet = "Raabalance" For Each MySheets In Worksheets If MySheets.Name = strNameSheet Then boolFound = True Next If boolFound Then MsgBox ("this sheet already exists") Else ActiveSheet.Name = strNameSheet End If End Sub best regards Gert |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Thanks Gert - that is better.
Only one problem left as I see it. If I keep running the macro it keeps adding new sheets called sheet 6,7,8 etc. I would like those sheet to be deleted or not to be added in the first place. Any ideas? best regards Heine Gert wrote: Heine, try this one: Sub SheetInsert() Dim strNameSheet As String Dim boolFound As Boolean Dim MySheets As Worksheet Sheets.Add boolFound = False strNameSheet = "Raabalance" For Each MySheets In Worksheets If MySheets.Name = strNameSheet Then boolFound = True Next If boolFound Then MsgBox ("this sheet already exists") Else ActiveSheet.Name = strNameSheet End If End Sub best regards Gert |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
Sorry Heine, it was my faulth,
it depends on the moment when you add the new sheet. I've replaced the line Sheets.Add and now it only adds a sheet when "Raabalance" isn't in use allready Sub SheetInsert() Dim strNameSheet As String Dim boolFound As Boolean Dim MySheets As Worksheet boolFound = False strNameSheet = "Raabalance" For Each MySheets In Worksheets If MySheets.Name = strNameSheet Then boolFound = True Next If boolFound Then MsgBox ("this sheet already exists") Else Sheets.Add ActiveSheet.Name = strNameSheet End If End Sub hopes this works as you wanna have it best regards Gert |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Add sheets using macro
That works like a charm, Gert. Thanks so much for taking your time to
help. Wish I were better at writing these codes:-) Best Regards Heine Gert wrote: Sorry Heine, it was my faulth, it depends on the moment when you add the new sheet. I've replaced the line Sheets.Add and now it only adds a sheet when "Raabalance" isn't in use allready Sub SheetInsert() Dim strNameSheet As String Dim boolFound As Boolean Dim MySheets As Worksheet boolFound = False strNameSheet = "Raabalance" For Each MySheets In Worksheets If MySheets.Name = strNameSheet Then boolFound = True Next If boolFound Then MsgBox ("this sheet already exists") Else Sheets.Add ActiveSheet.Name = strNameSheet End If End Sub hopes this works as you wanna have it best regards Gert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
error when running cut & paste macro | Excel Worksheet Functions | |||
Microsoft Visual Basic: Compile error: Sum or Function not defined | Excel Worksheet Functions | |||
Macro that stores all sheets as tab-delimited text files | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Macro to link Sheets to main workbook | Excel Discussion (Misc queries) |