Copy after

[Venkat Anvekar]

Dear Sirs.

I am trying to copy a sheet from one workbook to another using the following code.
A few days back the same code worked but now it is giving an error" subscript out of range" on the copy after statement.

Sheets("MS").Select
PathName1 = Range("v2").Value
pathname2 = Range("P8").Value
pathname3 = Range("u8").Value
filename1 = Range("v6").Value
ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=PathName1 & pathname2 & "\" & pathname3 & "\" & filename1
ActiveSheet.Name = "MS"
Worksheets("MS").Copy After:=Workbooks(filename1).Worksheets(1)

Kinldy give your suggestions pls.
Thanks & regards

Venkat

 

[Andrew Poulsom]

Where have you put that code?

 

[Tuelor]

Try with Workbooks(ControlFile). in front.

 

[Venkat Anvekar]

Many thanks Mr. Tuelor for the response.

I have to put this code on a "click" of a button which is on the sheet "MS"

On the click of the button i wish to copy the entire sheet "MS" into another workbook which is exiting at a different location.

Pls suggest.
Venkat

 

[Venkat Anvekar]

Try with Workbooks(ControlFile). in front.

Sir,

request you to suggest the appropriate VB code statment for the "copy after"

 

[Venkat Anvekar]

Where have you put that code?

Sir.
I have to put this code on a "click" of a button which is on the sheet "MS"

On the click of the button i wish to copy the entire sheet "MS" into another workbook which is existing at a different location. The path of the existing file is referred in the sheet "MS"

Pls suggest.
Venkat

 

[Andrew Poulsom]

Try:

ActiveSheet.Copy After:=Workbooks(filename1).Worksheets(1)