Receiving error when trying to copy a worksheet from one workbook to another in VB

[themadness]

I'm receiving the error "copy method of worksheet class failed" when trying to copy a worksheet from one workbook to another in my VB macro. The following is my code... Any help would be most appreciated I have included the code a recorded macro doing this operation gave me at the top, and have also commented above the line I receive the error on. I should mention in closing that there is only 1 sheet "Summary" in the workbook I want to copy from.

Please Login or Register  to view this content.

[broro183]

hi,

The root of the problem is your use of a new instance of Excel ("xltmp") because you can't copy a sheet from one instance to another. I don't think you need a separate instance anyway b/c the file is closed so quickly at the end of the macro.
To prevent the screen changing with the new file you can include the below lines at the start & end of your code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Also, this is untested but I think that the following line can be changed from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

hth
Rob

[davesexcel]

maybe this part of the code should be changed to this, at least it stops the error

Please Login or Register  to view this content.

I am not too sure what you are doing as
your code does not copy to a new workbook but copies a new sheet to the existing workbook from what I could tell anyway

This code copy's sheet 1 into a new workbook

Please Login or Register  to view this content.

from there you can add code to save as a new workbook such as this thread shows
http://www.excelforum.com/showthread.php?t=592310

[themadness]

Rob,

I tried your code and it didn't work. Basically what I'm doing is performing a bunch of calculations on data on a spreadsheet I already have open. (I didn't bother including this code because it works and is irrelevant to the part that is failing). Once these calculations are done I want to copy a summary sheet from another excel file into the one I have performed the calculations on. (which is the code I provided) How would I go about doing this since I can't copy a sheet form one instance to another?

hi,

The root of the problem is your use of a new instance of Excel ("xltmp") because you can't copy a sheet from one instance to another. I don't think you need a separate instance anyway b/c the file is closed so quickly at the end of the macro.
To prevent the screen changing with the new file you can include the below lines at the start & end of your code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Also, this is untested but I think that the following line can be changed from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

hth
Rob

[themadness]

Dave,

Please see my response to Rob above which explains what I'm trying to accomplish. The code you provided does work; however it merely copies sheet 1 from my workbook to itself again. What I need is a sheet from another excel file copied into this workbook. That is what the code I wrote attempts to do, but unfortunately fails.

I don't want to use buttons... ect. because a bunch of other calculations are being ran before this part of the code. Once the user uses the GetFile() function to open the file which contains the sheet I want to copy I want the macro to continue to perform additional calculations after it copies that sheet to xlBook .

maybe this part of the code should be changed to this, at least it stops the error

Please Login or Register  to view this content.

I am not too sure what you are doing as
your code does not copy to a new workbook but copies a new sheet to the existing workbook from what I could tell anyway

This code copy's sheet 1 into a new workbook

Please Login or Register  to view this content.

from there you can add code to save as a new workbook such as this thread shows
http://www.excelforum.com/showthread.php?t=592310

[broro183]

Sorry, I didn't make all the required changes to the code - hopefully I have this time ;-)

On my computer I can have multiple workbooks open in a single Excel Instance & this is what I have tried to do for you in the below modified code
where I have removed the separate instance completely...

Code:
'Please note that I recorded a macro using the standard record button in Excell to perform this very
'operation, so I could then adapt it to my own code. The macro code it gave me was
'Sheets("Summary").Copy Before:=Workbooks("FSRmonthdata.csv").Sheets(1)

Sub Modifedtest()
Application.ScreenUpdating = False
Dim xlTmpBook As Workbook
Dim xlBook As Workbook

'Get the summary sheet pointed to by the user and apply to ThisWorkbook

Set xlBook = ThisWorkbook
Set xlTmpBook = Workbooks.Open(GetFile("Select the FSR summary dashboard to use"))
'This next line is the line it gives me the error in regards, and is also the line I modified
'to do what the recorded macro did.
'NOTE: you need to check that the index number of the sheets in
'the xltmpbook & xlbook is actually = 1 (b/c I it is possible that it may not be
' even if there is only one sheet in the files)
xlTmpBook.Worksheets(1).Copy Before:=xlBook.Sheets(1)
xlTmpBook.Close
Application.ScreenUpdating = true
End Sub

Private Function GetFile(Optional HeaderMsg As String) As String

With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = Application.DefaultFilePath
.Title = HeaderMsg
If .Show = -1 Then
GetFile = .SelectedItems(1)
Else
GetFile = ""
End If
End With

End Function

hth
Rob

[Paul]

I might be mistaken, but I didn't think a .CSV file could contain more than one worksheet. CSV files aren't even Excel files, they're just text files that you can open in Excel.

Could that be the issue? Try copying it to a .XLS file...

--Update: It appears you can insert as many sheets as you want into a .CSV file opened in Excel (at least manually from my testing), but you cannot save the file in CSV format if you do. Each sheet would need to be saved as its own file.

[themadness]

Your code worked perfectly. I see what you ment about the instance of xlTmp As Excel.Application not being necessary and causing the problem. Thanks for your help!

Sorry, I didn't make all the required changes to the code - hopefully I have this time ;-)

On my computer I can have multiple workbooks open in a single Excel Instance & this is what I have tried to do for you in the below modified code
where I have removed the separate instance completely...





hth
Rob

[themadness]

Yes, this is true; however, when I get to the point of saving I'm going to write the code to save as a standard .xls file.

I might be mistaken, but I didn't think a .CSV file could contain more than one worksheet. CSV files aren't even Excel files, they're just text files that you can open in Excel.

Could that be the issue? Try copying it to a .XLS file...

--Update: It appears you can insert as many sheets as you want into a .CSV file opened in Excel (at least manually from my testing), but you cannot save the file in CSV format if you do. Each sheet would need to be saved as its own file.

[broro183]

Thanks for the feedback, I'm pleased I could help :-)

Rob