Paste Special Macro issue or macro look up issue

I need some assistance with a problem that I am having with my macro in Excel 2007.

I am combining two workbooks into one workbook. My Inventory Routing Guide tab does a lookup to the Master File Data tab and pulls that information into the tab. The problem that I am having is that the lookup function pulls across the information fine when there is not a formula in the cell. However, when there is a formula in the cell it inputs the information as “N/A”. I decided to a Paste Special for my Master File Data tab, but I still receive the same “N/A”.

The issue is with my MIN/MAX Button 2 macro.


I have attached my code and sample files for your review. Am I using an incorrect Paste Special command in my coding or is there a better way to get my information.

I apologize in advance, but I had to zip my Inventory Routing Guide because it is a .csv file.

Here is my code for the paste special

Sub Get_Master_File_Data()

curFile = ActiveWorkbook.FullName   ' Name of workbook
nLastSlash = InStrRev(curFile, "\")
curFileName = Right(curFile, Len(curFile) - nLastSlash)
'curFileName = Left(curFileName, Len(curFileName) - 4)

sFile = Worksheets("Main Menu").Range("E7").Value   ' Name of Solarsoft data file
nLastSlash = InStrRev(sFile, "\")
sFileName = Right(sFile, Len(sFile) - nLastSlash)
'sFileName = Left(sFileName, Len(sFileName) - 4)
Workbooks.Open (sFile)     ' Open workbook

Workbooks(curFileName).Activate     ' Back to verification workbook

Sheets("Master File Data").Select      ' Using Solarsoft worksheet delete all previous data
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select

Workbooks(sFileName).Activate     ' Back to Solarsoft workbook
Cells.Select                    ' Copy all data from Solarsoft workbook
Selection.Copy

Workbooks(curFileName).Activate     ' Back to verification workbook
Sheets("Master File Data").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

Workbooks(curFileName).Activate     ' Back to verification workbook

Application.CutCopyMode = False
Workbooks(sFileName).Close SaveChanges:=False       ' Close Solarsoft data workbook and DO NOT SAVE changes

End Sub

Here is my code for my lookup.

Sub Get_Update_Streme_Data()


Sheets("InventoryRoutingGuide").Select
' Find number of rows in Inventory Routing Guide for progress bar
For i = 2 To 65000  ' For each part in the Inventory Routing Guide
    If IsEmpty(Cells(i, 2).Value) Then Exit For
Next i
nrows = i - 1

For i = 2 To nrows  ' For each part in the Inventory Routing Guide
    If IsEmpty(Cells(i, 2).Value) Then Exit For
    
    For j = 3 To 65000  ' Find part in Master File Data
        If IsEmpty(Cells(j, 2).Value) Then
            Cells(i, 8).Value = "N/A"
            Cells(i, 9).Value = "N/A"
            Exit For
        End If
        ' // update the text below.
        If Val(Cells(i, 2).Value) = Val(Sheets("Master File Data").Cells(j, 2).Value) Then
            Cells(i, 8).Value = Sheets("Master File Data").Cells(j, 13).Value
            Cells(i, 9).Value = Sheets("Master File Data").Cells(j, 14).Value
            Exit For
        End If
    Next j
    
    PctDone = i / nrows
    With UserForm2
        .FrameProgress.Caption = Format(PctDone, "0%")
        .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
    End With
    DoEvents
    
Next i
           
Cells(1, 8).Value = "MIN"       ' Add column header
Cells(1, 9).Value = "MAX"       ' Add column header

Unload UserForm2

Sheets("Main Menu").Select
Range("A1").Select
MsgBox ("Inventory Routing Guide updated with MIN and MAX!")

End Sub

I have updated my macro above to possibly help provide some additional feedback. I have modified the .csv file that I normally import to a .xls file. I have modified the entry screen to accept the file as an .xls file. I hope this alleviate any concerns that my original post may have caused.

Please let me know if you need any additional information.