Copy Worksheet

[Sri via OfficeKB.com]

Hello,

I run a macro from currWB which produces the sheet "Overview".

Before I finish my macro, I need to copy this sheet into another workbook
destWB.

I use the following command but gettingthe error "subcript out of range".

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

Any help in this regard please ...???

Sri

 

[joel]

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

 

[Sri via OfficeKB.com]

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

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

 

[joel]

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

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

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

 

[Chip Pearson]

You currWB variable is, I assume, a Workbook type variable. Therefore,
you can use it directly instead of Workbooks or you can use its name
with Workbooks. E.g.,

Workbooks(currWB.Name)......

' or

currWB.Sheets.(.....

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Sri via OfficeKB.com]

Hello Joel,

Thanks for your reply but unfortunately, your code is not working. Please
find my problem as follows.

Set currWb = ActiveWorkbook --> My present workbook "File A" is assigned to
currWb

currWb.Sheets("Overview").Copy --> A new workbook is created and Overview
sheet is copied into new workbook

Set destWB = ActiveWorkbook --> My present workbook "File A" is assigned to
destWb

currWb.Close savechanges:=False --> My present workbook "File A" is closed
where I have this macro. So, no code after this is executed. At the end, I am
left with a new workbook with name "Book 1" and "Overview" sheet in it,
unsaved.

Any help please... One more this thins that my source workbook contains more
than one sheet out of which I have to copy only selected worksheet.

Thanks
Sri

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

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

[quoted text clipped - 35 lines]