Worksheets.Add

[XLML]

How can Worksheets.Add in vba be modified to insert a worksheet to the Right and not Left of the Active Sheet?

Thanks in advance,
XLML

 

[pgc01]

Hi XLML

Try

Worksheets.Add after:=ActiveSheet

HTH
PGC

 

[XLML]

Thanks pgc01. I am having problems because I am attempting to add the last sheet of a workbook.


XLML

 

[pgc01]

Hi again

Try

Worksheets.Add.Move After:=Worksheets(Worksheets.Count)

HTH
PGC

 

[agihcam]

if you mean adding sheet to the right of the last sheet then;

Worksheets.Add after:=Sheets(Sheets.Count)

 

[pgc01]

XLML

agihcam's solution is better. Sheets includes both worksheets and chart sheets. This assures that the new worksheet is the last sheet in the workbook.

Cheers
PGC

 

[XLML]

Thanks for the solutions. It works well for my purposes!
XLML

 

[DKcrm]

I've been looking for something kinda like this.


All my sheets are numbered and in numerical order going from 1 to infinite.

I'm copying the master sheet "Master", and the new sheet would go to the end, named as the next available number..

If "3" is the last, the next would be "4" and so on....

Anyone?

 

[pgc01]

Hi DKcrm

I'm not sure I understood well.

This is what I understood:

You start with a workbook with one worksheet named "Master"

You now want to make copies of the worksheet "Master", that go to the end and have the names "1", "2", "3", ...

If this is the case, try:

Worksheets("Master").Copy after:=Sheets(Sheets.Count)

If Worksheets(Worksheets.Count - 1).Name = "Master" Then
    Worksheets(Worksheets.Count).Name = 1
Else
    Worksheets(Worksheets.Count).Name = Worksheets(Worksheets.Count - 1).Name + 1
End If

Hope this helps
PGC

 

[DKcrm]

pgc01>>

This was exactly what I was looking for. It works perfectly. Thank you

Using this gives me a copy of the master. Now, I need to make some changes in every worksheet with a number.

What should I do to alter the code so it is possible to copy the worksheet I'm in (random) and put the copy in the end?