Fill a cell once the file is printed

[Lux Aeterna]

There's an excel form that we fill out and print several times every day. What I need is to have a cell filled every time the sheet is printed.

By the way, we only print as PDF using a macro and an assigned button.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\Νέα Τεστ Παπ\" & Range("AH1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

So, if for example I put ID number 1 (ID number goes to $U$2 - Sheet Test results), I'd like cell B5 (Sheet Demographics) to get filled with the info from $AA$9 (Sheet Test results) as soon as the PDF is printed.
If I put ID number 2, I'd like cell B6 to get the info from $AA$9 and so on. List goes down to cell B10033 (Sheet Demographics).

It would be a great help if the PDF is allowed to be printed only when $AA$9 is filled.

Thank you in advance!

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Lux Aeterna]

Mini sheet makes my excel crush. Formulas aren't important for what I need, so I'll put up a screenshot and try to explain better. If still necessary, I can upload the whole file.

What we do is to fill the ID (numbers from 1 to 10029) and then print the sheet as pdf.

What I need to be done is to (1) add the name of the supervisor in the box AA9 (we'll do that manually), (2) print the sheet as PDF (we use a macro for that), and (3) have the supervisor's name copied to the appropriate cell in another sheet. If ID number is 1, the appropriate cell is B5. If ID number is 2, the appropriate cell is B6 ... If ID number is 10029, the appropriate cell is B10033 (ID number plus 4). It would be best if step (3) is added to the printing macro we use. Moreover, it would be a great help if printing is disabled if AA9 is empty.

For example,
When I put ID 4 (Results sheet, cell U4) all greys are automatically filled with the respective data from the Demographics sheet. I want the supervisor to fill their name (Results sheet, cell AA9), print the sheet, and along with printing it have their name transferred to the respective cell in the Demographics sheet (in that case, Β8).

Hope that makes sense now.

Results

Demographics

 

[mumps]

OK, that gives me a good idea of what you want to do but it is difficult to work with pictures. Could you upload the file and paste the link here?

 

[Lux Aeterna]

Sure, thanks.

I've deleted several columns to reduce size and none of the "auto" options work, but you probably won't need any of the formulas I've used.

File.xlsm

Shared with Dropbox

www.dropbox.com

password is 299

 

[mumps]

When I put ID 4 (Results sheet, cell U4) all greys are automatically filled with the respective data from the Demographics sheet.

The macro I have in mind will do the following after the ID is entered in U2 and the ENTER key has been pressed:
-check to see if a name has been entered in AA9
-if AA9 is empty, the macro will terminate and nothing will be done
-if AA9 has been filled in with a name, the macro is automatically be executed
-the data in AA2:AA8 will be filled in - What data goes into Date1 and Date 2? I couldn't see Date1 and Date 2 in the Demographics sheet.
-the name will be entered in the appropriate row in column B of Demographics
-the Results sheet will be printed as a PDF

The actions will be performed in the order listed. Have I interpreted correctly what you want to do? If not, please list the correct steps as I have done.

 

[Lux Aeterna]

Not exactly.
These are all the steps that we follow:
Step 1: ID is entered in U2, ENTER key is pressed and grey boxes auto retrieve data from another sheet (I've got formulas for that)
Step 2: Supervisors manually fill in some extra data (I've deleted those cells to make the uploaded file lighter), including their name in AA9.
(AA2 to AA8 are mostly empty. They use them in case they need to manually edit data in the grey boxes, which contain formulas and they are locked).
Step 3: Once the above steps are done, they print the file using the macro below (it should be visible on the uploaded file, under the name "Αποθήκευση")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\Νέα Τεστ Παπ\" & Range("AH1").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True

So, you don't need to do anything about steps 1 and 2, they are all manual.

You only need to adjust step 3, by having the name from AA9 entered in the appropriate row in column B of Demographics, before Results sheet be printed as a PDF.

Edit: and disable printing if AA9 is empty.

 

[mumps]

Try:

Sub PrintPDF()
    If Sheets("Results").Range("AA9") = "" Then Exit Sub
    Application.ScreenUpdating = False
    Dim ID As Range
    Set ID = Sheets("Demographics").Range("A:A").Find(Sheets("Results").Range("U2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not ID Is Nothing Then
        ID.Offset(, 1) = Sheets("Results").Range("AA9")
    End If
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\??a ?est ?ap\" & Range("AH1").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

 

[Lux Aeterna]

Try:

Sub PrintPDF()
    If Sheets("Results").Range("AA9") = "" Then Exit Sub
    Application.ScreenUpdating = False
    Dim ID As Range
    Set ID = Sheets("Demographics").Range("A:A").Find(Sheets("Results").Range("U2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not ID Is Nothing Then
        ID.Offset(, 1) = Sheets("Results").Range("AA9")
    End If
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="C:\Users\pc50\Desktop\??a ?est ?ap\" & Range("AH1").Value _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

I am afraid it's not working. I put this sub here, right?

I still need to use my macro for saving, right?

 

[mumps]

Place the macro in a regular module, not the worksheet code module, and run it manually when you are ready to put the name from AA9 into the appropriate row in column B of Demographics and print to PDF.