Saving specific sheet as new workbook

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
82
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"""
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,846
.
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
 

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
82
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   ThisWorkbook.Sheets("New Data").Copy 
    ActiveWorkbook.SaveAs "H:\documents\SaveNewFolder\FUT_" & dTod & "." & "xls"""
 

Adar123

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

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Are you running this from Excel?
 

Adar123

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

ADVERTISEMENT

Yes. Is there another way?
 

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
82
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
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
 

Adar123

Board Regular
Joined
Apr 1, 2018
Messages
82
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

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
Top