CODEWITHSUNDEEP
vbaexcel
Christian Emil Johansen
Christian Emil Johansen

Reputation: 191

Copy values of a worksheet to a new workbook

I'm trying to do something that seems simple, but keeps on causing me trouble.

Copying the active worksheet into a new workbook without formulas.

I've tried my luck with the following code:

Sub test()
ActiveSheet.Copy
With ActiveSheet.UsedRange
    .Value = .Value
End With
End Sub

But that is quite unreliable, as it sometimes doesn't copy fields that clearly has a value. I have cells that needs to have text that is both bold and normal and different sizes in the same damn cell.

The aforementioned method does not keep that formatting.

What I am currently doing looks like this:

Sub EksporterExcel()
Dim ws As Worksheet
Dim wb As Workbook
Dim tid As String

Set ws = Sheets(ActiveSheet.Name)
tid = Format(CStr(Now), "hh.mm.ss")

Application.DisplayAlerts = False
Application.ScreenUpdating = False

ws.Copy
Set wb = Workbooks(ActiveWorkbook.Name)
ws.UsedRange.Copy
wb.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

ActiveWorkbook.SaveAs _
        FileFormat:=51, _
        Filename:=Application.ThisWorkbook.Path & "\Udfyldte Indleveringsplaner\Excel\" & Date & "\" & ActiveSheet.Name & " Kl. " & tid & ".xlsx"

ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

And that works.

It is however slow. Very, very slow. I'm assuming that this has to do with copying the sheet first and the going back to copy the cell values, so I'd like a way to avoid that.

This way also doesn't keep the formatting in the single cells that has multiple formatting options. That's low priority however.

I would love to know if there's a more efficient way to do this.

Below is an example of the result when using the first snippet of code, or the code in the answer by jkpieterse.

The original sheet The original sheet before being copied.

The copied sheet The copied sheet

Some of the data is clearly lost after being copied

Upvotes: 0

Views: 1212

Answers (1)

jkpieterse
jkpieterse

Reputation: 2956

What about this version (also tidied up some of your code):

Sub EksporterExcel()
    Dim tid As String

    tid = Format(CStr(Now), "hh.mm.ss")

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    ActiveSheet.Copy

    ActiveSheet.UsedRange.Value2 = ActiveSheet.UsedRange.Value2

    ActiveWorkbook.SaveAs _
            FileFormat:=51, _
            Filename:=Application.ThisWorkbook.Path & "\Udfyldte Indleveringsplaner\Excel\" & Date & "\" & ActiveSheet.Name & " Kl. " & tid & ".xlsx"
    'Assume it is the active workbook you wanted to close...
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions