Copy a specific sheet to a new workbook

Question

How would could one write a macro that will copy sheet 2 from workbook 1 and create a new workbook and paste that sheet into the new workbook If i click on a button I have added at the bottom of sheet 1, workbook 1.

Edit This code copies the active sheet(which is not the sheet I want) and does the rest perfectly by prompting to save as, which is cool, However I need to copy sheet 2 and not the active sheet.

Sub GetQuote()

   Dim activeWB As String
   Dim thisSheet As String

   activeWB = ActiveWorkbook.Name
   thisSheet = Workbooks(activeWB).ActiveSheet.Name
   Workbooks.Add
   Workbooks(activeWB).Sheets(thisSheet).Copy _
   Before:=ActiveWorkbook.Sheets(1)
   Application.Dialogs(xlDialogSaveAs).Show
   ActiveWorkbook.Close

End Sub

What have you tried? give the macro recorder a chance, look at the code and get back if you're stuck! :-) — Peter Albert
I have tried code that copies the active sheet and not sheet two because I have hidden that sheet on purpose. I will edit the question and add the code. — Mike Barnes

Peter Albert Peter Albert · Accepted Answer · 2013-02-18T14:20:40

This should do the job:

Sub GetQuote()
    ThisWorkbook.Sheets("Sheet2").Copy
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

Copy a specific sheet to a new workbook

1 Answers