Copy worksheet into a new workbook

[brendalpzm]

Let's say I have 2 workbooks and i have to create 1

wb1 = workbook with the macro
wb2 = worbook with the info

so I have an activeX button in wb1, when I click it it should copy the KeySheet from wb2, create a new workbook (wb3) and paste the sheet onto this new workbook

Idk if i explained myself :s

 

[jolivanes]

Are both workbooks open?
Save as xlsx or xlsm?

 

[brendalpzm]

Are both workbooks open?
Save as xlsx or xlsm?

w1 open
w2 closed (i want it to be closed)
xlsx

 

[jolivanes]

Workbooks("wb1").Activate
ActiveWorkbook.Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Saved_Sheet.xlsx", 51

Change "wb1" to the workbook name incl extension that has the sheet to be saved as workbook and is not currently the active workbook. Needs the double quotation marks.
Change "Sheet1" to the actual name of the sheet to be saved as a workbook. Needs the double quotation marks.
Change "Saved_Sheet", without the double quotation marks, to the name you want the new workbook to have.
If you want to save it as a macro enabled workbook, change the .xlsx to .xlsm and change 51 to 52.
You might want to use the Application.ScreenUpdating in the code.
New workbook will be saved in the folder where the workbook with the macro in it resides.
This is just the bare bones code. If you need any additional help/changes, you know where we are waiting for it.

You have to elaborate on wb2. I assume you mean the workbook where the sheet to be saved is from.

 

[jolivanes]

Sub Save_Sheet_As_Workbook()
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, newWb As String
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("whatever name it is.xlsm")    '<----- Change required
Set ws1 = wb2.Sheets("whatever name it is")    '<----- Change required
newWb = ThisWorkbook.Path & "\" & "whatever name it is.xlsx"    '<----- Change required
Application.ScreenUpdating = False
    ws1.Copy
        ActiveWorkbook.SaveAs newWb, 51
        wb2.Close False    '<----- Change to True if wb2 needs saving
    Workbooks("whatever name it is.xlsx").Close False    '<----- Change required
Application.ScreenUpdating = True
End Sub[/code

 

[brendalpzm]

Sub Save_Sheet_As_Workbook()
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, newWb As String
Set wb1 = ThisWorkbook
Set wb2 = Workbooks("whatever name it is.xlsm")    '<----- Change required
Set ws1 = wb2.Sheets("whatever name it is")    '<----- Change required
newWb = ThisWorkbook.Path & "\" & "whatever name it is.xlsx"    '<----- Change required
Application.ScreenUpdating = False
    ws1.Copy
        ActiveWorkbook.SaveAs newWb, 51
        wb2.Close False    '<----- Change to True if wb2 needs saving
    Workbooks("whatever name it is.xlsx").Close False    '<----- Change required
Application.ScreenUpdating = True
End Sub

It shows an error "Script out of range" in the Set wb2

 

[jolivanes]

Name is spelled wrong or the workbook is not open.

 

[jolivanes]

OK, I see that in Post #2 you say that wb2 is closed. I was under the impression that you wanted it closed after. Sorry about that.
Will get back later as I am pretty busy with other things at the moment.
The workbook will need to be opened, sheet saved as workbook and closed again.

 

[jolivanes]

Change this

Set wb2 = Workbooks("whatever name it is.xlsm")    '<----- Change required

to this

Set wb2 = Workbooks.Open("C:\Folder Name here\Workbook Name incl extension here")    '<----- Change as required

If it is always the same workbook, above should work fine.
If you want to choose a workbook, let us know.

 

[jolivanes]

If your 2nd workbook is not open, as I now understand it to be, select your workbook where you want the sheet saved from in the pop-up.

Sub Save_Sheet_As_Workbook_B()
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, newWb As String
ChDir "C:\Brenda"    '<---- Change to folder where workbook to be opened resides or delete line
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open(Application.GetOpenFilename(FileFilter:="Excel files (*.xl*), *.xl*", Title:="Select workbook to save sheet from.", MultiSelect:=False))
wb1.Activate
Set ws1 = wb2.Sheets("Sheet1")    '<----- Change required
newWb = ThisWorkbook.Path & "\" & "Name for new workbook here.xlsx"    '<----- Change required
    ws1.Copy
        ActiveWorkbook.SaveAs newWb, 51
        wb2.Close False    '<----- Change to True if wb2 needs saving
    Workbooks("Name for new workbook here.xlsx").Close False    '<----- Change required
Application.ScreenUpdating = True
End Sub