I have one excel file named Test1.xlsx with sheet named "List". I want to copy the sheet List into another file called Test2.xlsx. Both files are existing. I also want to delete the Sheet "List" in Test2.xlsx before copying.. Finally I want to save and close both the files.

I am new to Excel VBA.
I would appreciate if anybody can help me in showing how this can be done in Excel VBA 2013.

On 04/02/2014 21:46, wrote:
I have one excel file named Test1.xlsx with sheet named "List".
I want to copy the sheet List into another file called Test2.xlsx.
Both files are existing. I also want to delete the Sheet "List"
in Test2.xlsx before copying.. Finally I want to save and close
both the files.

I am new to Excel VBA.
I would appreciate if anybody can help me in showing how this can be
done in Excel VBA 2013.

Hi PPDS,

Alt-Fll to open the VBA editor
Alt-IM to insert a new code module
At the flashing cursor, paste the following code:

'=============
Option Explicit

'-------------
Public Sub CopySheet()
Dim srcWB As Workbook
Dim destWB As Workbook
Dim srcSH As Worksheet
Dim errStr As String
Dim CalcMode As Long
Const aStr As String = "Workbook"
Const bStr As String = "Worksheet"
Const srcWbName As String = "Test1.xlsx" '<<==== CHANGE
Const destWbName As String = "Test2.xlsx" '<<==== CHANGE
Const shName As String = "List"

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

On Error Resume Next
Set srcWB = Workbooks(srcWbName)
If Not srcWB Is Nothing Then
Set destWB = Workbooks(destWbName)
If Not destWB Is Nothing Then
Application.DisplayAlerts = False
destWB.Sheets(shName).Delete
Application.DisplayAlerts = True
Set srcSH = srcWB.Sheets(shName)
If Not srcSH Is Nothing Then
With destWB
srcSH.Copy After:=.Sheets(.Sheets.Count)
End With
Else
errStr = bStr & " " & shName
GoTo XIT
End If
Else
errStr = aStr & " " & destWbName
GoTo XIT
End If
Else
errStr = srcWbName
GoTo XIT
End If

XIT:
If Len(errStr) Then
Call MsgBox(Prompt:=errStr & " not found", _
Buttons:=vbCritical, _
Title:="ERROR")
Else
srcWB.Close SaveChanges:=True
destWB.Close SaveChanges:=True
End If

With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = CalcMode
End With
End Sub
'<<=============

Alt-Q to close the VBA editor and return to Excel
Alt-F8 to open the macro window
Select CopySheet | Run | ok


===
Regards,
Norman