Problem with macros running order

[Krb00]

Hi,

I ran into a problem with the following code. I have to run macros in this sequence:
firstfile -> secondfile -> Calctab -> calrep

- firstfile imports 4 different .csv files
- secondfile copy and paste a PDF file
- Calctab performs operations on data
- calrep extracts data from the pasted pdf

If I run the macros in this order: firstfile -> secondfile -> calrep, everything works.

Unfortunately, if I run the Calctab macro even once then the calrep macro no longer returns results.

Hope someone can help me with this.
Thank you

'***************************
Sub firstfile()

Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False

Dim Folder_Picker As FileDialog
Dim my_path As String
Set Folder_Picker = Application.FileDialog(msoFileDialogFolderPicker)
Folder_Picker.Title = "Select a folder" & FileType
Folder_Picker.Filters.Clear
Folder_Picker.Show
If Folder_Picker.SelectedItems.Count = 1 Then
    my_path = Folder_Picker.SelectedItems(1) & "\"
Else
    Exit Sub
End If
ActiveSheet.Range("D2").Value = my_path


Filename = Dir(my_path & "*.csv*")
Do While Filename <> ""
 Workbooks.Open Filename:=my_path & Filename, ReadOnly:=True
 For Each Sheet In ActiveWorkbook.Sheets
 Sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next Sheet
 Workbooks(Filename).Close
 Filename = Dir()
Loop

Sheets(2).Rows("1:58").EntireRow.Delete
Sheets(3).Rows("1:57").EntireRow.Delete
Sheets(4).Rows("1:57").EntireRow.Delete
Sheets(5).Rows("1:56").EntireRow.Delete
Sheets(5).Rows("2").EntireRow.Delete


End Sub

'**********************************************
Sub secondfile()

    Dim InitialFolder As String, FullName As Variant, ShtPdfData As Worksheet
    Dim wdApp As Object, wdPdfDoc As Object, wdRange As Object
    
    Application.ScreenUpdating = False
    
    InitialFolder = "C:\Users\Me\Desktop"
    ChDrive Left(InitialFolder, 1)
    ChDir InitialFolder
    FullName = Application.GetOpenFilename(" (*.pdf), *.pdf", 1)
    Sheets(1).Range("K2").Value = FullName

    If Not VarType(FullName) = vbBoolean Then

        Set wdApp = CreateObject("Word.Application")
        wdApp.Visible = True
        Set wdPdfDoc = wdApp.Documents.Open(Filename:=FullName, ConfirmConversions:=False, ReadOnly:=False, Format:=0, NoEncodingDialog:=True)
        Set wdRange = wdPdfDoc.Range(1)
        wdRange.WholeStory
        wdRange.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.name = "Data"
        Worksheets("Data").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
        Application.Goto Worksheets("Data").Range("A1")
        wdPdfDoc.Close False
        wdApp.DisplayAlerts = False
        wdApp.Quit
    End If
    
    Worksheets("Home").Activate    'Worksheets(1)
    Application.ScreenUpdating = True
    

End Sub

'**************************************
Sub calrep()


Dim findName As Range
Dim templine As Integer
Dim tempcont As String
Dim result As String

Set findName = Worksheets("Data").Cells.Find("TEMPERATURE")
If Not findName Is Nothing Then
templine = findName.Row
tempcont = Worksheets("Data").Range("A" & templine)
result = extractnumbers(tempcont)

Dim tempSplit As Variant
    tempSplit = Split(Application.Trim(result), " ")

    Dim res As String
    Dim tempreg As Double
   

    Dim p As Long
    For p = LBound(tempSplit) To UBound(tempSplit)
        tempreg = tempSplit(UBound(tempSplit))
    
    Exit For
    Next


Worksheets("Home").Range("E5") = tempreg
Else
MsgBox "not found"
End If

End Sub

'****************************************************************
Sub Calctab()

Dim rngRR As Range
Dim LC As Long
Dim ave As Double
Dim minc As Double
Dim maxc As Double
Dim maxcCol As Integer
Dim mincCol As Integer
Dim minTC As String
Dim maxTC As String

    LRR = Worksheets(5).Cells(Rows.Count, 1).End(xlUp).Row
    LC = Worksheets(5).Cells(1, Columns.Count).End(xlToLeft).Column
    
        For i = 2 To LRR
            With Worksheets(5)
                Set rngRR = .Range(.Cells(i, 2), .Cells(i, LC - 1))
                ave = Round(Application.WorksheetFunction.Average(rngRR), 2)
                minc = Application.WorksheetFunction.Min(rngRR)
                mincCol = rngRR.Find(minc, rngRR(rngRR.Columns.Count), LookIn:=xlValues, Lookat:=xlWhole).Column
                minTC = .Range(.Cells(1, mincCol), .Cells(1, mincCol))
                maxc = Application.WorksheetFunction.Max(rngRR)
                maxcCol = rngRR.Find(maxc, rngRR(rngRR.Columns.Count), LookIn:=xlValues, Lookat:=xlWhole).Column
                maxTC = .Range(.Cells(1, maxcCol), .Cells(1, maxcCol))
            End With
        Sheets(2).Range("G" & i) = ave
        Sheets(2).Range("B" & i) = minc
        Sheets(2).Range("C" & i) = minTC
        Sheets(2).Range("D" & i) = maxc
        Sheets(2).Range("E" & i) = maxTC
        Next i

End Sub

'******************************
Function extractnumbers(s As String)
  With CreateObject("VBScript.RegExp")
    .Pattern = "[^0-9.+]"
    .Global = True
    extractnumbers = Replace(.Replace(s, " "), ".", ",")
  End With
End Function

 

[James006]

Hi,

To keep your sequence in check, you can use a Boolean variable as a flag ... True / False... to control whenever you actually need to run calrep

 

[Krb00]

Thank you for your kind reply. I try to be clearer.

First of all, I import inside the workbook two .csv files and data from a pdf file running the macros "firstfile" and "secondfile".
The attached images show the structure of the 3 files.
One .csv file contains raw data, the other .csv file shows stats based on raw data.
"removeTC" macro allows me to delete columns from raw data and "Calctab" macro performs calculations in order to obtain updated stats.

"calrep" macro just extract data from the imported pdf file and print it in Sheets("Home"), that is the first sheet of the workbook.

The problem is that if I run "Calctab" before "calrep" then "calrep" is not able to find the TEMPERATURE REG. value.
If I run "calrep" before "Calctab" everything works.

As you can see in the vba code "Calctab" and "calrep" are not related to each other but it seems that "Calctab" interferes in some way with "calrep".