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
Bookmarks