Macro works from VBA UI, but not when assigned to a button

[tommychowdah]

Hi, I am having an issue with the code below. I am able to hit the run button in the VBA UI, and the macro works perfectly, but when I assign the macro to a button, it errors out. "Run-time error '1004': Method 'Range' of object '_Worksheet' failed. Any help would be much appreciated. Thank you!


Sub Import_Medians()


Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet


Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet


'-------------------------------------------------------------
'Open file with data to be copied


MsgBox "Select The Median Transfer Model."


Application.DisplayAlerts = False


vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)


'If Cancel then Exit


If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If


'--------------------------------------------------------------
'Copy Range


wsCopyFrom.Range("SP_FS_EXPORT").Copy
wsCopyTo.Range("SP_FS_IMPORT").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


wsCopyFrom.Range("SP_MS_EXPORT").Copy
wsCopyTo.Range("SP_MS_IMPORT").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


wsCopyFrom.Range("M_FS_EXPORT").Copy
wsCopyTo.Range("M_FS_IMPORT").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


wsCopyFrom.Range("M_MS_EXPORT").Copy
wsCopyTo.Range("M_MS_IMPORT").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


wsCopyFrom.Range("F_FSMS_EXPORT").Copy
wsCopyTo.Range("F_FSMS_IMPORT").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False


'Close file that was opened


wbCopyFrom.Close SaveChanges:=False


MsgBox "Done."


End Sub

 

[higrm]

I commented out your code section from 'Copy Range' to 'Close file...' and it worked with no issues on a command button, both an Active X and a form button. Perhaps it is getting confused with your range names. You don't mention which line gives you the error when you select Debug from the error message. Do you have the code in a module or on a sheet?