Copy workbook without Macros

[Robert]

I'm having problems copying an excel workbook the way I want to.

I currently have a MasterCopy.xls with data on Sheet1 and all my
macros imported. I need to create a new workbook (I called it Junk
Test.xls) and copy the first sheet of MasterCopy.xls onto
JunkTest.xls. However, I don't want the new Junk test workbook to
have any of the VBA macro stuff in it. I can't figure out how to do
that. Here's a basic look at what I have:

Private Sub Workbook_Open()
Dim sDest As String
sDest = "C:\Documents and Settings\rb012653\Desktop"
Data_Mover.SaveCopy sDest
End Sub

~~~In my Data_Mover Module~~~~~
Public Sub SaveCopy(sDest As String)
Dim NewWB As Workbook
Set NewWB = Workbooks.Add(sDest & "\Junk Test.xls")
'I've tried lots of copy commands here without any luck.
End Sub

Whenever I try and open the new JunkTest.xls file I also get the
following error:
Runtime error '1004': Cannot access 'JunkTest.xls.' This is my first
time posting, so I appologize if it is in another thread. I've
searched all over and couldn't find anything to help me figure this
one out.

 

[Chip Pearson]

Your code is attempting to use sDest & "\Junk Test.xls" as template for a
newly created workbook. When call Add, if you pass parameter, it must the
name of an existing file that is used as a "template" (in the generic sense,
not necessarily in the "xlt" sense) for the newly create workbook. The
parameter is NOT the name of the file as which the newly create workbook
will be saved. Since sDest & "\Junk Test.xls" doesn't exist, Add blows up
with a file not found problem.

Instead, do something like

Set NewWB = Workbook.Add() ' no parameter
NewWB.SaveAs sDest & "\Junk Test.xls" ' save to disk

See www.cpearson.com/excel/vbe.htm for details and example code about how to
remove all existing VBA code from a workbook.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)