Copy a worksheet to a new workbook

[jean]

Hi

I have an excel 2007 file (workbook) named "MFL"

In this file (workbook) I have a worksheet named "Export"

Cell A3 contains an employee name let say "John Smith"

I want to copy the worksheet "Export" to a new workbook under the file
name "John Smith.xlsm"; all that using VBA

thanks for helping

 

[Don Guillett]

Hi



I have an excel 2007 file (workbook) named "MFL"



In this file (workbook) I have a worksheet named "Export"



Cell A3 contains an employee name let say "John Smith"



I want to copy the worksheet "Export" to a new workbook under the file

name "John Smith.xlsm"; all that using VBA



thanks for helping

Have you tried recording the macro /??

 

[jean]

Hi I have an excel 2007 file (workbook) named "MFL" In this file (workbook) I have a worksheet named "Export" Cell A3 contains an employee name let say "John Smith" I want to copy the worksheet "Export" to a new workbook under the file name "John Smith.xlsm"; all that using VBA thanks for helping

Recording a macro does not work as I need more sophisticated VB

 

[Don Guillett]

Hi



I have an excel 2007 file (workbook) named "MFL"



In this file (workbook) I have a worksheet named "Export"



Cell A3 contains an employee name let say "John Smith"



I want to copy the worksheet "Export" to a new workbook under the file

name "John Smith.xlsm"; all that using VBA



thanks for helping

Sure it does. start record>On the sheet tab>right click>copy >to new workbook>save as >

 

[Don Guillett]

Hi



I have an excel 2007 file (workbook) named "MFL"



In this file (workbook) I have a worksheet named "Export"



Cell A3 contains an employee name let say "John Smith"



I want to copy the worksheet "Export" to a new workbook under the file

name "John Smith.xlsm"; all that using VBA



thanks for helping

When all else fails, use this

Sub cleanedup()
ms = "don"
Sheets(ms).Copy
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & ms & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

 

[Don Guillett]

Hi



I have an excel 2007 file (workbook) named "MFL"



In this file (workbook) I have a worksheet named "Export"



Cell A3 contains an employee name let say "John Smith"



I want to copy the worksheet "Export" to a new workbook under the file

name "John Smith.xlsm"; all that using VBA



thanks for helping

Sub cleanedup()
ms = "don"
Sheets(ms).Copy
ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & ms & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

 

[ralph]

this file (workbook) I have a worksheet named "Export"
Cell A3 contains an employee name let say "John Smith" I want to
copy the worksheet "Export" to a new workbook under the
file name "John Smith.xlsm"; all that using VBA thanks for helping

Recording a macro does not work as I need more sophisticated VB

Part of what Don Guillett is suggesting is that creating a Macro is a
valid and useful first step in creating a VBA solution.

The generated code is often less than optimal. Well, make that
down-right ugly on occasion. <g> And Macros tend to be be more, shall
we say, "situationally aware". That is, they can make assumptions
about objects and their states that are risky or even invalid in a
'free-standing' VBA module.

Create your Macro.
Cleanup the code.
Fully qualify the references, and Voilà!
A more "sophisticated" VBA solution.

-ralph