Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy COLUMN from 1 worksheet to another (in a different workbook) | Excel Discussion (Misc queries) | |||
copy a workbook from other workbook with lot of sheets wit... | Excel Discussion (Misc queries) | |||
How can I save an editable copy of a protected excel workbook? | Excel Discussion (Misc queries) | |||
copy range of cells from one workbook to another | Excel Discussion (Misc queries) | |||
how can I duplicate or copy a workbook then divide selected cells. | Excel Discussion (Misc queries) |