copying a worksheet to a new workbook

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)

 

Answer 1

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

Answer 2

this didn't help it just errors out and stops running the code :-(

 

Answer 3

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