Results 1 to 9 of 9

Copy data from workbook selected in combobox (recieving object error)

Threaded View

  1. #1
    Registered User
    Join Date
    12-18-2016
    Location
    Utah, United States
    MS-Off Ver
    2016
    Posts
    7

    Copy data from workbook selected in combobox (recieving object error)

    I am trying to have my Workbook copy the data from another workbook. The workbook to copy data from is based on the selection from combobox1, which lists all open workbooks. After the selection is made, my macro to copy data will run.

    When I run the macro, it opens the combo box and I select the data I want copied and click “OK”. After I click “OK”, it runs the sub named “ClearandPullStatsinArray1”. However, I get a “no object defined” error when it is running this sub.

    I cannot figure out why. Screenshot below. Thank you for your help!

    USER FORM:

    Option Explicit
    Private Sub ComboBox1_Change()
     
    End Sub
     
    Private Sub CommandButton1_Click()
        MyFile = Me.ComboBox1.Value
        Unload Me
    End Sub
    Private Sub CommandButton2_Click()
        Stopped = True
        Unload Me
    End Sub
    Private Sub UserForm_Initialize()
        Dim wkb As Workbook
        Me.Label1.Caption = "Select workbook with transcripts:"
        With Me.ComboBox1
            For Each wkb In Application.Workbooks
                .AddItem wkb.Name
            Next wkb
        End With
    End Sub

    IN ANOTHER MODULE:

    Option Explicit
    Public MyFile As String
    Public Stopped As Boolean
     
    Sub test()
        Stopped = False
        UserForm1.Show
        If Stopped Then Exit Sub
        Run "ClearAndPullNewStatsInArray1"
    End Sub
     
    Sub ClearAndPullNewStatsInArray1()
     
    ' Pulls stats from LivePerson file lableled transcripts.
     
    Dim MyFile As Object
    Set MyFile = UserForm1.ComboBox1.Value
     
       
        Sheets("1").Range(("A18"), ("ZA15000")).ClearContents
        'Clears contents of current transcripts in Array 1^
        MyFile.Activate
        Sheets("TRANSCRIPTS").Range("A18", Range("A18").End(xlDown).End(xlToRight)).Copy
        'Copies stats from transcripts report^
        Windows("LivePerson Stats.xlsm").Activate
        Sheets("1").Range("A18").PasteSpecial
        'Pastes transcripts values from transcripts file to array 1^
        Application.CutCopyMode = False
        Sheets("Stats").Select
        'Workbooks("LivePerson Stats.xlsm").Save
        'Saves LivePerson Stats workbook
        MyFile.Close SaveChanges:=False
        'Forces the transcripts.xls export to close without saving^
        MsgBox "Copy of transcripts to Array 1 was successful."
        'All done!
    End Sub
    Attached Images Attached Images

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy data from workbook selected in combo box "object error"
    By cadams in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2017, 08:32 PM
  2. [SOLVED] Object variable error with combobox
    By ajam in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-17-2016, 02:13 PM
  3. [SOLVED] Copy selected data from one workbook to another
    By sjm.sonu82 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-22-2015, 09:14 AM
  4. ComboBox Value to use in VBA formula gets Run-time error 424 Object Required
    By Bud Wilkinson in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-15-2015, 10:34 PM
  5. Recieving the error "the object invoked has disconnected from its clients"
    By chidmas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-13-2013, 07:52 AM
  6. Open Workbook .Copy to Wb Causes Object Error 424
    By iamafiqing in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-18-2013, 08:41 PM
  7. Copy selected data to Workbook on Desktop
    By hutch@edge.net in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2009, 11:17 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1