.Rows("1:11").Copy _
Destination:=wsNew.Range("a1")

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("X1:X2"), _
CopyToRange:=wsNew.Range("A12"), _
Unique:=False

wsNew.Range("R:iv").Delete

could become

.Rows("1:11").Copy _
Destination:=wsNew.Range("a1")

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("X1:X2"), _
CopyToRange:=wsNew.Range("A12"), _
Unique:=False

.Columns("A:R").copy
wsnew.range("A1").pastespecial paste:=xlPasteColumnWidths

wsNew.Range("R:iv").Delete

(Untested and uncompiled.)

========
That last line deletes column R from the new worksheet in the new workbook. You
may want to change that.

And I didn't understand the layout of your data. If you have that extra row 2
rows down, then subtracting 2 is what you want to do.

ps. if you're using xl2k, then change xlpastecolumnwidths to 8. It's a bug
that was fixed in xl2002.

Sean wrote:

Thanks Dave

I did a COUNT and LEN in A2861 but it returned 0, so not sure, I
modified as per your suggestion -

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1

But that still left a #n/a in R2861, so I changed it to -

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 2 and it seems to
have worked

There was data in Row 2862 but none in Row 2861, in that instance
would you have expected LastRow = .Cells(.Rows.Count,
"A").End(xlUp).Row to work?

Anyway above is working.

On the formatting, recording the macro etc is fine, but how do I
select the relevant Sheets/File name when I can't reference a specific
File name as it could be 'anything' once the code creates the new
workbook. Below hard codes a copy formats to Sheet7, but next time I
run the code a new Sheet4 might be created?

Columns("A:R").Select
Selection.Copy
Windows("Sheet7").Activate
Columns("A:R").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A1").Select

--

Dave Peterson