Creating Transmittal record based on PDF files generated

[camerong]

Hi guys,

Have a question, not sure how to adjust the formula below to get the result I require. Basically, I have the formula below currently which is assigned to a macro button on sheet 1 (named "RFI LOG", see Master RFI LOG File v1.7 - Excel 2023-05-17 07.34.). What it does is prints to PDF worksheet 1, it then goes through sheets 2-101 checking cell "E1" for the result "OUTSTANDING". For each sheet that has that value in cell "E1" it will print it to PDF.

Before it prints any worksheets to PDF it first creates a new folder in the workbook location and another folder within this new folder named "Individual sheets".

Once it has started printing to PDF it will save the pdf files in the inner most new folder (named "Individual sheets") as a separate pdf per worksheet. Following printing all the single worksheet PDF files, it then combines them into 1 PDF file and saves that up a level in the same area as where the macro created the new folder named "Individual sheets".

Now what I need it to do now is create a record of each time the macro is run on a worksheet named "TRANSMITTAL HISTORY" (same worksheet each time). I have attached a screenshot (Master RFI LOG File v1.7 - Excel 2023-05-17 07.33.) of how I would like it to be laid out on this worksheet. Basically, before the macro is run for the first time, rows 1-5 will already be on the sheet. After the macro is run the first time it will generate the data shown on rows 6-15 (this may be more or less depending on how many outstanding RFI's there are / how many PDFs). The cell co-ordinates shown (on the same screenshot, Master RFI LOG File v1.7 - Excel 2023-05-17 07.33.) refer to the values shown on each worksheet for that RFI (see Master RFI LOG File v1.7 - Excel 2023-05-17 07.35.).

When the macro is run the second time and so on the macro will need to pick up that there is already information on the "TRANSMITTAL HISTORY" worksheet (in screenshot shown rows 1-15, Master RFI LOG File v1.7 - Excel 2023-05-17 07.33.) and so insert the same data fields as the previous run, except below the existing data (shown as rows 16-24). And so on and on and on, each time it is run so that there is a list generated of each time the macro is run and a record of the RFI cell values when the macro was run.

There is a checkbox shown on the "TRANSMITTAL HISTORY" worksheet in column "A" that I will need generated each time the macro is run. When the checkbox is ticked, I need the cell fill color to change to light green for the cells in columns A:D (as seen in screenshot Master RFI LOG File v1.7 - Excel 2023-05-17 07.33.), Likewise when it is not ticked the color for these cells will need to be light red. The macro will also need to autofill the "DATE", "TIME" and "OFFICE USER NAME" fields to the right of this checkbox WHEN the checkbox is checked on, I do not want anything in these 3 cells when the macro is run, only when the user checks the checkbox in column "A" manually. The date and time will be the date and time when the checkbox is checked. The "OFFICE USER NAME" will be the name of the office account with the workbook open at the time.

There are another 2 checkboxes that will be required to be generated each time the macro is run, these do not need to be linked to any cell, just need the capability of being able to be checked on and off. They are seen in column "C" (screenshot Master RFI LOG File v1.7 - Excel 2023-05-17 07.33.).

The last step of the macro is that I need excel to take the users screen to highlight the checkbox cell generated in column "A" (screenshot Master RFI LOG File v1.7 - Excel 2023-05-17 07.33.).

Thanks guys

Option Explicit
Public Sub Print_all_outstanding_TO_NEW_FOLDER()

    Dim i As Long
    Dim dt As String
    Dim mainFolder As String
    Dim sheetsFolder As String
    Dim combinedPDF As String
  
    dt = Format(Now, "dd.mm.yyyy hh.mm.ss")
    With Worksheets(1)
        mainFolder = ActiveWorkbook.Path & "\" & .Range("D2").Value & " - " & .Range("D3").Value & " - RFI Set - " & dt & "\"
        sheetsFolder = mainFolder & "Individual Sheets\"
        combinedPDF = mainFolder & .Range("D2").Value & " - " & .Range("D3").Value & " - RFI Set - " & dt & ".pdf"
    End With
    If Dir(mainFolder, vbDirectory) = vbNullString Then MkDir mainFolder
    If Dir(sheetsFolder, vbDirectory) = vbNullString Then MkDir sheetsFolder
    
    Print_RFI_LOG_sub sheetsFolder
    
    For i = 2 To 101
        If Worksheets(i).Range("E1").Value = "OUTSTANDING" Then
            Print_to_PDF_sub i, sheetsFolder
        End If
    Next
    
    Worksheets(1).Select
    For i = 2 To 101
        If Worksheets(i).Range("E1").Value = "OUTSTANDING" Then
            Worksheets(i).Select Replace:=False
        End If
    Next
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=combinedPDF, _
                                    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
                                    
    Worksheets(1).Select
    
    MsgBox "Created PDFs in " & mainFolder

End Sub

Sub DefaultMsgBox()
    MsgBox "Process complete"
End Sub

Sub Print_to_PDF_sub(n As Long, sheetsFolder As String)
    Dim PDFfile As String
    
    With Worksheets(n)
        PDFfile = sheetsFolder & "RFI " & .Range("E4").Value & " - " & .Range("B4").Value & " - " & .Range("B5") & ".pdf"
    End With
    
    Worksheets(n).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                                      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

Sub Print_RFI_LOG_sub(sheetsFolder As String)
    Dim PDFfile As String
    
    With Worksheets(1)
        PDFfile = sheetsFolder & "RFI RECORD SHEET - " & .Range("D2") & " - " & .Range("D3") & ".pdf"
    End With
    
    Worksheets(1).ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
                                      Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False
End Sub

 

[OaklandJim]

I'd like to assist but without something concrete to work with designing code is difficult, for me at least. Is there any chance that you could post a link to your workbook, with fake data if necessary. Use the link icon above to do it. Use Dropbox, 1Drive, etc.

 

[camerong]

Hi @OaklandJim,

I've removed all sensitive information from the workbook, see a link to a copy below:

Example Workbook

Thanks for your help

 

[camerong]

Hi @OaklandJim,

Just thought Id mention the macro buttons I am currently using for the above code is on Worksheet "RFI LOG", located in the 2 in columns V and W (far right).

The two buttons are slightly different, the code above is for the one at the top, relating to outstanding RFIs.

But I will be wanting to do the same to both macro buttons, with the 2nd (lower) doing all RFIs (outstanding and resolved).

Thanks

 

[camerong]

Hi @OaklandJim

Please see new link below to example workbook:

Example Workbook

Thanks