copy workbook

[Johan]

Hi,

I want to do the following with a macro:

Msgbox: "copy?" if Yes
Then
Active wbk - sheet1 - range("name1") and active wbk - sheet2 -
range("name2") copy to new wbk without formulas and macro.
Then
Save as: range("name").xls

Please, can anyone help me!

Thanks in advance,

Johan

 

[Nick Hodge]

Johan

Not quite sure what you want the new workbook saved as but this should help

Sub CopySheets()
Dim wbNew As Workbook
Dim iAnswer As Integer
iAnswer = MsgBox("Copy Worksheets?", vbYesNo + vbQuestion, "Copy
Workbooks?")
If iAnswer = vbNo Then Exit Sub
Range("Name1").Value = Range("Name1").Value
Range("Name2").Value = Range("Name2").Value
Worksheets("Sheet1").Copy
Set wbNew = ActiveWorkbook
ThisWorkbook.Worksheets("Sheet2").Copy After:=wbNew.Worksheets(1)
wbNew.SaveAs Filename:=ThisWorkbook.Path & "\RangeName.xls"
ThisWorkbook.Close SaveChanges:=False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Johan]

Hi Nick,

This works fine, but it copies the formulas and macros. Is there a way
to copy only data and layout?

Thanks in avance.

Johan

 

[Nick Hodge]

Johan

Do the sheets have code behind them?

If not the newly created workbook 'RangeName.xls' will have no formulae or
code in it.

The two lines of Range("Name1") and 2 'kill' the formulas by setting them to
the value of themselves and moving the sheets to a workbook without code
ensures there is none, so the only remaining possibility is code behind the
sheets?

Maybe I am misunderstanding?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)