[RESOLVED] Insert a spreadsheet from other workbook

[vilic]

How can I insert a spreadsheet from other workbook using VBA? Depending on parameters I would set a code to insert a certain spreadsheet from an external workbook. I just couldn't find the "insert" procedure.

[Hack]

Excel VBA question moved to Office Development

[DKenny]

Would the source and target workbooks be open already or would you want the procedure to open them?

[Webtest]

I did a quick Macro Record and extracted the following:

    Workbooks("Book1Name").Sheets("SheetName").Copy Before:=Workbooks("Book2Name").Sheets(1)
    Workbooks("Book1Name").Sheets("SheetName").Move Before:=Workbooks("Book2Name").Sheets(1)

This requires that you specify all names explicitly ... you can substiture string variables with the names if you need to. This makes the Moved/Copied sheet the first sheet in the destination workbook. To make the Moved/Copied sheet the LAST sheet, just make the following substitution:

Sheets(1)  >>  Sheets(Workbooks("Book2Name").Sheets.Count)

You can make things easier by assigning handles to your Workbooks and Worksheets. Let me know if you need to know how to do that.

[DKenny]

IF both books are closed use the following...

Sub CopySheet(sSourcePath As String, sTargetPath As String, sSheetName As String)
Dim wkbSource As Workbook
Dim wkbTarget As Workbook
Dim lSheetCount As Long
 
    Workbooks.Open Filename:=sSourcePath
    Set wkbSource = ActiveWorkbook
    
    Workbooks.Open Filename:=sTargetPath
    Set wkbTarget = ActiveWorkbook
    
    lSheetCount = wkbTarget.Sheets.Count
    
    wkbSource.Worksheets(sSheetName).Copy After:=wkbTarget.Sheets(lSheetCount)
    
    wkbSource.Close SaveChanges:=False
    wkbTarget.Close SaveChanges:=True
 
    Set wkbSource = Nothing
    Set wkbTarget = Nothing
End Sub

[vilic]

Thanks Kenny, I'try it. Target book will be open. The source will be closed.
Vladimir

[vilic]

Hi Kenny, it worked. I have changed it a little bit.