Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Sheet Naming, and code trimming.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Sheet Naming, and code trimming.
I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without having
to go through VB naming it "SheetXX" first?
My code works only if this is the first sheet opened in
the workbook. But sometimes I need to insert other sheets
before I run the macro, therefore I would need an absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 811
Default Sheet Naming, and code trimming.
Hi Victor,

I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without having
to go through VB naming it "SheetXX" first?

Worksheets.Add.Name = "New"

Also is there a way to trim this?

Assuming your intention is to copy the formula to all of those other
cells, this should do it:

Range("E11").FormulaR1C1 = _
"=IF(R[-1]C="""","""",NOW()-INT(NOW()))"
Range("E14").Formula = Range("E11").Formula
Range("E17").Formula = Range("E11").Formula
Range("E20").Formula = Range("E11").Formula
Range("E23").Formula = Range("E11").Formula
Range("M11").Formula = Range("E11").Formula
Range("M14").Formula = Range("E11").Formula
Range("M17").Formula = Range("E11").Formula
Range("M20").Formula = Range("E11").Formula
Range("M23").Formula = Range("E11").Formula

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Victor H" wrote in message
...
My code works only if this is the first sheet opened in
the workbook. But sometimes I need to insert other sheets
before I run the macro, therefore I would need an absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 811
Default Sheet Naming, and code trimming.

On second thought, the second answer is probably not what you're looking
for because it doesn't cause the formula reference to adjust for the
additional cells. Here's some code that puts everything in one small
procedu

Sub AddSheetEnterFormula()
Dim szFormula As String
Worksheets.Add.Name = "New"
szFormula = "=IF(R[-1]C="""","""",NOW()-INT(NOW()))"
Range("E11,E14,E17,E20,E23,M11,M14,M17,M20,M23").F ormula = szFormula
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rob Bovey" wrote in message
...
Hi Victor,

I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without having
to go through VB naming it "SheetXX" first?

Worksheets.Add.Name = "New"

Also is there a way to trim this?

Assuming your intention is to copy the formula to all of those other
cells, this should do it:

Range("E11").FormulaR1C1 = _
"=IF(R[-1]C="""","""",NOW()-INT(NOW()))"
Range("E14").Formula = Range("E11").Formula
Range("E17").Formula = Range("E11").Formula
Range("E20").Formula = Range("E11").Formula
Range("E23").Formula = Range("E11").Formula
Range("M11").Formula = Range("E11").Formula
Range("M14").Formula = Range("E11").Formula
Range("M17").Formula = Range("E11").Formula
Range("M20").Formula = Range("E11").Formula
Range("M23").Formula = Range("E11").Formula

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Victor H" wrote in message
...
My code works only if this is the first sheet opened in
the workbook. But sometimes I need to insert other sheets
before I run the macro, therefore I would need an absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
No Name
 
Posts: n/a
Default Sheet Naming, and code trimming.
Thanks Bob,

That looks AND works great!

V

-----Original Message-----

On second thought, the second answer is probably not
what you're looking
for because it doesn't cause the formula reference to
adjust for the
additional cells. Here's some code that puts everything
in one small
procedu

Sub AddSheetEnterFormula()
Dim szFormula As String
Worksheets.Add.Name = "New"
szFormula = "=IF(R[-1]C="""","""",NOW()-INT(NOW()))"
Range
("E11,E14,E17,E20,E23,M11,M14,M17,M20,M23").Formul a =
szFormula
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Rob Bovey" wrote in message
...
Hi Victor,

I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without
having
to go through VB naming it "SheetXX" first?

Worksheets.Add.Name = "New"

Also is there a way to trim this?

Assuming your intention is to copy the formula to
all of those other
cells, this should do it:

Range("E11").FormulaR1C1 = _
"=IF(R[-1]C="""","""",NOW()-INT(NOW()))"
Range("E14").Formula = Range("E11").Formula
Range("E17").Formula = Range("E11").Formula
Range("E20").Formula = Range("E11").Formula
Range("E23").Formula = Range("E11").Formula
Range("M11").Formula = Range("E11").Formula
Range("M14").Formula = Range("E11").Formula
Range("M17").Formula = Range("E11").Formula
Range("M20").Formula = Range("E11").Formula
Range("M23").Formula = Range("E11").Formula

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Victor H" wrote in message
...
My code works only if this is the first sheet opened
in
the workbook. But sometimes I need to insert other
sheets
before I run the macro, therefore I would need an
absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW
()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.





.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Sheet Naming, and code trimming.
Hi Victor

Worksheets.Add.Name = "New"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Victor H" wrote in message
...
I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without having
to go through VB naming it "SheetXX" first?
My code works only if this is the first sheet opened in
the workbook. But sometimes I need to insert other sheets
before I run the macro, therefore I would need an absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default Sheet Naming, and code trimming.
That worked! Thanks


-----Original Message-----
Hi Victor

Worksheets.Add.Name = "New"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Victor H" wrote in message
...
I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without having
to go through VB naming it "SheetXX" first?
My code works only if this is the first sheet opened in
the workbook. But sometimes I need to insert other
sheets
before I run the macro, therefore I would need an
absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW
()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.



.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default Sheet Naming, and code trimming.
Viktor,

in answer to your first question, when you add a sheet it
automatically becomes the actie sheet, so you can use:

Sheets.Add
Activesheet.Name = "New"

to get round the naming problem.

As to the second part, one of the main thins the recorder
does which you can strip out to save time is SELECTing
ranges then working on the Selection. So your code could
be simplified to:

Range("E11").FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-INT
(NOW()))"
Range("E11").Copy
Activesheet.paste Range("E14")
Activesheet.paste Range("E17")
Activesheet.paste Range("E20")
ActiveSheet.Paste Range("E23")
Activesheet.paste Range("M11")
Activesheet.paste Range("M14")
Activesheet.paste Range("M17")
Activesheet.paste Range("M20")
Activesheet.paste Range("M23")
Activesheet.paste Range("E11")

Application.CutCopyMode = False
Range("C10").Select

However, as you appear to be pasting the formula at
regualr intervals, you could use a loop to further
simplify to:

Dim X As Integer
Dim Y as Integer

Range("E11").FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-INT
(NOW()))"
Range("E11").Copy

For X = 5 to 13 step 9
For Y = 11 to 23 step 3

Activesheet.paste cells(y,x)

Next y
next X

Application.cutcopymode = false
Range("C10").select

Cheers, Pete.

-----Original Message-----
I have the following code:

Sheets.Add
Sheets("Sheet1").Name = "New"

Is there a way to directly name the sheet without having
to go through VB naming it "SheetXX" first?
My code works only if this is the first sheet opened in
the workbook. But sometimes I need to insert other sheets
before I run the macro, therefore I would need an
absolute
naming routine rather than the one VB provides me.

Also is there a way to trim this?

Range("E11").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C="""","""",NOW()-
INT(NOW()))"
Range("E11").Select
Selection.Copy
Range("E14").Select
ActiveSheet.Paste
Range("E17").Select
ActiveSheet.Paste
Range("E20").Select
ActiveSheet.Paste
Range("E23").Select
ActiveSheet.Paste
Range("M11").Select
ActiveSheet.Paste
Range("M14").Select
ActiveSheet.Paste
Range("M17").Select
ActiveSheet.Paste
Range("M20").Select
ActiveSheet.Paste
Range("M23").Select
ActiveSheet.Paste
Range("E11").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C10").Select

This has been written using the recorder but I'm sure
there's a more elegant, leaner way to write it.

Thanks in advance for any help.

.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Code Help for naming a worksheet tab Very Basic User Excel Discussion (Misc queries) 3 November 11th 09 12:21 PM
Naming a reference table for use in a VBA Code CAT Excel Discussion (Misc queries) 4 October 2nd 08 01:36 PM
naming sheet tab artist4christ Excel Worksheet Functions 5 January 17th 07 11:54 PM
protection de-activating tab-naming code Stilla Excel Worksheet Functions 2 May 23rd 06 05:21 PM
Code for naming worksheet name range?? Sandy[_3_] Excel Programming 2 September 7th 03 04:49 AM


All times are GMT +1. The time now is 09:34 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement