Doh!!
if you want formulas copied then
Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

Sub CopySheet()
Dim lCell as Range 'LastCell
Set lCell = FindLastCell
Sheets("Paste").Activate
Range("A1",lCell).Copy
Sheets("format").Activate
Range("A1").PasteSpecial xlPasteAll
End Sub

If you only want to paste Values change xlPasteAll to xlPasteValues

HTH

Die_Another_Day
P.S. Sry 'bout the misunderstanding
Michael Vaughan wrote:
Hi HTH,

Well, that works kind of. It will make up a new sheet called "format".
But, I wanted to copy it into a sheet that already exits called "format".
With the commands you gave me, it errors out and says, "Cannot rename sheet
to the same name as another sheet".

If the sheet does not exist, then it works. But I wanted to copy it into a
sheet that already exits, "format".

Thanks,

Mikey Vaughan

"Die_Another_Day" wrote in message
ups.com...
Simply declare Befo
ActiveSheet.Copy Befo=Sheets("Paste")
ActiveSheet.Name = "format"

HTH

Die_Another_Day
Michael Vaughan wrote:
Hello Everyone,

I have VBA code that takes a sheet and copies it, and then formats it to
what I need. But, the copy method that I use, is copying it to a new
book
with the same sheet name.

ActiveSheet.Copy

Is there a way to copy the contents into the same book but to a different
sheet name instead of copying it to a completely new book??
For example:

I want to copy the contents of sheet "paste" into sheet "format".

Thanks,

Mikey Vaughan