Create new sheet

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

I need to copy the active sheet to a new sheet but I need
it to paste only the values and formats.

How would I do this?


Thanks
Todd
 
Something like this might work, just change the sheet names as needed.


Sheets("Sheet1").Cells.Copy

Sheets("Sheet2").Cells.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False

Sheets("Sheet2").Cells.PasteSpecial _
Paste:=xlPasteFormats, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
 
I cant use the sheet name or reference to a sheet because
there could be any amount of sheets already in the
workbook and also I will not know the sheet names until
they are created.

I need the code to just make a new sheet.
 
One of the best ways to learn code is to use the macro recorder to record
your actions.

Tools Menu/Macros/Record New Macro

After recording your macro you can view it by:
Pressing Alt + F11 (this takes you to the Visual Basic Editor
Press Control + r (this shows you project explorer)
Then look for the modules folder and expand it. The first macro recorded is
usually in Module1.

Have a look at the code and adapt it to suite your needs.
 
Back
Top