excelvbaexcel-2016

User prompt to select a specific workbook and specific sheet within that workbook to cut and paste into current workbook


This code allows me to choose a file and open it, but when I enter the sheet name it gives me

runtime error 91: block variable not set

This code does not close the workbook wb after opening it because I wanted to make sure it was at least opening the workbook, which it is.

What I am trying to achieve:

  1. Macro is run on a specific sheet of any workbook (even better if it can be run and automatically navigate to that sheet - the name of the sheet will always be the same, even if the workbook this is run on isn't)

  2. Macro prompts user to choose a file and then choose a sheet within that file by name.
    I want to check if the sheet name entered does not exist.

  3. Macro cuts specific section of that sheet and pastes it to the sheet in the workbook where the Macro was initially run.

Sub ChooseImportSheet()

Dim w As String, wb As Workbook, myFile As Office.FileDialog

Application.ScreenUpdating = False

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
    .Title = "Choose File"
    .Allow MultiSelect = False
    If .Show <> -1 Then
        Exit Sub
    End If
    FileSelected = .SelectedItems(1)

    w = InputBox("Enter Sheet Name")
    Workbooks.Open(wb)
    wb.Sheets(w).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks("ThisWorkbook").Activate
    ActiveSheet.Sheets.Range("A4").Select
    Selection.Paste

    Application.Screen Updating = True

End Sub

Solution

  • Try this:

    Sub ChooseImportSheet()
    
        Dim w As String, wb As Workbook, FileSelected
        Dim wsDest As Worksheet, wsSrc As Worksheet
        
        Application.ScreenUpdating = False
        
        With Application.FileDialog(msoFileDialogOpen)
            .Title = "Choose File"
            .AllowMultiSelect = False
            If .Show <> -1 Then Exit Sub
            FileSelected = .SelectedItems(1)
        End With
        Set wsDest = ActiveSheet               'capture the active sheet
        Set wb = Workbooks.Open(FileSelected)  'reference the opened workbook
        
        w = InputBox("Enter Sheet Name")
        On Error Resume Next          'ignore error if no matching sheet
        Set wsSrc = wb.Worksheets(w)  'try to get the worksheet
        On Error GoTo 0               'stop ignoring errors
        
        If wsSrc Is Nothing Then      'sheet not found?
            MsgBox "Sheet '" & w & "' was not found in " & wb.Name
        Else
            With wsSrc.Range("A1")
                Debug.Print "Copying from " & .Address()
                .CurrentRegion.Copy wsDest.Range("A4")
            End With
        End If
       
        'wb.Close false
        Application.ScreenUpdating = True
    End Sub