export sheet
 

Hi all

I need a simple macro to export one worksheet to a existing workbook.
the name of the worksheet must be entered in a msgbox

Can anybody help me ?

export sheet
 

Hi,
Try something like the following (modify code to fit your scenario in
section 'Change Here'):
Sub Macro2()

Dim wshO As Worksheet, nameO As String 'Origin sheet
Dim wshD As Worksheet, WbkD As Workbook, nameD As String 'Destination
variables
Dim count As Long


' Set variables
'------ CHANGE HERE ------------
Set wshO = ActiveSheet
Set WbkD = Workbooks(2)
'-------------------------------
nameO = wshO.Name
count = WbkD.Sheets.count

'Get name from user
nameD = Application.InputBox("Enter new name", "New Sheet Name")
If nameD = "False" Then Exit Sub 'Cancelled by user

'Copy sheet
wshO.Copy After:=Workbooks(2).Sheets(count)
Set wshD = WbkD.Sheets(count + 1) 'new sheet is last one

'Rename
On Error Resume Next
wshD.Name = nameD
If Err < 0 Then
MsgBox "The provided name '" & nameD & "' is not valie (invalid or
already exist)" & _
vbNewLine & "Please, set it manually."
End If
End Sub

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"christophe" wrote:

Hi all

I need a simple macro to export one worksheet to a existing workbook.
the name of the worksheet must be entered in a msgbox

Can anybody help me ?

export sheet
 

thanks , it works just perfect

"sebastienm" wrote:

Hi,
Try something like the following (modify code to fit your scenario in
section 'Change Here'):
Sub Macro2()

Dim wshO As Worksheet, nameO As String 'Origin sheet
Dim wshD As Worksheet, WbkD As Workbook, nameD As String 'Destination
variables
Dim count As Long


' Set variables
'------ CHANGE HERE ------------
Set wshO = ActiveSheet
Set WbkD = Workbooks(2)
'-------------------------------
nameO = wshO.Name
count = WbkD.Sheets.count

'Get name from user
nameD = Application.InputBox("Enter new name", "New Sheet Name")
If nameD = "False" Then Exit Sub 'Cancelled by user

'Copy sheet
wshO.Copy After:=Workbooks(2).Sheets(count)
Set wshD = WbkD.Sheets(count + 1) 'new sheet is last one

'Rename
On Error Resume Next
wshD.Name = nameD
If Err < 0 Then
MsgBox "The provided name '" & nameD & "' is not valie (invalid or
already exist)" & _
vbNewLine & "Please, set it manually."
End If
End Sub

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"christophe" wrote:

Hi all

I need a simple macro to export one worksheet to a existing workbook.
the name of the worksheet must be entered in a msgbox

Can anybody help me ?