Subs working in isolation but not main code?

[Mr2017]

Hi

I've got some code which imports some data from a file.

I've also got two separate Subs which

i) check if a user has selected the right file by checking the text in cell A8
ii) copy the formulas from row 1 into row 5 until the end of the range when data is imported.

Although the Subs work in isolation, they don't work when I add them to the code to import the file?

This is the check/validation code:

Sub verify()
    If ActiveSheet.Cells(8, 1).Value <> "Product ID" Then
            MsgBox "Check you've selected the right file"
            Else
            MsgBox "You've got the right file"
        Exit Sub
        End If
End Sub

This is the formula code

Sub Formulas()
   
   Range("AD1:AL1").Copy Range("AD5:AL5")
   Range("AD5", Range("A" & Rows.Count).End(xlUp).Offset(, 38)).FillDown
   
   Range("BY1:CL1").Copy Range("BY5:CL5")
   Range("BY5", Range("A" & Rows.Count).End(xlUp).Offset(, 90)).FillDown
   
End Sub

And this is the code that I've tried to add the subs to. I've commented them out in the sections where they're supposed to run.

Does anyone know why they'd fail when added to the main Sub?

Thanks to Mumps for help on the import code!

Sub Import()


    Application.ScreenUpdating = False
    Dim flder As FileDialog, FileName As String, lastRow1 As Long, bottomA As Long, bottomB As Long, lCol As Long
    Dim wkbSource As Workbook, wkbDest As Workbook, desWS1 As Worksheet, desWS3 As Worksheet, FileChosen As Boolean
    Set wkbDest = ThisWorkbook
    Set desWS1 = wkbDest.Sheets("Line level detail")
    Set desWS3 = wkbDest.Sheets("actual export")
    Set flder = Application.FileDialog(msoFileDialogFilePicker)
    flder.Title = "Please Select a File to import."
    'flder.InitialFileName = Environ("UserProfile") & "\Downloads"
    flder.InitialFileName = ThisWorkbook.Path
    flder.Filters.Clear
    flder.Filters.Add "Excel xlsx. Files", "*.xlsx"
    FileChosen = flder.Show
    If Not FileChosen Then
        MsgBox "You didn't select a file?"
        Exit Sub
    End If
    FileName = flder.SelectedItems(1)
    
                'If ActiveSheet.Cells(8, 1).Value <> "Product ID" Then
                 '   MsgBox "Check you've selected the right file"
                  '  Exit Sub
                'End If
    Set wkbSource = Workbooks.Open(FileName)
    With wkbSource.Sheets("Report")
        lastRow1 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lCol = .Cells(9, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(9, 1), .Cells(lastRow1, lCol)).Copy desWS1.Cells(desWS1.Rows.Count, "B").End(xlUp).Offset(1, 0)
        .Range(.Cells(9, 1), .Cells(lastRow1, lCol)).Copy desWS3.Cells(desWS1.Rows.Count, "B").End(xlUp).Offset(1, 0)
        With desWS1
            bottomA = .Range("A" & .Rows.Count).End(xlUp).Row
            bottomB = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("A" & bottomA + 1).Resize(bottomB - bottomA, 1) = ActiveSheet.Range("B4").Value
        End With
        With desWS3
            bottomA = .Range("A" & .Rows.Count).End(xlUp).Row
            bottomB = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("A" & bottomA + 1).Resize(bottomB - bottomA, 1) = ActiveSheet.Range("B4").Value
        End With
    End With
    
    'Call FillFormulaeTest
    'Populate formulae


    'Range("AD1:AL1").Copy Range("AD5:AL5")
    'Range("AD5", Range("A" & Rows.Count).End(xlUp).Offset(, 38)).FillDown
   
    'Range("BY1:CL1").Copy Range("BY5:CL5")
    'Range("BY5", Range("A" & Rows.Count).End(xlUp).Offset(, 90)).FillDown
    
    wkbSource.Close savechanges:=False
    Application.ScreenUpdating = True
End Sub

 

[mole999]

FileName = flder.SelectedItems(1)

verify
'If ActiveSheet.Cells(8, 1).Value <> "Product ID" Then
' MsgBox "Check you've selected the right file"
' Exit Sub
'End If
Set wkbSource = Workbooks.Open(FileName)
With wkbSource.Sheets("Report")
lastRow1 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lCol = .Cells(9, .Columns.Count).End(xlToLeft).Column
.Range(.Cells(9, 1), .Cells(lastRow1, lCol)).Copy desWS1.Cells(desWS1.Rows.Count, "B").End(xlUp).Offset(1, 0)
.Range(.Cells(9, 1), .Cells(lastRow1, lCol)).Copy desWS3.Cells(desWS1.Rows.Count, "B").End(xlUp).Offset(1, 0)
With desWS1
bottomA = .Range("A" & .Rows.Count).End(xlUp).Row
bottomB = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A" & bottomA + 1).Resize(bottomB - bottomA, 1) = ActiveSheet.Range("B4").Value
End With
With desWS3
bottomA = .Range("A" & .Rows.Count).End(xlUp).Row
bottomB = .Range("B" & .Rows.Count).End(xlUp).Row
.Range("A" & bottomA + 1).Resize(bottomB - bottomA, 1) = ActiveSheet.Range("B4").Value
End With
End With



'Call FillFormulaeTest
'Populate formulae

Formulas
'Range("AD1:AL1").Copy Range("AD5:AL5")
'Range("AD5", Range("A" & Rows.Count).End(xlUp).Offset(, 38)).FillDown

'Range("BY1:CL1").Copy Range("BY5:CL5")
'Range("BY5", Range("A" & Rows.Count).End(xlUp).Offset(, 90)).FillDown

wkbSource.Close savechanges:=False
Appl

 

[Mr2017]

Hi mole999

Thanks for your response.

I tried it, but it didn't work.

Could you please clarify what you believe was wrong?

Thanks in advance.

 

[mole999]

are they in the same sheet / module. If you step through with F8 and watch the code does it move to the sub and back again to the main code

 

[Mr2017]

Yes, they're in the same module.

If I step through the code, I still get the MsgBox saying "Check you've selected the right file" even though I have selected the right file, with the words "Product ID" in cell A8.

But the code below works as intended if you run it as a separate Sub. I've never seen this kind of error before? Any other ideas?

Sub verify()
If ActiveSheet.Cells(8, 1).Value <> "Product ID" Then
MsgBox "Check you've selected the right file"
Else
MsgBox "You've got the right file"
Exit Sub
End If
End Sub

 

[mole999]

is product ID supposed to be a reference value, as it is it is looking for text

 

[Mr2017]

“Product ID” is just text.

The verify Sub finds “Product ID” in A8 in a file that has the text “Product ID’ when you run the Sub in isolation. And returns the alternative message in the message box, when a file doesn’t have that text.

The Sub just doesn’t work when added to the other Sub.