destWBName = vPath & "\LCM_Report_" & inpDate & ".xls"
'currWB = vPath & "\" & vName
Application.DisplayAlerts = False

Set currWb = ActiveWorkbook

If Dir$(destWB, vbNormal) = "" Then

Application.DisplayAlerts = True

'copy without After will create new workbook automatically
currWb.Sheets("Overview").Copy
Set destWB = ActiveWorkbook
currWb.Close savechanges:=False

With destWB
.Sheets("Overview").Copy _
After:=.Sheets(.Sheets.Count)
End With
destWB.SaveAs Filename:=destWBName
End If

"Sri via OfficeKB.com" wrote:

Thanks joel for your reply but I still have same problem. See my exact code
as follows.

"Subscript out of range" at "With Sheets(destWB)"

destWB = vPath & "\LCM_Report_" & inpDate & ".xls"
currWB = vPath & "\" & vName

If Dir$(destWB, vbNormal) = "" Then

Workbooks.Add
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=destWB
ActiveWindow.Close SaveChanges:=False
Application.DisplayAlerts = True

With Sheets(destWB)
Workbooks(currWB).Sheets("Overview").Copy _
After:=.Sheets(.Sheets.Count)
End With

End If



joel wrote:
from
Workbooks(currWB).Sheets("Overview").Copy
After:=Workbooks(destWB).Sheets.Count

to

with sheets(destWB)
Workbooks(currWB).Sheets("Overview").Copy _
After:=.Sheets(.sheets.Count)
end with

Hello,

[quoted text clipped - 11 lines]

Sri

--
Message posted via http://www.officekb.com