Solved: Copy Sheets to new workbook and save copy

[john3j]

Hello,

I was wondering if someone could help me come up with code to copy all of the sheets in a workbook to a new workbook and then save it as a specific file. I need to copy the sheets to a new workbook because I have datasources and code in the original that other users should not be able to see or given the chance to break. Lets say there is a workbook called Test1 where I will be copying the sheets from. I want copy all of the worksheets from Test1 into a new workbook. I would then like it to save the new workbook as Test2 in the specified folder of My Documents. I just dont want users to be able to screw this up. Any help is greatly appreciated.

Thanks,
John: pray2:

[lucas]

Sub SaveAsExample()
Dim FName As String
Dim FPath As String

FPath = "C:\Temp"
' FName = Sheets("Sheet1").Range("A1").Text
FName = ActiveWorkbook.Name
ThisWorkbook.SaveCopyAs Filename:=FPath & "\" & FName
End Sub

[ZVI]

Hi John,
This saves only sheets without VBA standard & class modules:


Sub Test1()
Application.ScreenUpdating = False
With ActiveWorkbook
Sheets.Copy
ActiveWorkbook.SaveCopyAs .Path & "\Test2.XLS"
ActiveWorkbook.Close False
.Activate
End With
Application.ScreenUpdating = True
End Sub
Vladimir

[GTO]

...because I have datasources and code in the original...

Greetings John,

Reference the existing code is the workbook to be replicated, is the code all in Standard and/or developer created Class Modules, or, is there any code under the worksheets?

By datasources, do you mean that there are formula(s) in the workbook that refer to other workbooks, and that you wish only the values or returned calculation(s) to be planted in the newly created wb?

Mark

[john3j]

ZVI's code works perfectly! Thank you so much. Now only if I could have a code that opens all of these saved copies and sends them to the printer!