CODEWITHSUNDEEP
vbaexcel
Paul Stroie
Paul Stroie

Reputation: 31

How can I copy a sheet to a new uncreated workbook?

This is a really dumb question but I don't find the answer in the book I have and at work I don't have internet. What I wanna do is to save a worksheet from the activeworkbook to a new file. I tried to do it this way but obviously you can't assign a worksheet directly to an uncreated workbook.

Public Sub SaveRaport() 
Dim ws As Worksheet 
Set ws = Worksheets("Raport") 
Dim wb As Workbook 
wb = ws 'here's the problem… I don't know how to do this thing 
Dim TempFilePath, TempFileName, FileExtStr, adrmail As String

wb.Save 
TempFilePath = "C:\Users\ionadr\Desktop\test" & "\" 
TempFileName = "Raportare" & " " & Format(data, "dd-mmm-yy ") & " schimbul " & schimb 
FileExtStr = "." & LCase(Right(wb.Name, Len(wb.Name) - InStrRev(wb.Name, ".", , 1))) 
    MsgBox ("Saving file...") 
    wb.SaveCopyAs TempFilePath & TempFileName & FileExtStr 
End Sub

I appreciate your help! Thanks

Upvotes: 3

Views: 245

Answers (3)

Financial Economist
Financial Economist

Reputation: 763

Here is the code that you need and that I use almost all the time. Assuming that you want to export a sheet called "Report" as C:\Report.xlsx

Sub ExportSummary()

Dim WsToExport As Worksheet
Set WsToExport = Sheets("Report")
Dim CurrentWbk As Workbook
Dim NewWbk As Workbook

Set CurrentWbk = ActiveWorkbook

Workbooks.Add
Set NewWbk = ActiveWorkbook

CurrentWbk.Activate
WsToExport.Select
WsToExport.Copy Before:=NewWbk.Sheets(1)

NewWbk.Activate
NewWbk.SaveAs "C:\Report.xlsx"
NewWbk.Close

End Sub

Upvotes: 0

Brad
Brad

Reputation: 12255

your wb = ws must be throwing errors. You are trying to set two objects equal to eachother that are different types (not possible).

Dim newWB As Workbook
Dim originalWorkbook As Workbook 'declare a variable of the workbook type (still an uncreated file)
Set newWB = Workbooks.Add() 'create your new file
Set originalWorkbook = ThisWorkbook
originalWorkbook.Sheets(2).Copy after:=newWB.Sheets(1) 'replace the sheet indicies I used with whatever yours are or with strings...

then you can save the workbook using the saveas method newWB.SaveAs()

Upvotes: 1

Paul McLain
Paul McLain

Reputation: 359

If you are simply looking to save the worksheet "Raport" and nothing else, something along these lines will get you started: 

Sub Report()

    Dim ws As Worksheet
    Set ws = Worksheets("Raport")

    ws.Copy
    ActiveWorkbook.SaveAs "NewCopy.xlsx"

End Sub

Obviously you'll have to modify the filename to suit your needs. If you don't want to save the new copy just yet, calling ws.Copy alone will create a new workbook with the data in the "Raport" spreadsheet (in my experience -- just tested the code a minute ago too).

Hope this helps.

Upvotes: 1

Related Questions