Copying the entire workbook to a new workbook

[yoitsmejy]

I want to copy the entire workbook to a new workbook without copying the formulas in the original workbook. I do not need the workbook to be saved. I have a delimiter code that i would like to be applied to the new workbook. Please help me figure out the best way to do this. Thank you.The delimiter code are as follow:


Sub SaveText()
Const DELIMITER As String = ","
Dim ws As Worksheet
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

For Each ws In ActiveWorkbook.Worksheets

Set myRecord = Application.Intersect(ws.UsedRange, _
ws.Columns(ActiveCell.Column))

nFileNum = FreeFile
Open "C:\Documents" & ws.Name & ".txt" For Output As #nFileNum
For Each myRecord In ws.Range("A1:A" & _
ws.Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
.Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
Next ws
End Sub

[BrianMH]

So are you wanting to export them as text or actually as a new workbook? Can we have an example of the data? Also when posting code please use the vba tags.

Thanks

[yoitsmejy]

The end goal is to export it to a text file. But i would like it to copy the entire workbook into a new workbook before it is exported. If it is easier, the workbook can be copied into a specific workbook before it is being exported. Please let me know. Thanks

[BrianMH]

This code will copy the data from the worksheets to a new workbook for you and paste only values and formats so it gets rid of formulas.

Sub workbookcopy()
Dim wbCopy As Workbook
Dim wbPaste As Workbook
Set wbCopy = ThisWorkbook
Set wbPaste = Workbooks.Add
Dim stCopy As Worksheet
Dim stPaste As Worksheet
For Each stCopy In wbCopy.Sheets
Set stPaste = wbPaste.Sheets.Add
stPaste.Name = stCopy.Name
stCopy.UsedRange.Copy


stPaste.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Next
End Sub

[yoitsmejy]

the following error message come up:
Run-time error '1004':
Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic.
It is highlighting the following:
stPaste.Name = stCopy.Name

[RonMcK]

How about opening your source workbook and then saving it with the new name?

Here's a quick macro to show what I'm suggesting. You can adjust the code to let you search for your source file and apply your desired name for the backup copy.


Sub Macro9()
'
' Macro9 Macro
'
'
Workbooks.Open Filename:= _
"C:\Users\Ron\Desktop\G1-5 NL 2012 Diagnostic Assessment\g3_INFO-TXT.xls"

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\Ron\Desktop\G1-5 NL 2012 Diagnostic Assessment\g3_INFO-TXT-new.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Thanks,