How to copy -paste exactly except formula

**suman.biswas** · 04-07-2010, 10:47 AM

Hi,
I need to copy and paste one sheet to another sheet exactly (values, formatting, grouping, zooming etc) but except formula.

How can I do it. Please provide me the code for this 'paste special' work.

Thanks in advance,
Suman

**davegugg** · 04-07-2010, 11:36 AM

Hi

Not sure what you mean by "grouping", but this will do the other three things you asked for.

Public Sub suman()

Dim SourceSheet As String
Dim DestSheet As String
Dim Szoom As Integer

SourceSheet = "Sheet1" 'Set to your source sheet
DestSheet = "Sheet3" ' Set to your destination sheet
Szoom = ActiveWindow.zoom

Sheets(SourceSheet).Activate
Sheets(SourceSheet).Cells.Copy

With Sheets(DestSheet)
    .Activate
    .Cells(1, 1).PasteSpecial xlPasteValues
    .Cells(1, 1).PasteSpecial xlPasteFormats
    ActiveWindow.zoom = Szoom
End With

Application.CutCopyMode = False

End Sub