CODEWITHSUNDEEP
vbaexcel
lucas
lucas

Reputation: 55

VBA (excel) copy worksheet to new workbook

right now I am trying to create an excel macro which should copy one worksheet from my workbook. This worksheet should be saved as a new Excel file named with the value from cell B1. Everything works fine so far. The problem is: I want that the copy is a back-up. So the values in the table (copy) should not be connected to the original table. So to make it short: I just want to copy format+values but not the formulas from the table. Do you have any ideas how I can make this work?

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Dim FName           As String
Dim FPath           As String

Dim wbkExport As Workbook
Dim shtToExport As Worksheet
FPath = "C:\Users\User\Desktop\Artikelnummern"
FName = Worksheets("Test").Cells(1, 2).Value

Set shtToExport = ThisWorkbook.Worksheets("Test")
Set wbkExport = Application.Workbooks.Add
shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)
Application.DisplayAlerts = False                       'Possibly overwrite 
without asking
wbkExport.SaveAs Filename:=FPath & "\" & FName & ".xlsx"
Application.DisplayAlerts = True
wbkExport.Close SaveChanges:=False

End Sub

Upvotes: 0

Views: 4720

Answers (1)

Shai Rado
Shai Rado

Reputation: 33692

Replace your line: 

shtToExport.Copy Before:=wbkExport.Worksheets(wbkExport.Worksheets.Count)

With:

shtToExport.Cells.Copy
wbkExport.Worksheets(wbkExport.Worksheets.Count - 1).Range("A1").PasteSpecial xlPasteValues
wbkExport.Worksheets(wbkExport.Worksheets.Count - 1).Range("A1").PasteSpecial xlPasteFormats

Upvotes: 1

Related Questions