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.

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.

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.

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.

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.

.

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.



.

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.





.