locked
copying a worksheet to a new workbook RRS feed

  • Question

  • I am moving two sheets from one workbook into their own workbooks.  After doing the save as for the last worksheet how do I bring the focus back to the original workbook?  Here is the code I am using for copying:

    Private Sub CommandButton7_Click()
       
        Sheets("Pricelist 28 Day").Visible = True
        Sheets("Pricelist").Select

    Const SHEET_NAME_1 = "PriceList 28 Day" 'Change
    Const SHEET_NAME_2 = "Material Order 28 Day" 'Change

        ThisWorkbook.Sheets(SHEET_NAME_1).Copy
           
        Application.Dialogs(xlDialogSaveAs).Show
       
        ThisWorkbook.Sheets(SHEET_NAME_2).Copy
       
        Application.Dialogs(xlDialogSaveAs).Show
       


    End Sub

     

    I need to be able to hide the original sheet to protect it.  Also I have a range of cells that I need to copy and then paste values to get rid of formulas that link to another worksheet, is this even possible to automate or do I just need to go in and manually do it each time?

    Thanks in advance for all your help!


    oh, the columns are Column B Rows 12:61 and Column G Rows 12:59

    **update**

    I figured out the code to bring the focus back on the original workbook.  Now is there a line of code I can insert after this line:

    "Application.Dialogs(xlDialogSaveAs).Show"

    that will cause this workbook to close?
    I tried Workbook.Close but that says it is missing an object (I suspect that it is looking for the name of the workbook to close)

     

    • Edited by gmartineau Friday, January 20, 2012 3:57 PM update/original question
    Wednesday, January 18, 2012 11:12 PM

Answers

  • I am moving two sheets from one workbook into their own workbooks.  After doing the save as for the last worksheet how do I bring the focus back to the original workbook?  Here is the code I am using for copying:

    Private Sub CommandButton7_Click()
       
        Sheets("Pricelist 28 Day").Visible = True
        Sheets("Pricelist").Select

    Const SHEET_NAME_1 = "PriceList 28 Day" 'Change
    Const SHEET_NAME_2 = "Material Order 28 Day" 'Change

        ThisWorkbook.Sheets(SHEET_NAME_1).Copy
           
        Application.Dialogs(xlDialogSaveAs).Show
       
        ThisWorkbook.Sheets(SHEET_NAME_2).Copy
       
        Application.Dialogs(xlDialogSaveAs).Show


    End Sub

     

    I need to be able to hide the original sheet to protect it.  Also I have a range of cells that I need to copy and then paste values to get rid of formulas that link to another worksheet, is this even possible to automate or do I just need to go in and manually do it each time?

    Thanks in advance for all your help!


    oh, the columns are Column B Rows 12:61 and Column G Rows 12:59

    **update**

    I figured out the code to bring the focus back on the original workbook.  Now is there a line of code I can insert after this line:

    "Application.Dialogs(xlDialogSaveAs).Show"

    that will cause this workbook to close?
    I tried Workbook.Close but that says it is missing an object (I suspect that it is looking for the name of the workbook to close)

     

    You can use this line of code to close the workbook on which procedure locates:

    'Save changes when closing the workbook
    ThisWorkbook.Close True
    
    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by gmartineau Friday, February 3, 2012 2:28 PM
    Friday, February 3, 2012 9:29 AM

All replies

  • You don't need to change the focus at all.  Usually I just set a variable to any worksheet as required.  something lole this

    Set SHEET_NAME_1_sht = Sheets(SHEET_NAME_1)

    with SHEET_NAME_1_sht

        .Copy
           
        Application.Dialogs(xlDialogSaveAs).Show

       .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

    end with

     


    jdweng
    Thursday, January 19, 2012 12:14 AM
  • this didn't help it just errors out and stops running the code :-(

     

    Thursday, January 19, 2012 8:58 PM
  • I am moving two sheets from one workbook into their own workbooks.  After doing the save as for the last worksheet how do I bring the focus back to the original workbook?  Here is the code I am using for copying:

    Private Sub CommandButton7_Click()
       
        Sheets("Pricelist 28 Day").Visible = True
        Sheets("Pricelist").Select

    Const SHEET_NAME_1 = "PriceList 28 Day" 'Change
    Const SHEET_NAME_2 = "Material Order 28 Day" 'Change

        ThisWorkbook.Sheets(SHEET_NAME_1).Copy
           
        Application.Dialogs(xlDialogSaveAs).Show
       
        ThisWorkbook.Sheets(SHEET_NAME_2).Copy
       
        Application.Dialogs(xlDialogSaveAs).Show


    End Sub

     

    I need to be able to hide the original sheet to protect it.  Also I have a range of cells that I need to copy and then paste values to get rid of formulas that link to another worksheet, is this even possible to automate or do I just need to go in and manually do it each time?

    Thanks in advance for all your help!


    oh, the columns are Column B Rows 12:61 and Column G Rows 12:59

    **update**

    I figured out the code to bring the focus back on the original workbook.  Now is there a line of code I can insert after this line:

    "Application.Dialogs(xlDialogSaveAs).Show"

    that will cause this workbook to close?
    I tried Workbook.Close but that says it is missing an object (I suspect that it is looking for the name of the workbook to close)

     

    You can use this line of code to close the workbook on which procedure locates:

    'Save changes when closing the workbook
    ThisWorkbook.Close True
    
    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    • Marked as answer by gmartineau Friday, February 3, 2012 2:28 PM
    Friday, February 3, 2012 9:29 AM