Sheets.add Type:= ?

[roemun]

What is the correct method to duplicate the last sheet when using Sheets.add Type: (how to finish this statement to have the newsheet be the same as the preceding sheet)

 

[mikerickson]

To duplicate a sheet:

Sheets("SheetName").Copy After:Sheets("SheetName")

VBA help has more info about the After and Before arguments of the Sheet.Copy method.

 

[roemun]

Thanks. But I'm using this in a macro and I need to do in the form

Sheets.Add Type:

 

[mikerickson]

The code I posted goes inside a macro.

"I need to do this in the form ..." sounds like homework.
If so, the VBEditor Help explains the use of the Add Method for worksheets.

If its not homework, why do you need to use the .Add method rather than .Copy?

 

[roemun]

Thanks, again.
It is an assignment. I have looked in the MSDN site for an answer, but the only one I can find is for a file in another workbook, with the file path after Type:. But I thought there must be a more efficient way to accomplish this if I want to duplicate the preceding sheet in the active wbk, while still using the Sheets.Add method.

 

[mikerickson]

You might try using the Macro Recorder to get the syntax for copying a sheet.

 

[roemun]

Thanks alot, I'll try it.

 

[Weaver]

Excel help says:

expression.Add(Before, After, Count, Type)

expression Required. An expression that returns one of the above objects.
Before Optional Variant. An object that specifies the sheet before which the new sheet is added.
After Optional Variant. An object that specifies the sheet after which the new sheet is added.
Count Optional Variant. The number of sheets to be added. The default value is one.
Type Optional Variant. Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. The default value is xlWorksheet.

so

sheets.Add type:=sheets(sheets.count).type

adds a sheet of the same type as the last sheet in the book and

sheets.Add type:=activesheet.type

adds a sheet of the same type as the currently selected sheet.

I'd be surprised if the macro recorder would give you this syntax, but I have to say I didn't know any of this until I read the help file. **** useful.

 

[roemun]

Thanks, but those two sheets.add statements only insert a xlsheet without any formatting. I was hoping to find a add statement that would duplicate the last, or the active sheet.

 

[Norie]

If you want to duplicate a sheet use Copy, type refers to a generic type of worksheet, not one you've created.

If you wanted you could create a template worksheet with all the formatting but not the data and copy that.