?
Solved

Copy a worksheet into a new work book in xlsm format

Posted on 2014-07-14
15
Medium Priority
?
1,213 Views
Last Modified: 2014-07-15
I have a workbook with 10 different work sheet. I would like to copy only one worksheet into a new workbook. I am using excel 2010.  The sheet name is Trial. Only the records with a sheet name of "trail" should be copied. Rest should not be copied. The output workbook should be saved with the sheet name.
For example: Trail.xlsm.
I prefer the output to be saved in xlsm format.
Thanks.
0
Comment
Question by:karthik80c
15 Comments
 
LVL 5

Expert Comment

by:dani gammon
ID: 40195384
Hi there,

*Right-click on the worksheet tab you want to copy.
*Click-on "Move or Copy"
*Make sure that the right worksheet is selected
*On: "Move selected sheets To book: choose the new workbook.
*Click on "Create a copy"
*Click OK

You will have a copy of the worksheet in the new workbook.  You will have to save the new workbook.

Hope this helps!The
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 40195385
Do you want this done with VBA or manually?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40195412
Sub CopySheet()
    Dim xls As Excel.Worksheet
    
    For Each xls In ActiveWorkbook.Sheets
        If UCase(xls.Name) = "TRIAL" Then
            Dim xlb As Excel.Workbook
            Set xlb = Workbooks.Add()
            xls.Copy Before:=xlb.Sheets("Sheet1")
            xlb.SaveAs "TRIAL.xlsm", xlOpenXMLWorkbookMacroEnabled
            xlb.Close (True)
        End If
    Next xls
End Sub

Open in new window


Just add this as a macro
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 36

Expert Comment

by:Norie
ID: 40195453
All you need to copy a worksheet named 'Trail' to a new workbook and save it in xlsm format is this.ldmis this.
Sheets("Trail).Copy

ActiveWorkbook.SaveAs Filename:="Trail.xlsm", FileFormat:=54

Open in new window

0
 

Author Comment

by:karthik80c
ID: 40196988
Thank you imnorie and randy. The issue I am facing the excel sheet gets hanged while doing to save into a new workbook.
The excel sheet has lot of data for different sheets. But this sheet has only 5 records. Other than that there are other macros for different purposes. But I am executing only this sheet macro. It still gets hanged while trying to save.
0
 

Author Comment

by:karthik80c
ID: 40196997
dani and Macro...
I am looking for VBA code not manually. Thank you for replying to my post. Appreciate it.
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 2000 total points
ID: 40197043
It is possible you are getting a save conflict,try this:
Sub CopySheet()
    Dim xls As Excel.Worksheet
    
    For Each xls In ActiveWorkbook.Sheets
        If UCase(xls.Name) = "TRIAL" Then
            Dim xlb As Excel.Workbook
            Set xlb = Workbooks.Add()
            xlb.Application.DisplayAlerts = False
            xls.Copy Before:=xlb.Sheets("Sheet1")
            xlb.SaveAs "TRIAL.xlsm", xlOpenXMLWorkbookMacroEnabled, AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
            xlb.Close False
        End If
    Next xls

End Sub

Open in new window

0
 

Author Comment

by:karthik80c
ID: 40197230
Thank you so much Randy for a quick reply. But the code still hangs. I did try your code.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197240
Does the file "TRIAL.xlsm" exist in the directory your workbook that is running the macro resides in?
0
 

Author Comment

by:karthik80c
ID: 40197260
yes. Randy. It is in the same location.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197264
If you delete that file before running the macro does the macro still freeze?
0
 

Author Comment

by:karthik80c
ID: 40197519
Yes. Randy. It still freezes. :(  Thank you so much for your support.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40197538
Hmm, can you upload the workbook so I can try it
0
 

Author Comment

by:karthik80c
ID: 40197703
I cannot do that as it fails only in the client system. He does not share the whole file with me as it is confidential. But your code is perfectly good so I will accept you as the solution. Only issue is its failing due to that excel sheet having too many macros and records. Thanks again for your effort and help. Appreciate it.
0
 

Author Closing Comment

by:karthik80c
ID: 40197706
Good work. Appreciate his handwork in helping to resolve the issue.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question