Modify code to export a specific sheet when creating a workbook

[Neltu]

Hi,

I currently have code that will create a new workbook, and paste data onto the new workbook after a macro is run. What i want to do is make sure that Sheet 1 in the original document is always included when the workbook is created.
Code is as follows:

Sub NewDocCreate()
Application.ScreenUpdating = False
Dim xcelNew As Excel.Application
Dim xcelSheet As Excel.Workbook
CurWkbk = ActiveWorkbook.Name
Set xcelNew = CreateObject("Excel.Application")
Windows(CurWkbk).Activate
Sheets("Checklist").Activate
ActiveSheet.Cells.Activate
ActiveSheet.Cells.Copy
Set xcelSheet = xcelNew.Workbooks.Add
xcelSheet.SaveAs ("MCRC1_" & Environ("UserName") & ".xls")
xcelNew.DisplayAlerts = False
xcelSheet.Close
Set xcelSheet = Workbooks.Open(Filename:="MCRC1_" & Environ("UserName") & ".xls")
Workbooks("MCRC1_" & Environ("UserName") & ".xls").Activate
Sheets("Sheet2").Cells.PasteSpecial
Application.CutCopyMode = False
xcelSheet.Save
xcelSheet.Close
MsgBox "Test Script exported to " & ("C:\Documents and Settings" & Application.PathSeparator & Environ("UserName") & Application.PathSeparator & "My Documents" & Application.PathSeparator & "MCRC1_" & Environ("UserName") & ".xls"), vbInformation, "Export Success"
Set xcelNew = Nothing
Set xcelSheet = Nothing
End Sub

I have already modified it so that it pastes the result into Sheet 2, but i cant figure out how to make it include Sheet 1 titled "Instructions" from the Current workbook whenever it creates the new workbook for the data to be pasted in.

Any help would be greatly appreciated.

Thanks in advance.

 

[Von Pookie]

You can just copy the entire sheet(s) to a new workbook:

Sub NewDocCreate()
Dim wbNew As Workbook, strWbName As String

Application.ScreenUpdating = False

'create new workbook, set to variable
Set wbNew = Workbooks.Add

With wbNew
    'copy "Instructions" and "Checklist" sheets from current workbook to new workbook
    ThisWorkbook.Sheets(Array("Instructions", "Checklist")).Copy Before:=wbNew.Sheets(1)
    
    'delete Sheet1 and Sheet2 from new workbook
    Application.DisplayAlerts = False
    .Sheets(Array("Sheet1", "Sheet2")).Delete
    Application.DisplayAlerts = True
    
    'save new workbook
    .SaveAs ("MCRC1_" & Environ("UserName") & ".xls")
    
    'set the full path of new workbook-including filename-to variable
    strWbName = .FullName
    
    'close new workbook
    .Close (False)
End With

'display msgbox
MsgBox "Test Script exported to " & strWbName, vbInformation, "Export Success"

End Sub

 

[Neltu]

Thank You that was exactly what i wanted.