'You should set up each 'chunk' of code as a sub routine, think like method/function in Java (please don't tear
'me apart seasoned devs, trying my best at analogies).  Also if you think you might use this code often, you
'can copy/paste it to a .txt file for later use.  I have a slew of them saved as text files that I frequently
'use for quick cleanup tasks in excel

'I have done something similar to what you are in the past and like to set up my workbook as two sheets
'a data sheet, with all of your rows of data, and a summary or results page.

Sub PDFdataToExcel()

Application.ScreenUpdating = false  'this is just to increase performance

Dim lastRow As Integer
lastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row 'see how it references the data sheet

Dim tableRow As Integer 'this is going to replace your 'x'
tableRow = 2
'headers for your table, you could do this manually but we're already here
Sheets("Results").Cells(1, 1).Value = "Company Name"
Sheets("Results").Cells(1, 2).Value = "Nature of Services"
Sheets("Results").Cells(2, 1).Value = "Details"
Sheets("Results").Cells(2, 1).Value = "Finances"

For i = 1 To lastRow 'I prefer this over "rows.count", gives you a more accurate count and allows
                        'you to manipulate the var if needed

    'Dim x As Integer:: Since you have this inside the For loop, your x resets on each
    'iteration.  So basically you would keep overwriting the first line in your table
    'x = 2

    If Sheets("Data").Cells(i, 1).Font.Bold Then 'remember that the coordinates go (row, column) so 
                                                 'that's why the i is first, since we are going down the sheet
        'I've never used select/copy/paste on this scale and assume it is less efficient so I 
        'have the code directly get and set the values
        'company name
        Sheets("Results").Cells(tableRow, 1).Value = Sheets("Data").Cells(i, 1).Value
        'nature of services
        Sheets("Results").Cells(tableRow, 2).Value = Sheets("Data").Cells(i + 3, 1).Value
        Sheets("Results").Cells(tableRow, 1).Value = Sheets("Data").Cells(i + 4, 1).Value
        Sheets("Results").Cells(tableRow, 1).Value = Sheets("Data").Cells(i + 5, 1).Value

        'Now we need to increase tablerow so on the results tab it doesn't overwrite the first row
        tableRow = tableRow + 1

    'we really don't need an else if.  If the cell isn't bold, it will just skip to the next
    'iteration.  Good practice would be to have and else and blah blah.  That's next lesson haha

    '**ElseIf Cells(i, 1).Value.contains("Nature of Services:") Then Cells (1,i).Select**
    '   Selection.Copy
    '   Cells(11, x).Select
    '   ActiveSheet.Paste
    '   y = x + 1
    '   Cells(1,y).Select
    '   Selection.Copy
    '   Cells(12,x).Select
    '   ActiveSheet.Past
    '   t = x + 2
    '   Cells(1,t).Select
    '   Selection.Copy
    '   Cells(13,x).Select
    '   ActiveSheet.Paste

'It's just good practice to enable it again when you're done.
Application.ScreenUpdating = True

End Sub
/r/PostPreview Thread