"Copy" a worksheet from a workbook to another wbk

My problem is this:

I have this workbook in the app.path that contains this one worksheet. I want to get this worksheet and put it, or copy it, into another workbook with the Excel object all through VB. How would I go about doing this? Thanks.

if you don't have a specific file you wish to transfer the sheet to (ie. just want to get it out of the source document)
you can use the 'Sheets(strSheettoMove$).Move' method - you will need a reference to the 'MS Excel x.x Object Library'

otherwise you will also require a reference to the 'MS Visual Basic for Applications Extensibility x.x' library and do something like this:

Sub ExcelComponent_Transfer()
Dim objExcelSourceFile As Excel.Workbook

Set objExcelSourceFile = CreateObject(strExcelSOURCEFileandLocation$)
objExcelSourceFile.VBProject.VBComponents(strSheetToMove$).Export strFileNameandLocationOfOutput$
objExcelSourceFile.Close

Set objExcelSourceFile = CreateObject(strExcelDESTINATIONFileandLocation$)
objExcelSourceFile.VBProject.VBComponents.Import strFileNameandLocationOfOutput$
objExcelSourceFile.Close

Set objExcelSourceFile = Nothing

End Sub

good luck..

"He who dares my son!.. He who dares.." - Del-Boy Trotter

I don't think I quite understand what you are saying. Here is my predicament. I have this workbook that contains this sheet. The workbook (coresheet.xls) is in the app.path. The program asks the user to select a file name (strFileName) from a dialog which the filename is also a .xls file. Then, I want code that will "copy" the worksheet from 'corsesheet.xls' and "paste" it into the user-selected workbook (.xls) file. How would I do this?

ok,

Sub ExcelComponent_Transfer()
Dim objExcelSourceFile As Excel.Workbook

'open the 'corsesheet.xls' file in memory
Set objExcelSourceFile = CreateObject(strExcelSOURCEFileandLocation$)
'export the entire worksheet from the file and save it onto disk
objExcelSourceFile.VBProject.VBComponents(strSheetToMove$).Export strFileNameandLocationOfOutput$
objExcelSourceFile.Close

'open the user-selected workbook in memory
Set objExcelSourceFile = CreateObject(strExcelDESTINATIONFileandLocation$)
'import the file that was saved to disk - it will appear as a new worksheet in the user-selected Excel workbook
objExcelSourceFile.VBProject.VBComponents.Import strFileNameandLocationOfOutput$
objExcelSourceFile.Close

Set objExcelSourceFile = Nothing

End Sub


..if you don't want to add sheets to the user-selected file, but instead just want to copy over any exising data with the values from 'corsesheet.xls', then use:

Sub ExcelSheet_Copy()
Dim objExcelSourceFile As Excel.Workbook
Dim objExcelDestinationFile As Excel.Workbook

Set objExcelSourceFile = CreateObject(strExcelSOURCEFileandLocation$)
Set objExcelDestinationFile = CreateObject(strExcelDESTINATIONFileandLocation$)

objExcelDestinationFile.Sheets(strSheetToReplace$).UsedRange.Delete
objExcelSourceFile.Sheets(strSheetToCopy$).UsedRange.Copy
objExcelDestinationFile.Sheets(strSheetToReplace$).Paste "A1"

objExcelSourceFile.Close
Set objExcelSourceFile = Nothing
objExcelDestinationFile.Close
Set objExcelDestinationFile = Nothing

End Sub

..and you won't require a reference to the VBA Extensibility library.


"He who dares my son!.. He who dares.." - Del-Boy Trotter

Thanks for the code, but there are still a couple of more problems. For one thing, I got an error that the "Paste method failed". Why is this? I looked in the clipboard and, low and behold, the contents were there. The problem was with the paste method.

One more question about your post. You had two solutions for me and I chose to do the second one (the one that doesn't require me to reference VBA). What I want to know is does this code also copy borders of cells? Because I need to do that also. Thanks a lot. I really appreciate it. Thanks again!

oops, the paste method failed 'cos it should have read:
'... .Paste Range("A1")'

Yes, this method will copy everything including formulas - if you have formulas in the source workbook, you will end up with links in your destination worksheet (yuk!) in which case you will need to use the 'PasteSpecial' method:
'objExcelDestinationFile.Sheets(strSheetToReplace$).Range("A1").PasteSpecial xlPasteValues
objExcelDestinationFile.Sheets(strSheetToReplace$).Range("A1").PasteSpecial xlPasteFormats'

..or, more efficiently:
'With objExcelDestinationFile.Sheets(strSheetToReplace$).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With'


"He who dares my son!.. He who dares.." - Del-Boy Trotter

There is still another error coming up. "Range method failed" and something global. Why is this? Thanks for your help. I really need to figure this out.

Okay, Timbo, don't worry about that post that says the the Range method failed. I fixed it so that now the paste method and the copy method work. There is still one more problem, though. Here it is:

You see, I told you about the sheet that I want to copy from the workbook at app.path and then paste it into a sheet in the user-selected workbook. Well, as I said before, it worked and the data and borders were all copied. But, now another problem arises. I also want to copy cell width and all those other formats like 'FitToPages'. How would I copy stuff like that?

Thanks so much. I really appreciate it. I'm sorry to keep bothering you like this, but this is really crucial that I get this program done. Thanks again!

try something like this (non tested code):
<pre>
Dim xlApp As Excel.Application
Dim xw As Excel.Workbooks

Set xlApp = GetObject(, "Excel.Application")

If xlApp Is Nothing Then Set xlApp = CreateObject("Excel.Application")

Set xw = xlApp.Workbooks

xw("Book1").Sheets("Sheet1").Select
xw("Book1").Sheets("Sheet1").Move After:=xw("Book2").Sheets(1)
</pre>

Some other tip:

Start Excel, start the macro-recorder, do all needed operations manually, stop the macro-recorder and learn of the generated code...

hey Garfield, sorry, I wasn't ignoring you - my weekend starts and ends 15 hours b4 the U.S. (if that's where you are..).

Anyhow, the 'Copy'/'Paste' method should take care of things like cell height/width etc...

As far as 'PageSetup' properties, you will need to access each one (easier to loop them I guess) and assign the values to your destination worksheet. Look up 'PageSetup' in your Help file.

(BTW: No need apologise for asking questions, it's really not a problem.)