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:
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)
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.
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
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