CODEWITHSUNDEEP
excelvba
nootskej
nootskej

Reputation: 69

Copy entire Excel worksheet without formulas

Any recommendations please? I’ve tried 

Ws = ActiveWorkbook.Sheets(“tab”)
ThisWorkbook.Sheets(“tab”).UsedRange.Value = Ws.UsedRange.Value

but no luck

I would like to copy all of the info over to another file but have tried a few methods but gotten stuck

Upvotes: 0

Views: 2292

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Give something like this a try:

Sub KopyKat()
    Sheets("original").Cells.Copy
    Sheets("kopy").Cells.PasteSpecial (xlPasteValues)
End Sub

(use your own worksheet names)

EDIT#1:

It is better if the kopy worksheet does not exist when we start:

We will

  • copy the entire worksheet (not just the cells)
  • rename the copy
  • convert all formulas to values



Sub KopyKat2()
    With Sheets("original")
        .Copy after:=Sheets("original")
    End With

    With ActiveSheet
        .Name = "kopy"
        With .UsedRange
            .Value = .Value
        End With
    End With
End Sub

Now kopy also has all the formats as the original as well and column widths, row heights, etc.

Upvotes: 2

Related Questions