Saving specific sheet as new workbook

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
83
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hi all,

I am trying to save the sheet "New Data" (not an active sheet) in an active workbook as a new workbook and clear the sheet "New Data" after that.

The code I wrote clearly does not work. Tried a few methods...appreciate help. Thank you.
VBA Code:
'***save file under specified name***
    Set wkBk = Workbooks.Add
    ThisWorkbook.Sheets("New Data").Copy 
    wkBk.SaveAs "H:\documents\SaveNewFolder\FUT_" & dTod & "." & "xls"""
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
.
Look at this for an example. Edit the sheet name as required, the workbook name as required and the path.

VBA Code:
Option Explicit

Sub SveWB()

Dim wb, wbnew As Workbook
Dim ws As Worksheet
Dim filename As String

Set wb = ThisWorkbook 'Name of the workbook you are copying from
Set ws = wb.Sheets("TOTAL") 'Name of sheet you are copying

Application.DisplayAlerts = False
filename = "Total" & ".xlsm"

'adds new workbook
Set wbnew = Workbooks.Add
wb.Activate

Sheet1.CommandButton1.Visible = False

'copies sheet to new workbook
wb.Sheets("TOTAL").Copy Before:=wbnew.Sheets(1)

ActiveWorkbook.SaveAs Environ("USERPROFILE") & "\Desktop\EmailGroup\Total.xlsx"
Sheet1.CommandButton1.Visible = True
Application.DisplayAlerts = True

wbnew.Close

End Sub
 
Upvote 0
I get type mismatch error 13 when reach to this line. What could be the reason?

VBA Code:
Set wb = TestMacroBook 'Name of the workbook you are copying from

The code looks as follows:
VBA Code:
  another macro here...
End Sub

Sub SaveWB()
Dim wb, wbnew As Workbook
Dim ws As Worksheet
Dim filename As String

Set wb = TestMacroBook 'Name of the workbook you are copying from
Set ws = wb.Sheets("NewData") 'Name of sheet you are copying

Application.DisplayAlerts = False
filename = "FUT" & ".xls"

'adds new workbook
Set wbnew = Workbooks.Add
wb.Activate

'copies sheet to new workbook
wb.Sheets("NewData").Copy Before:=wbnew.Sheets(1)

ActiveWorkbook.SaveAs "H:\documents\1. Trading\Excel\SaveNewFolder\FUT.xls"
Application.DisplayAlerts = True

wbnew.Close

End Sub

Thank you for your help.
 
Upvote 0
How about
VBA Code:
   ThisWorkbook.Sheets("New Data").Copy 
    ActiveWorkbook.SaveAs "H:\documents\SaveNewFolder\FUT_" & dTod & "." & "xls"""
 
Upvote 0
I figured how to solve the error (see updated code below). However, I do get the error "1004 copy method of WorkSheet Class has failed". A new book is generated but it does not proceed to copy-pasting the data and saving the new workbook under a specified name in the destination folder.

The debugger points me to this line:
VBA Code:
wkBk.Sheets("NewData").Copy Before:=wkBknew.Sheets(1)

Tried the syntax mentioned in the follow-up response above, it did not work.

Full code:
VBA Code:
Dim wkBk, wkBknew As Workbook
Dim ws As Worksheet
Dim filename As String
Dim myApp As Excel.Application

Set myApp = CreateObject("Excel.Application")
Set wkBk = myApp.Workbooks.Open("H:\documents\1. Trading\Excel\TestMacroBook2.xlsm") 'Name of the workbook you are copying from
Set ws = wkBk.Sheets("NewData") 'Name of sheet you are copying

Application.DisplayAlerts = False
filename = "FUT" & ".xls"

'adds new workbook
Set wkBknew = Workbooks.Add
wkBk.Activate

'copies sheet to new workbook
wkBk.Sheets("NewData").Copy Before:=wkBknew.Sheets(1)

ActiveWorkbook.SaveAs "H:\documents\1. Trading\Excel\SaveNewFolder\FUT.xls"
Application.DisplayAlerts = True

wkBknew.Close

End Sub
 
Upvote 0
Are you running this from Excel?
 
Upvote 0
I figured that the issue is specifically with part of the code responsible to paste data:
VBA Code:
Before:=wkBknew.Sheets(1)

Once I remove it, the code run smoothly. Except no data is pasted in the closed workbook.
 
Upvote 0
How about
VBA Code:
Dim wkBk As Workbook

Set wkBk = Workbooks.Open("H:\documents\1. Trading\Excel\TestMacroBook2.xlsm") 'Name of the workbook you are copying from

wkBk.Sheets("NewData").Copy

ActiveWorkbook.SaveAs "H:\documents\1. Trading\Excel\SaveNewFolder\FUT.xls"
ActiveWorkbook.Close
 
Upvote 0
Thank you.

All of the options above would work but the latest is the shortest.

The only difference is to point to the correct workbook. The previous code was attempting to open another version of the file I am running the macro from, hence it had issued with creating a copy.
VBA Code:
Set wkBk = ActiveWorkbook

Many thanks for your help!

Full syntax for future use:
VBA Code:
Dim wkBk As Workbook

Set wkBk =  ActiveWorkbook 'assumes the macro is run from the workbook where the data is also stored and is intended to be copied from

wkBk.Sheets("NewData").Copy

ActiveWorkbook.SaveAs "H:\documents\1. Trading\Excel\SaveNewFolder\FUT.xls"
ActiveWorkbook.Close
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top