copy workbook
 

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

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
HIS


"Johan" wrote in message
om...
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

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" wrote in message ...
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
HIS


"Johan" wrote in message
om...
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

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
HIS


"Johan" wrote in message
om...
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" wrote in message
...
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
HIS


"Johan" wrote in message
om...
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