VBA-Range(Selection, Selection.End(xlToLeft))

G

george hart

Board Regular
Joined
Dec 4, 2008
Messages
241
Hi

I have data I need to select then copy to another worksheet. The code below works fine if the data I want is in more than one row. But, when I have data in only one row it falls over because it copies the data I want and all the enty cells/rows below - this causes a problem when pasting, because paste area are not the same size/shape etc...I've tried changing the code in allsorts of ways but cant figure it out...I'm going mad!


Sheets("LAIRA STOP").Select
Cells.Find(What:="power cars", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Data").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Columns("B").Value = "LA"
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
What range exactly do you want to copy?
 
Upvote 0
Not sure how, as i somehow need to select just the data under the ActiveCell.Offset(1, 0).Select - selection.

This can range from - for example, A4:E7 or A4:E4

the data in question however can sometimes be in one row or many rows. so I'm not sure how I would select all the data whether in one row or more.

Hope this makes sense...
 
Upvote 0
Sorry I misphrased that perhaps.

I realise the data is dynamic but how are you determining what the range is?

Is it from the found cell down to the last row of data and from column A to the last column with data in the row of the found cell.

eg if found cell is B5 and the data goes from columns A-G and down to row 213 you would want A5:G213

Is that anywhere near?
 
Upvote 0
I could have one sheet like this:

<TABLE style="WIDTH: 563pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=749 x:str><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 298pt; mso-width-source: userset; mso-width-alt: 14518" width=397><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" span=2 width=92><TBODY><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 563pt; HEIGHT: 14.1pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 2pt double" class=xl30 height=18 width=749 colSpan=6>Power Cars</TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 height=18 width=69>43022</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=49>LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=50>29/05</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=397>F EXAM + COMP MOD + GSMR MOD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>2200 06/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>2200 09/07</TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 height=18 width=69>43142</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=49>LE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=50>07/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=397>F EXAM + GSMR MOD + COMP MOD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>2200 26/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 height=18 width=69>43140</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=49>LE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=50>09/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=397>F EXAM + POWER UNIT CHANGE WAITS P/U + COMP MOD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>2200 28/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl27 height=18 width=69>43023</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl27 width=49>LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl27 width=50>19/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=397>F EXAM - DUE QL4 07/08 + GRP CHANGE + CAB DOOR SEALS</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl27 width=92>2200 31/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl27 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 height=18 width=69>43025</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=49>LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=50>30/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=397>D EXAM + C/GROUP CHANGE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>2200 05/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>2200 07/07</TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 height=18 width=69>43086</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=49>OC</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=50>03/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=397 x:str="G EXAM + FUEL TANK AUDIT ">G EXAM + FUEL TANK AUDIT </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92>ERNG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=92> </TD></TR></TBODY></TABLE>

And another like this:

<TABLE style="WIDTH: 580pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=772 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14116" width=386><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 580pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 2pt double" class=xl33 height=19 width=772 colSpan=6>POWER CARS</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 height=18 width=71>43031</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=84>LA13</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=51>05/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29>20800 HR (1K) MTU EXAM + B EXAM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=86>ERNG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=94> </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 height=18 width=71> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=84> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=51> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=86> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=94> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 height=20 width=71> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 width=84> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=51> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=86> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=94> </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD></TR></TBODY></TABLE>
I need to select and copy all the data under where it reads Power cars into another worksheet
 
Upvote 0
So where could Power Cars be?

Is it always in the first column?
 
Upvote 0
Thank you for your patience

I use the find function to establish where the power car is. Although it's always going to be in column A something

My current code is this and works fine with:

Sheets("LANDORE").Select
Cells.Find(What:="power cars", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Data").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.Columns("B").Value = "LE"

Power Cars is in cell A15
<TABLE style="WIDTH: 563pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=749 x:str><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 298pt; mso-width-source: userset; mso-width-alt: 14518" width=397><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" span=2 width=92><TBODY><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 563pt; HEIGHT: 14.1pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 2pt double" class=xl25 height=18 width=749 colSpan=6>Power Cars</TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 height=18 width=69>43022</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=49>LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=50>29/05</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32 width=397>F EXAM + COMP MOD + GSMR MOD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>2200 06/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>2200 09/07</TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 height=18 width=69>43142</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=49>LE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=50>07/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32 width=397>F EXAM + GSMR MOD + COMP MOD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>2200 26/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 height=18 width=69>43140</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=49>LE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=50>09/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32 width=397>F EXAM + POWER UNIT CHANGE WAITS P/U + COMP MOD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>2200 28/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 height=18 width=69>43023</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 width=49>LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 width=50>19/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=397>F EXAM - DUE QL4 07/08 + GRP CHANGE + CAB DOOR SEALS</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 width=92>2200 31/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl30 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 height=18 width=69>43025</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=49>LA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=50>30/06</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=397>D EXAM + C/GROUP CHANGE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>2200 05/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>2200 07/07</TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 height=18 width=69>43086</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=49>OC</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=50>03/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32 width=397 x:str="G EXAM + FUEL TANK AUDIT ">G EXAM + FUEL TANK AUDIT </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92>ERNG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92> </TD></TR><TR style="HEIGHT: 14.1pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 52pt; HEIGHT: 14.1pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 height=18 width=69> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 37pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=49> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=50> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 298pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32 width=397> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 69pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=92> </TD></TR></TBODY></TABLE>

The code falls over when I have a sheet like this

Power Cars is in cell A3
<TABLE style="WIDTH: 580pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=772 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14116" width=386><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 580pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 2pt double" class=xl25 height=19 width=772 colSpan=6>POWER CARS</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 height=18 width=71>43031</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=84>LA13</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=51>05/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32>20800 HR (1K) MTU EXAM + B EXAM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=86>ERNG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=94> </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 height=18 width=71> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=84> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=51> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=86> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl28 width=94> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl33 height=20 width=71> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl33 width=84> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl34 width=51> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl35> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=86> </TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=94> </TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30>





</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30 height=17></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl30></TD></TR></TBODY></TABLE>
 
Upvote 0
I think this should work:

Code: 
ActiveCell.CurrentRegion.Offset(1, 0).Copy

You don't need any of the End statements.
 
Upvote 0
Unfortunately it doesn't, as it copies everything that above Power Cars


<TABLE style="WIDTH: 580pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=772 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14116" width=386><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 53pt; HEIGHT: 18.75pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: #d4d0c8" class=xl27 height=25 width=71> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 63pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: #d4d0c8" class=xl28 width=84> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 38pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: #d4d0c8" class=xl28 width=51> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 290pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl29 width=386>ST PHILIPS MARSH STOP LIST</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 65pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: #d4d0c8" class=xl28 width=86> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 71pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl30 width=94> </TD></TR><TR style="HEIGHT: 37.5pt; mso-height-source: userset" height=50><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 37.5pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl31 height=50 width=71>VEH No SET No</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=84>DEPOT SET</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=51>DATE ON</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 290pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=386>EXAM/REPAIRS</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=86>TIME & DATE EXPECT OK</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: windowtext 2pt double" class=xl31 width=94>TIME & DATE UPDATE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: silver; WIDTH: 580pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 2pt double; BORDER-RIGHT: black 2pt double" class=xl33 height=19 width=772 colSpan=6>POWER CARS</TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 2pt double; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 height=18 width=71>43031</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=84>LA13</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl26 width=51>05/07</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl32>20800 HR (1K) MTU EXAM + B EXAM</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 65pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=86>ERNG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 2pt double" class=xl25 width=94> </TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
macro for copying values to the next blank cell
Replies
5
Views
453
Micron
M
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
187
Peter_SSs
Peter_SSs
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
205
MikeVol
MikeVol
D
  • Solved
VBA If statement to continue
2
Replies
17
Views
651
dinunan
D
R
  • Solved
Retype Macro to run properly
2
Replies
11
Views
138
Alex Blakenburg
A

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top