Neltu
New Member
- Joined
- Jan 28, 2009
- Messages
- 25
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:
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.
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:
Code:
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.
Last edited: