ExcelVBA loop exports data from spreadsheet to table within Excel
I'm having difficulty in visualising your data here, a screenshot would be really useful.

In the meantime:



Option Explicit

This should be at the top of every code module you ever create in VBA. Go to Tools --> Options --> Require Variable Declaration and it will automatically insert it for you from now on.

This is important because without it, VBA will interpret any new variable names (including mis-spellings) as entirely new variables, instead of what you intended.

It also forces you to declare your variables. So you must explicitly give them a type (Long, String, Variant etc.) and a scope (Procedure Dim var As, Module Private Var As, Project Public Var As).

This will then automatically catch all sorts of unintended situations (such as accidentally setting a number equal to an object) which would not be caught if VBA has to assume that all your variables are Variant because you never explicitly declared them.



Magic Variables

And not the good kind of magic either. A Magic Variable is any value which is hardcoded. Especially if it's hardcoded in multiple places.


  lr = Cells(Rows.Count, 1).End(xlUp).Row


Why 1? How was that number determined? 


  "Bac Form", "A1", "Q3" etc.


Why those values in particular? If you can't look at a variable and know what it represents, then it has appeared in your code as if by magic. 



If you have to hardcode values, then they should be hardcoded precisely once, into a descriptively named variable.

This:  


  FolderPath = "C:\Users\Joe.Dimaggio\Desktop\PDFs"


is the right way to hardcode a value. It's written once, and I can see FolderPath later on in the code and know exactly what it is.

This:


For x = 2 To lr
    Sheets("Bac Form").Range("A2").Value = _
    Sheets("Q3").Cells(1, 1).Value &amp; Sheets("Q3").Cells(x, 1).Value
    Sheets("Bac Form").Range("A3").Value = _
    Sheets("Q3").Cells(1, 2).Value &amp; Sheets("Q3").Cells(x, 2).Value &amp; _
    " (Third Bacterial Quarter)"
    Sheets("Bac Form").Range("A4").Value = _
    Sheets("Q3").Cells(1, 3).Value &amp; Sheets("Q3").Cells(x, 3).Value
    Sheets("Bac Form").Range("B4").Value = _
    Sheets("Q3").Cells(1, 4).Value &amp; Sheets("Q3").Cells(x, 4).Value
    Sheets("Bac Form").Range("A5").Value = _
    Sheets("Q3").Cells(1, 5).Value &amp; Sheets("Q3").Cells(x, 5).Value
    Sheets("Bac Form").Range("A6").Value = _
    Sheets("Q3").Cells(1, 6).Value &amp; Sheets("Q3").Cells(x, 6).Value
    Sheets("Bac Form").Range("A7").Value = _
    Sheets("Q3").Cells(1, 7).Value &amp; Sheets("Q3").Cells(x, 7).Value
    Sheets("Bac Form").Range("A8").Value = _
    Sheets("Q3").Cells(1, 8).Value &amp; Sheets("Q3").Cells(x, 8).Value
    Sheets("Bac Form").Range("A9").Value = _
    Sheets("Q3").Cells(1, 9).Value &amp; Sheets("Q3").Cells(x, 9).Value
    Sheets("Bac Form").Range("A10").Value = _
    Sheets("Q3").Cells(1, 10).Value &amp; Sheets("Q3").Cells(x, 10).Value



is not. What happens if the name of your sheet changes? Or your data table moves because someone inserted/deleted a column/row? You'll have to go and re-code every single value.



Naming

Variable names should be Concise, Descriptive and, above all, Unambiguous.


  FolderPath


is a good name because it's clear exactly what the variable contains/represents.


  lr


is not. If I encounter that line halfway through your code, I'm going to go all the way back through to remind myself what it's meant to be representing. Just call it lastRow and you avoid all that trouble.

VBA Naming Conventions:

Typical VBA Naming conventions go as follows:

camelCase for local variables.

PascalCase for Module/Global Variables.

SHOUTY_SNAKE_CASE for constants.

Get in the habit of following them.



Bringing it all together

Step 1, put your worksheets in variables:

For x = 2 To lr
    wsBacForm.Range("A2").Value = wsQ3.Cells(1, 1).Value &amp; wsQ3.Cells(x, 1).Value
    wsBacForm.Range("A3").Value = wsQ3.Cells(1, 2).Value &amp; wsQ3.Cells(x, 2).Value &amp; " (Third Bacterial Quarter)"
    wsBacForm.Range("A4").Value = wsQ3.Cells(1, 3).Value &amp; wsQ3.Cells(x, 3).Value
    wsBacForm.Range("B4").Value = wsQ3.Cells(1, 4).Value &amp; wsQ3.Cells(x, 4).Value
    wsBacForm.Range("A5").Value = wsQ3.Cells(1, 5).Value &amp; wsQ3.Cells(x, 5).Value
    wsBacForm.Range("A6").Value = wsQ3.Cells(1, 6).Value &amp; wsQ3.Cells(x, 6).Value
    wsBacForm.Range("A7").Value = wsQ3.Cells(1, 7).Value &amp; wsQ3.Cells(x, 7).Value
    wsBacForm.Range("A8").Value = wsQ3.Cells(1, 8).Value &amp; wsQ3.Cells(x, 8).Value
    wsBacForm.Range("A9").Value = wsQ3.Cells(1, 9).Value &amp; wsQ3.Cells(x, 9).Value
    wsBacForm.Range("A10").Value = wsQ3.Cells(1, 10).Value &amp; wsQ3.Cells(x, 10).Value


And suddenly the structure becomes clear.



Then:

Add options to change the row/column position of your tables.
Loop through rows/columns.
Add separate variables for each sheet to deal with (potentially) separate positions.
Add an index counter to track where to insert extra text.
Put the filename creation on a separate line.
Add proper Range objects.

And now, if you need to change something, or your data tables get bigger, or you need to modify a specific aspect of the execution, you can just change one value and the rest is done for you.

Private Sub CommandButton1_Click()

    Dim fileName As String

    Const FOLDER_PATH As String = "C:\Users\Joe.Dimaggio\Desktop\PDFs"

    Dim wsBacForm As Worksheet, wsQ3 As Worksheet
    Set wsBacForm = Sheets("Bac Form")
    Set wsQ3 = Sheets("Q3")

    Dim q3BaseRow As Long, q3BaseCol As Long '/ Location of the Q3 data table
    q3BaseRow = 1
    q3BaseCol = 1

    Dim bacBaseRow As Long, bacBaseCol As Long '/ Location of the bac output table
    bacBaseRow = 1
    bacBaseCol = 1

    Dim lastRow As Long, lastCol As Long
    lastRow = wsQ3.Cells(Rows.Count, q3BaseCol).End(xlUp).row
    lastCol = wsQ3.Cells(q3BaseRow, Columns.Count).End(xlToLeft).Column

    Dim bacRow As Long, bacCol As Long, q3Row As Long, q3Col As Long
    Dim row As Long, col As Long, counter As Long

    Dim bacOutputCell As Range, q3HeaderCell As Range, q3DataCell As Range
    Dim outputString As String

    For row = q3BaseRow To lastRow
        bacRow = row - q3BaseRow + bacBaseRow
        q3Row = row

        counter = 0
        For col = q3BaseCol To lastCol
            counter = counter + 1
            q3Col = col

            Set bacOutputCell = wsBacForm.Cells(bacRow, bacBaseCol)
            Set q3HeaderCell = wsQ3.Cells(q3BaseRow, q3Col)
            Set q3DataCell = wsQ3.Cells(q3Row, q3Col)

            outputString = q3HeaderCell.Value &amp; q3DataCell.Value

            Select Case counter '/ used a counter so that it is (absolute position of column) agnostic

                Case Is = 2
                outputString = outputString &amp; " (Third Bacterial Quarter)"

                Case Is = 14
                outputString = outputString &amp; " colony/100 ml"

                Case Is = 16
                outputString = outputString &amp; " MPN/100 ml"

            End Select

            bacOutputCell.Value = outputString
        Next col

        fileName = FOLDER_PATH &amp; "\" &amp; wsBacForm.Name &amp; "(" &amp; wsQ3.Name &amp; ")" &amp; (row - 1)
        wsBacForm.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, openafterpublish:=False
    Next row

End Sub