copy worksheet
 

How can I copy a sheet entitled "Planning" as the first sheet in a workbook
name "tmplt_planning.xlsm" which is in the same folder as the current
workbook?

Thank you

QB

copy worksheet
 

Hi there,

You could use something like this ...

Option Explicit

Sub Copy_Planning_Worksheet()
Dim wb As Workbook, wbDest As Workbook, ws As Worksheet
Dim bWBOpen As Boolean, sName As String
Set wb = ThisWorkbook
If WSEXISTS("Planning", wb) = False Then
MsgBox "Worksheet (Planning) was not found in this workbook!",
vbExclamation, "ERROR!"
Exit Sub
End If
Set ws = wb.Worksheets("Planning")
sName = "tmplt_planning.xlsm"
If ISWBOPEN(sName) = True Then
Set wbDest = Workbooks(sName)
bWBOpen = False
Else
Set wbDest = Workbooks.Open(wb.Path & Application.PathSeparator
& sName)
bWBOpen = True
End If
If WSEXISTS("Planning", wbDest) = True Then
MsgBox "Worksheet already exists in target workbook (" & sName
& ")!", vbExclamation, "ERROR!"
Else
ws.Copy befo=wbDest.Worksheets(1)
End If
If bWBOpen = True Then
wbDest.Close SaveChanges:=True
End If
End Sub

Public Function ISWBOPEN(wbName As String) As Boolean
'Originally found written by Jake Marx
On Error Resume Next
ISWBOPEN = Len(Workbooks(wbName).Name)
End Function

Public Function WSEXISTS(wsName As String, Optional wkb As Workbook) As
Boolean
If wkb Is Nothing Then
If ActiveWorkbook Is Nothing Then Exit Function
Set wkb = ActiveWorkbook
End If
On Error Resume Next
WSEXISTS = CBool(Len(wkb.Worksheets(wsName).Name))
End Function


HTH


--
Zack Barresse
------------------------------------------------------------------------
Zack Barresse's Profile: http://www.thecodecage.com/forumz/member.php?userid=119
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158629

Microsoft Office Help