1

I'm looking to do the following:

  1. CommandButton in a destination Worksheet opens a source file (dialog box to choose which one)
  2. Finds a worksheet (always the same name - "Performance") within the source file
  3. Copies a range of cells (actually a couple of separate ranges - to be added)
  4. Makes sure destination sheet (which has the same name as cell I2 in source sheet) exists
  5. Pastes values to same ranges in destination Worksheet
  6. 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!

L.J.
  • 13
  • 7

1 Answers1

1

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
Tony M
  • 1,694
  • 2
  • 17
  • 33
  • Thanks Tony, the point is though that I want it to make sure on its own that the right destination sheet is active. The right destination sheet is one with name equal to the contents of cell I2 in source sheet within source file just opened. Technically it should come back to the correct destination sheet, since this is where the button is located, but I want to make absolutely sure it doesn't accidentally change - there are multiple sheets in the destination file and the same operation will have to be performed for each one separately. They're all formatted exactly the same, so prone to mixup – L.J. Aug 04 '17 at 16:14
  • Okay, I've added more code to address that. Hope it helps. – Tony M Aug 04 '17 at 16:18
  • Almost there, but it seems it's looking for the sheet in the just-opened source workbook - which it won't find. To clarify, cell I2 is located in source workbook and source sheet called "Performance", the destination sheet within destination file is called the same as contents of I2 in source sheet. Say source sheet says STACK in I2, I want it to find sheet called STACK in destination workbook and paste there. – L.J. Aug 04 '17 at 16:32
  • I've now modified the code to address your question – Tony M Aug 04 '17 at 18:54
  • Thanks Tony, I've copied as is and for the moment it's not working - throws up the Message Box (Not found...) although worksheet definitely present, no errors as such. The devil is in the Function then, since it says it's False - could it be that it's searching for the destination worksheet in the source workbook, rather than destination workbook? If so, how do I direct it back to destination for the search? – L.J. Aug 04 '17 at 20:16
  • I just copied the code that I pasted into a workbook and ran it and it worked fine. I suggest you repeat what I've done with a fresh workbook and a fresh document that it opens, giving them each the required sheet names & value in cell I2. Then, step thru that code carefully to make sure you understand it before applying it to your code. The function loops thru all sheets of the destWk. If you want to check that, make a string variable (Dim s as string) & then put in a line like (s=destWk.name) in the code, and then hover your mouse over (s) to confirm the name is correct. – Tony M Aug 04 '17 at 20:51
  • Copied to a new sheet for testing and get the same outcome - Function seems to come out as False every time, which throws out the error MsgBox and closes Sub. – L.J. Aug 07 '17 at 08:24
  • Sorry it's not working for you. I went ahead & followed my steps again & it worked fine. Here is what I did: 1) Made 2 new workbooks named "book1" and "book2", 2) named a sheet "testSheet" in book1 and put the macro code in a module, 3) named a sheet "Performance" in book2, 4) put "testSheet" in I2 of book 2, 5) put text in range E25:I64 of book2, 6) closed book2, 7) ran the macro in book1, 8) selected book2 in the dialog. The result: the contents of E25:I64 were copied into book1, as expected. Again, I want to emphasize that my code is just to demonstrate & I expect you to modify as needed – Tony M Aug 07 '17 at 15:53