Sheet Naming, and code trimming.

  • Thread starter Thread starter Victor H
  • Start date Start date
V

Victor H

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.
 
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?
Click to expand...

Worksheets.Add.Name = "New"
Also is there a way to trim this?
Click to expand...

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 said:
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.
Click to expand...
 
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)
 
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
procedure:

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").Formula = 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 said:
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?
Click to expand...

Worksheets.Add.Name = "New"
Also is there a way to trim this?
Click to expand...

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 said:
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.
Click to expand...
Click to expand...
 
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.
 
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 said:
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.
Click to expand...


.
Click to expand...
 
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
procedure:

Sub AddSheetEnterFormula()
Dim szFormula As String
Worksheets.Add.Name = "New"
szFormula = "=IF(R[-1]C="""","""",NOW()-INT(NOW()))"
Range
Click to expand...
("E11,E14,E17,E20,E23,M11,M14,M17,M20,M23").Formula =
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 said:
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?
Click to expand...

Worksheets.Add.Name = "New"
Also is there a way to trim this?
Click to expand...

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 said:
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.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Code and a sort problem 1
Long VBA Code - Can it be reduced 11
CopyPasteCode 5
Using Autofilter within a macro 3
I need help with a "Run-Time Error 13: Type Mismatch" 2
VBA Copying/Pasting problem when data is filtered 1
How to copy from each sheet and paste to a summary sheet 2
Streamlining my code 1

Back
Top