VBA to Save selected sheets as new Workbook

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 19, 2010

• #2

Try like this

Code:

Sheets(Array("Sheet1", "Sheet4", "Sheet5")).Copy

 

Upvote 0

I

initiator

New Member

Joined

May 13, 2010

Messages

16

• Oct 19, 2010

• #3

Thanks a lot Peter! . It works!

There's a small issue, however. One of the sheets has formulae, and copied sheet in the newly saved file is still referencing to those formula in the main sheet. Any way to copy values and formats only?

thanks

 

Upvote 0

VoG

Legend

Joined

Jun 19, 2002

Messages

63,650

• Oct 19, 2010

• #4

Try

Code:

Dim ws As Worksheet
Sheets(Array("Sheet1", "Sheet4", "Sheet5")).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws

 

Upvote 0

I

initiator

New Member

Joined

May 13, 2010

Messages

16

• Oct 19, 2010

• #5

Thanks!

Works Perfectly!

 

Upvote 0

E

eagerbeav3r

New Member

Joined

Feb 24, 2022

Messages

14

Office Version

1. 365

Platform

1. Windows

• Jun 15, 2022

• #6

I was searching for a solution and this basically does it. I dont have much experience in VBA so i got an additional question:

Can you also use a specific path to save the new Spreadsheet i an Folder?
And i also want to use an custom Name for the File.
What do i have to add to your code VoG?

Thank you in advance.

 

Upvote 0

Y

yschang

New Member

Joined

Aug 20, 2022

Messages

1

Office Version

1. 2019

Platform

1. Windows

• Aug 20, 2022

• #7

eagerbeav3r said:

I was searching for a solution and this basically does it. I dont have much experience in VBA so i got an additional question:

Can you also use a specific path to save the new Spreadsheet i an Folder?
And i also want to use an custom Name for the File.
What do i have to add to your code VoG?

Thank you in advance.

Click to expand...

WS.Copy
'add path
ChDir "D:\Alex\汇报工作\K3"
ActiveWorkbook.SaveAs Filename:="D:\Alex\汇报工作\K3\" & ActiveSheet.Name & ".xlsx"
ActiveWorkbook.Close

 

Upvote 0