none
Copy workshets from 1 workbook to another RRS feed

  • Question

  • Hi,

    I have workbook which has two worksheets - let's call them ShA and ShB.

    These are both formatted but have no formulas.

    During my macro execution I create a new workbook using the  Workbooks.Add method.

    I want to copy ShA and ShB  from my workbook to the new workbook, and name the sheets ShA and ShB, and call the new workbook "Output.xlsx".

    I have tried various things with mixed results - and though I can get it to work the code looks a bit untidy and I guess not efficient.

    there must be an easier way.

    Can you help please?

    thank you.

    Peter

    Tuesday, October 4, 2016 6:49 PM

Answers

  • Hello Peter,

    Do not need to add new workbook first; the worksheet copy method does that for you and the worksheets will automatically inherit the name of the source worksheets with the code example below. The copied worksheets will have all formatting etc same as source.

    You have said source does not contain formulas but for future reference, if the worksheets contain formulas then it is possible that some or all will point back to the original source location and become links to the source and more code is required to remove those links.

    I have made the following assumptions

    • The code will be installed in the source workbook (workbook to be copied).
    • The source workbook will be a macro enabled.
    • The output workbook to be saved in same path as source workbook.
    • The output workbook will be a standard xlsx (non macro) workbook.

    Feel free to get back to me if any problems.

    Sub CopyMultShtsToNewWorkBook()
       
        Worksheets(Array("ShA", "ShB")).Copy
       
        'New workbook becomes the Active Workbook
        ActiveWorkbook.SaveAs Filename:= _
                        ThisWorkbook.Path & "\Output.xlsx", _
                        FileFormat:=xlOpenXMLWorkbook
           
        'Following line is optional to close the newly created workbook
        ActiveWorkbook.Close SaveChanges:=False 'Already saved. No need to save again
           
    End Sub


    Regards, OssieMac

    • Marked as answer by py1 Wednesday, October 5, 2016 8:13 AM
    Wednesday, October 5, 2016 4:45 AM
  • Hi PY1,

    if your source workbook contains only this 2 sheets then you just need to write a single line code like below.

    ActiveWorkbook.SaveCopyAs "C:\CopyOfMyWorkbook.xlsx"

    if your source workbook have multiple sheets but you just want to copy particular sheet then you can try to use the code mentioned below.

    Sub demo() 
        Windows("Workbook1.xls").Activate 
        Sheets("Sheet1").Select 
        Sheets("Sheet1").Copy Before:=Workbooks("Workbook2.xls").Sheets(1) 
         
        Windows("Workbook1.xls").Activate 
        Sheets("Sheet2").Select 
        Sheets("Sheet2").Copy Before:=Workbooks("Workbook2.xls").Sheets(2) 
         
       
    End Sub 
    
    

    you can also try to do something like mentioned below.

    With Workbooks("Workbook1.xlsx") 
        .Sheets(Array("Sheet1", "Sheet2")).Copy _ 
        Before:=Workbooks("Workbook2.xlsx").Sheets(1) 
    End with
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by py1 Wednesday, October 5, 2016 8:14 AM
    Wednesday, October 5, 2016 4:50 AM
    Moderator

All replies

  • Hello Peter,

    Do not need to add new workbook first; the worksheet copy method does that for you and the worksheets will automatically inherit the name of the source worksheets with the code example below. The copied worksheets will have all formatting etc same as source.

    You have said source does not contain formulas but for future reference, if the worksheets contain formulas then it is possible that some or all will point back to the original source location and become links to the source and more code is required to remove those links.

    I have made the following assumptions

    • The code will be installed in the source workbook (workbook to be copied).
    • The source workbook will be a macro enabled.
    • The output workbook to be saved in same path as source workbook.
    • The output workbook will be a standard xlsx (non macro) workbook.

    Feel free to get back to me if any problems.

    Sub CopyMultShtsToNewWorkBook()
       
        Worksheets(Array("ShA", "ShB")).Copy
       
        'New workbook becomes the Active Workbook
        ActiveWorkbook.SaveAs Filename:= _
                        ThisWorkbook.Path & "\Output.xlsx", _
                        FileFormat:=xlOpenXMLWorkbook
           
        'Following line is optional to close the newly created workbook
        ActiveWorkbook.Close SaveChanges:=False 'Already saved. No need to save again
           
    End Sub


    Regards, OssieMac

    • Marked as answer by py1 Wednesday, October 5, 2016 8:13 AM
    Wednesday, October 5, 2016 4:45 AM
  • Hi PY1,

    if your source workbook contains only this 2 sheets then you just need to write a single line code like below.

    ActiveWorkbook.SaveCopyAs "C:\CopyOfMyWorkbook.xlsx"

    if your source workbook have multiple sheets but you just want to copy particular sheet then you can try to use the code mentioned below.

    Sub demo() 
        Windows("Workbook1.xls").Activate 
        Sheets("Sheet1").Select 
        Sheets("Sheet1").Copy Before:=Workbooks("Workbook2.xls").Sheets(1) 
         
        Windows("Workbook1.xls").Activate 
        Sheets("Sheet2").Select 
        Sheets("Sheet2").Copy Before:=Workbooks("Workbook2.xls").Sheets(2) 
         
       
    End Sub 
    
    

    you can also try to do something like mentioned below.

    With Workbooks("Workbook1.xlsx") 
        .Sheets(Array("Sheet1", "Sheet2")).Copy _ 
        Before:=Workbooks("Workbook2.xlsx").Sheets(1) 
    End with
    

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by py1 Wednesday, October 5, 2016 8:14 AM
    Wednesday, October 5, 2016 4:50 AM
    Moderator
  • Ossie

    Thanks for this - very helpful.

    much appreciated.

    regards,

    Peter

    Wednesday, October 5, 2016 8:13 AM
  • thanks Deepak - it is helpful.
    Wednesday, October 5, 2016 8:14 AM