Hi Gemz

Thought i would join in here... It would seem that you want to copy
all the data in a worksheet then create a new worksheet and paste this
data into it? Would it not be easier to copy the and paste the
worksheet rather than what it holds??? Anywho the code below would do
just that.

Option Explicit
Dim i As Integer

Private Sub CommandButton1_Click()

i = Worksheets.Count

Sheets("Sheet3").Copy After:=Sheets(i)


With Sheets(i + 1)

.Name = "B"

.Cells.EntireColumn.AutoFit

.Cells.EntireRow.AutoFit

End With

End Sub

Steve