CODEWITHSUNDEEP
excelvbaexternal
Syed Meesam Ali
Syed Meesam Ali

Reputation: 35

Copying external Excel sheet to current workbook using VBA

I'm working on small project in which I need to add sheets to the currently open workbook from any external database of worksheets in another workbook. I made a form to import any sheet that is required in the currently open (active) workbook.

The sheets will be copied from remote (in any other folder but same computer) workbook. I am using following code but due to unknown reasons the sheets are NOT getting copied to my current workbook.

Dim wb As Workbook
Dim activeWB As Workbook
Dim FilePath As String
Dim oWS      As String
Set activeWB = Application.ActiveWorkbook

FilePath = "D:\General Required Docs\DATA.xlsm"

If optFirst.Value = True Then
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   On Error Resume Next
   oWS = cboMaterial.Value
   Set wb = Application.Workbooks.Open(FilePath)
   wb.Worksheets(oWS).Copy      
   After:=Application.ActiveWorkbook.Sheets(ThisWorkbook.Sheets.count)
   activeWB.Activate
   wb.Close False

   Application.ScreenUpdating = True
   Application.DisplayAlerts = True

Upvotes: 2

Views: 61719

Answers (2)

Ramsin Yacoob
Ramsin Yacoob

Reputation: 21

Sub Add_Bridge_1()
    Dim wbk1 As Workbook, wbk2 As Workbook

    'add your own file path
    fileStr = "C:\Program Files\Microsoft Office\Office\HL DDS Templates.xlsx"

    Set wbk1 = ActiveWorkbook
    Set wbk2 = Workbooks.Add(fileStr)

    'wbk2.Sheets("Bridge 1").Copy After:=Workbooks("WorkbookNameYouCopyCodeInto").Sheets(1)
    wbk2.Sheets("Sheet Name").Copy After:=wbk1.Sheets(1)
    wbk2.Saved = True
End Sub

Upvotes: 2

Ripster
Ripster

Reputation: 3585

Change

wb.Worksheets(oWS).Copy      
   After:=Application.ActiveWorkbook.Sheets(ThisWorkbook.Sheets.count)

to

wb.Worksheets(oWS).Copy      
   After:=activeWB.Sheets(activeWB.Sheets.count)

assuming that oWS is the index of the worksheet you want to copy.

Upvotes: 2

Related Questions