Need to copy blocks of data from sheet 1 to sheet 2, but not in the same order

[zookeepertx]

Hello!

I have a PDF file that I'm trying to work with in Excel. We only have access to Adobe Reader, not Acrobat, so that's a problem. Anyway, I figured out how to get the data all into Column A of a spreadsheet, then do a text-to-columns, using space as a delimiter. I can't see any other way to break up the data; there are no commas, semi-colons or anything. Naturally, this plays the devil with the formatting! So, the only thing I can think to do, is to have a macro that will move the data to a second sheet, in the correct order, so that I can work with it further.

Here's what it looks like before doing text-to-columns:

06/30/14 Page 1 of 9

P. O. Box 123456

Los Angeles, CA 90051-5176

Phone (562)345-2200 Fax (562)741-2500

B90 - Smith Monthly Roster, East Coast

Trailering Team

Roster of Trailer Manifests

Manifest #

Load #

Created

Operator

Consolidator

Consignee

Trailer

LH Carrier

Loaded

Closed

Departed

Arrived

Ctns Pcs Lbs PU$ Cons$ LH$ FSC Total

Inv #

Spot

1241103 John Smith

7795

5800 02/19/14 06:17 PM

5/26/2014 22:56

5/26/2014 23:59

FLY

02/19/14 575 0 9121 310.20 2,736.30 837.51

cartre John Smith- Direct

E0163828 415.52 111.79

34561546 John Smith

8854

7200 02/19/14 06:17 PM

5/26/2014 22:20

5/26/2014 23:30

GROUND

02/19/14 363 0 5142 253.1 3,456.23 951.26

cartre John Smith- Direct

E0163242 215.11 213.56

<TBODY>
</TBODY>

Here's what it looks like AFTER the text-to-columns. (I've color coded it to help me see whe:

6/30/2014	Page	1	of	9
P.	O.	Box	123456
Los	Angeles,	CA	90051-5176
Phone	(XXX)XXX-XXXX	Fax	(XXX)XXX-XXXX
B90	-	Smith	Monthly	Roster,	East	Coast
Trailering	Team
Roster	of	Trailer	Manifests
Manifest	#
Load	#
Created
Operator
Consolidator
Consignee
Trailer
LH	Carrier
Loaded
Closed
Departed
Arrived
Ctns	Pcs	Lbs	PU$	Cons$	LH$	FSC	Total
Inv	#
Spot
1241103	John	Smith
7795
5800	2/19/2014	6:17	PM
5/26/2014 0:00	10:56:00	PM
5/26/2014 0:00	11:59:00	PM
FLY
2/19/2014	575	0	9121	310.2	2,736.30	837.51
cartre	John	Smith-	Direct
E0163828	415.52	111.79
34561546	John	Smith
8854
7200	2/19/2014	6:17	PM
5/26/2014 0:00	10:20:00	PM
5/26/2014 0:00	11:30:00	PM
GROUND
2/19/2014	363	0	5142	253.1	3,456.23	951.26
cartre	John	Smith-	Direct
E016242	215.32	213.56

<TBODY>
</TBODY>

And here's what I need it to look like:

6/30/2014

Page 1 of 9

TraileringTeam

P.O. Box 123456

LosAngeles, CA 90051-5176

Phone(XXX)XXX-XXXX Fax (XXX)XXX-XXXX

Rosterof Trailer Manifests

B90-Smith Monthly Roster, East Coast

Manifest #

Created

Consolidator

Inv #

Trailer

Loaded

Departed

Ctns

Pcs

Lbs

PU$

Cons$

LH$

FSC

Total

Load #

Operator

Consignee

Consolidator

LH Carrier

Closed

Arrived

1241103

2/19/2014

John Smith

E0163828

5800

2/19/2014 6:17:00 PM

5/26/2014 12:00:00 AM 11:59:00 PM

575

0

9121

415.52

310.2

2,736.30

111.79

837.51

7795

cartre

John Smith- Direct

FLY

5/26/2014 12:00:00 AM 10:56:00 PM

34561546

2/19/2014

John Smith

E016242

7200

2/19/2014 6:17:00 PM

5/27/2014 12:00:00 AM 11:30:00 PM

363

0

5142

215.32

253.1

3,456.23

213.56

951.26

8854

cartre

John Smith- Direct

GROUND

5/27/2014 12:00:00 AM 10:20:00 PM

<TBODY>
</TBODY>

In the 2 purple cells that have 12:00:00, I have NO idea where that part came from! It doesn't appear on the pre-text-to-columns sheet!

The block of data are all 9 rows long, all formatted exactly the same. I've written a macro to move the top section and the data headers to sheet 2 and to move the first block of data to sheet 2. But the report is 1,600 or so lines long and I'm thinking there's GOT to be a way to move all of the blocks to sheet 2, in sets of 2 rows.

Here's the code I have so far (I know it's really messy, so, if you can see any way to tweak it, I'd be grateful):

Sub Roster()
'Roster Macro-JD 07012014
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
Sheets(2).Range("A1").Value = Sheets(1).Range("A1").Value
Sheets(2).Range("O1").Value = Range("Sheet1!B1") & " " & Range("Sheet1!C1") _
    & " " & Range("Sheet1!D1") & " " & Range("Sheet1!E1")
Sheets(2).Range("H2").Value = Range("Sheet1!A6") & Range("Sheet1!B6")
Sheets(2).Range("H3").Value = Range("Sheet1!A2") & Range("Sheet1!B2") _
    & " " & Range("Sheet1!C2") & " " & Range("Sheet1!D2")
Sheets(2).Range("H4").Value = Range("Sheet1!A3") & Range("Sheet1!B3") _
    & " " & Range("Sheet1!C3") & " " & Range("Sheet1!D3")
Sheets(2).Range("H5").Value = Range("Sheet1!A4") & " " & Range("Sheet1!B4") _
    & " " & Range("Sheet1!C4") & " " & Range("Sheet1!D4")
Sheets(2).Range("H6").Value = Range("Sheet1!A7") & " " & Range("Sheet1!B7") _
    & " " & Range("Sheet1!C7") & " " & Range("Sheet1!D7")
Sheets(2).Range("H7").Value = Range("Sheet1!A5") & Range("Sheet1!B5") _
    & " " & Range("Sheet1!C5") & " " & Range("Sheet1!D5") & " " & _
    Range("Sheet1!E5") & " " & Range("Sheet1!F5") & " " & Range("Sheet1!G5")
With Range("H2:H7")
    .HorizontalAlignment = xlCenter
End With
Sheets(2).Range("A9").Value = Range("Sheet1!A8") & " " & Range("Sheet1!B8")
Sheets(2).Range("A10").Value = Range("Sheet1!A9") & " " & Range("Sheet1!B9")
Sheets(2).Range("B9").Value = Range("Sheet1!A10")
Sheets(2).Range("B10").Value = Range("Sheet1!A11")
Sheets(2).Range("C9").Value = Range("Sheet1!A12")
Sheets(2).Range("C10").Value = Range("Sheet1!A13")
Sheets(2).Range("D9").Value = Range("Sheet1!A21") & " " & Range("Sheet1!B21")
Sheets(2).Range("D10").Value = Range("Sheet1!A22")
Sheets(2).Range("E9").Value = Range("Sheet1!A14")
Sheets(2).Range("E10").Value = Range("Sheet1!A15") & " " & Range("Sheet1!B15")
Sheets(2).Range("F9").Value = Range("Sheet1!A16")
Sheets(2).Range("F10").Value = Range("Sheet1!A17")
Sheets(2).Range("G9").Value = Range("Sheet1!A18")
Sheets(2).Range("G10").Value = Range("Sheet1!A19")
Range("Sheet1!A20:H20").Copy
    Sheets(2).Select
    Range("H9:O9").PasteSpecial
Sheets(2).Range("A12").Formula = "=(Sheet1!A23)"
Sheets(2).Range("A13").Formula = "=(Sheet1!A24)"
Sheets(2).Range("B12").Formula = "=(Sheet1!A29)"
Sheets(2).Range("B13").Formula = "=(Sheet1!A30)"
Sheets(2).Range("C12").Formula = "=(Sheet1!B23&Sheet1!C23)"
Sheets(2).Range("C13").Formula = "=(Sheet1!B30&Sheet1!C30&Sheet1!D30)"
Sheets(2).Range("D12").Formula = "=(Sheet1!A31)"
Sheets(2).Range("E12").Formula = "=(Sheet1!A28)"
Sheets(2).Range("E13").Formula = "=(Sheet1!A30)"
Sheets(2).Range("F12").Formula = "=(Sheet1!B25&Sheet1!C25&Sheet1!D25)"
Sheets(2).Range("F13").Formula = "=(Sheet1!A26&Sheet1!B26&Sheet1!C26)"
Sheets(2).Range("G12").Formula = "=(Sheet1!A27&Sheet1!B27&Sheet1!C27)"
Sheets(2).Range("H12").Formula = "=(Sheet1!B29)"
Sheets(2).Range("I12").Formula = "=(Sheet1!C29)"
Sheets(2).Range("J12").Formula = "=(Sheet1!D29)"
Sheets(2).Range("K12").Formula = "=(Sheet1!B31)"
Sheets(2).Range("L12").Formula = "=(Sheet1!E29)"
Sheets(2).Range("M12").Formula = "=(Sheet1!F29)"
Sheets(2).Range("N12").Formula = "=(Sheet1!C31)"
Sheets(2).Range("O12").Formula = "=(Sheet1!G29)"
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With
End Sub

I would be so happy for any help! I started on this yesterday morning and I'm not making any more progress. I guess I could give up, but now it's WAR, LOL!

Sorry for the long post, but I wanted to get all the info in here.

I posted this on another site yesterday, but they haven't been able to help yet, so I'm hoping you guys will see something they didn't. Trying to copy blocks of data from sheet 1 to sheet 2, but not in the same order

Thank you!!
Jenny