TechTalk - Excel VBA : Copy Worksheet From Another Workbook

TechTalk - Excel VBA : Copy Worksheet From Another Workbook

A reference snippet on how to copy a sheet from another workbook to the active workbook.predetermined column.


Option Explicit
Private strSDKRawFileDir As String

Public Sub CopyWorksheet()
    strSDKRawFileDir = SDKFileOpenDialogBox
    CopyWorkbook (strSDKRawFileDir)
End Sub

Private Function SDKFileOpenDialogBox()
    Dim strTemp() As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        SDKFileOpenDialogBox = .SelectedItems.Item(1)
    End With
End Function


Private Sub CopyWorkbook(strWBFilePath As String)
    Dim wb As Workbook
    Dim activeWB As Workbook
    Set activeWB = Application.ActiveWorkbook

    Application.ScreenUpdating = False: Application.DisplayAlerts = False
    On Error Resume Next
    Set wb = Application.Workbooks.Open(strWBFilePath)
    wb.Worksheets("MeJuice").Copy After:=activeWB.Sheets(activeWB.Sheets.Count)
    activeWB.Activate
    wb.Close False
    Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub

A vlog demo to go with it.