Re: Errors Keep Popping Up
Hi ekfasy
I've got your code working & I presume it's putting the copied data where you want it. I haven't changed anything like that.
It's easier if you select the file you want from the dropdown before you start the macro, you won't have to stop the routine to do the check. I put a test in to compare the file name you selected to open with the name in the cell J1. If the J1 text is in the filename selected, it will run the routine, if not it will terminate.
The causes of the errors were
1) Matching of hyphens "-" and underscores "_". You had hyphens in the dropdown and underscores in the file names. The file you selected from the file open window won't match Cell J1 causing the Select Case part of the macro to never run & routine would exit every time.
2) When copying the data from the HIJ_123 etc workbook, you didn't fully qualify where the code was supposed to be copied from. You used ".Range(.Cells(11, 1), .Cells(z, 1)).Copy" when it needed to be "Wb2.Worksheets("Quote").Range(.Cells(11, 1), .Cells(z, 1)).Copy"
I changed a couple of things to make the code more readable, any questions, gimme a shout. The code is below & the file is attached for you to check out
If you have the editor open place a break just after myFile = application.blah blah by putting the mouse cursor on the left hand frame of the window, right next to the next line down & left click, select the file you want to open in J1, press F5 to start the macro, select the file to open & the macro will stop at the break. You can then step through the macro by pressing F8 to check out the code. Apologies if you already knew how to do all that 
Cheers
'Opens up a window for user to browse and choose which file to open up
Sub BrowseFileToOpen()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With
Dim Wb1 As Workbook ' This Workbook (ABC Quote To Customer)
Dim Wb2 As Workbook ' The Other Workbook
Dim lr As Long, rCount As Long, z As Long, y As Long
Dim BkName As String ' Name of the file chosen with GetOpenFileName
Dim Vendor As String ' Holds contents of Wb1 Cell J1
Dim myFile As String 'Using string will evaluate to an error
Set Wb1 = ThisWorkbook
Vendor = Sheet1.Range("J1")
'Restrict browsing other files - Only excel files
myFile = Application.GetOpenFilename("Excel Files (*.xl*),*.xl*", , "Choose File", "Open", False)
BkName = Dir$(myFile)
' Test if selected workbook is the same as that in cell J1
' If so, open it. If not, let user know & exit routine
If InStr(1, BkName, Vendor, vbTextCompare) = 0 Then
MsgBox " File Names Don't Match"
Sheet1.Range("J1").ClearContents
GoTo TheExit ' End routine including resetting EnableEvents etc
Else ' Open the file
' Set Open Workbook Variable.
On Error Resume Next ' Returns Error If Not Open
Set Wb2 = Workbooks(BkName)
On Error GoTo 0
' Test If External Workbook Is Open. If Not, Open It
If Nothing Is Wb2 Then
Workbooks.Open (myFile)
Set Wb2 = Workbooks(BkName)
Else: End If
'coding for drop-down menu
Select Case Vendor
'If "HIJ-123" is chosen in the drop-down box:
Case Is = "HIJ_123"
'create macro for HIJ-123
With Wb2.Worksheets("Quote") 'Change the name to match your worksheet.
lr = .Columns("A:E").Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
rCount = lr - 21
Wb1.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
Wb2.Worksheets("Quote").Range(.Cells(21, 1), .Cells(lr, 1)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("B24") 'Change the name to match your worksheet.
Wb2.Worksheets("Quote").Range(.Cells(21, 2), .Cells(lr, 2)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("C24") 'Change the name to match your worksheet.
Wb2.Worksheets("Quote").Range(.Cells(21, 3), .Cells(lr, 3)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("D24") 'Change the name to match your worksheet.
Wb2.Worksheets("Quote").Range(.Cells(21, 4), .Cells(lr, 5)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("E24") 'Change the name to match your worksheet.
End With
Wb2.Close False ' Close the other workbook without saving
Sheet1.Range("J1").ClearContents
MsgBox "HIJ_123 quote conversion is complete."
GoTo TheExit
'If "HIJ-456" is chosen in the drop-down box:
Case Is = "HIJ_456"
'create macro for HIJ-456 quote
With Wb2.Worksheets("Quote") 'Change the name to match your worksheet.
z = .Columns("A:E").Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
y = z - 11
Wb1.Worksheets("ABC Quote").Rows("24:" & CStr(24 + y)).EntireRow.Insert
Wb2.Worksheets("Quote").Range(.Cells(11, 1), .Cells(z, 1)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("C24") 'Change the name to match your worksheet.
Wb2.Worksheets("Quote").Range(.Cells(11, 2), .Cells(z, 2)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("D24") 'Change the name to match your worksheet.
Wb2.Worksheets("Quote").Range(.Cells(11, 3), .Cells(z, 3)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("B24") 'Change the name to match your worksheet.
Wb2.Worksheets("Quote").Range(.Cells(11, 4), .Cells(z, 5)).Copy _
Destination:=Wb1.Worksheets("ABC Quote").Range("E24") 'Change the name to match your worksheet.
End With
Wb2.Close False ' Close the other workbook without saving
Sheet1.Range("J1").ClearContents
MsgBox "HIJ_456 quote conversion is complete."
GoTo TheExit
End Select
End If
TheExit:
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
Exit Sub
End Sub
Display More