VBA Import data from external worksheet - variable worksheet name

Question

I'm looking to do the following:

CommandButton in a destination Worksheet opens a source file (dialog box to choose which one)
Finds a worksheet (always the same name - "Performance") within the source file
Copies a range of cells (actually a couple of separate ranges - to be added)
Makes sure destination sheet (which has the same name as cell I2 in source sheet) exists
Pastes values to same ranges in destination Worksheet
Closes source file

I have this so far:

Private Sub CommandButton1_Click()

Dim SourceFile As String
Dim SourceBook As Workbook
Dim DestinationBook As Workbook
Dim desiredName As String

Set DestinationBook = ThisWorkbook

SourceFile = Application.GetOpenFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

Set SourceBook = Workbooks.Open(SourceFile)

SourceBook.Sheets("Performance").Activate
desiredName = ActiveSheet.Range("I2")
Application.CutCopyMode = True
SourceBook.ActiveSheet.Range("E25:I64").Copy
DestinationBook.Activate

If WorksheetExists = False Then

        MsgBox "Couldn't find " & desiredName & " sheet within destination workbook"

    Call SourceBook.Close(False)

    Exit Sub

Else


Range("E25:I64").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Application.CutCopyMode = False

Call SourceBook.Close(False)

End If

End Sub

Function WorksheetExists() As Boolean

Dim sh As Object

    For Each sh In DestinationBook.Worksheets
      If sh.Name = desiredName Then WorksheetExists = True: sh.Activate
      Exit For
    Next

End Function

I'm getting Run-time error '424': Object Required

Any suggestions...?

Thanks in advance!

Tony M Tony M · Accepted Answer · 2017-08-04T16:02:56

Here is a modification of your latest code. Notice these additions: 1) "Option Explicit" ensures you've properly declared all variables, 2) variables have been assigned to the important workbooks, worksheets, and ranges, 3) needed variables are passed to the WorkSheetExists function. For this to work there should be sheets named "Performance" and "testSheet" in the DestinationBook, and "testSheet" in I2 of the SourceBook. Remember, that this is just an attempt to "get you going" so I expect you'll need to modify.

Option Explicit
Sub test()

Dim SourceFile As String
Dim SourceBook As Workbook, performanceSh As Worksheet
Dim DestinationBook As Workbook
Dim desiredName As String
Set DestinationBook = ThisWorkbook

SourceFile = Application.GetOpenFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
Set SourceBook = Workbooks.Open(SourceFile)
Set performanceSh = SourceBook.Sheets("Performance")
desiredName = performanceSh.Range("I2")
Application.CutCopyMode = True
performanceSh.Range("E25:I64").Copy
If WorksheetExists(DestinationBook, desiredName) = False Then
        MsgBox "Couldn't find " & desiredName & " sheet within destination workbook"
    SourceBook.Close(False)
    Exit Sub
Else
Range("E25:I64").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
SourceBook.Close(False)
End If
End Sub

Function WorksheetExists(destWk As Workbook, theName As String) As Boolean
Dim sh As Object
    For Each sh In destWk.Worksheets
      If sh.Name = theName Then WorksheetExists = True: sh.Activate
      Exit For
    Next
End Function

VBA Import data from external worksheet - variable worksheet name

1 Answers