Copy, Paste, Tidy macro help

[falcon5nz]

G'day.
A while ago JBeaucaire provided me with code for a macro that copied data from one sheet, pasted it into another sheet, deleted empty and duplicate cells and sorted the results. I tweaked the code to get it to work in my work book and ended up with the following code:

Please Login or Register  to view this content.

Can anyone tell me how to make the code paste the first result into cell A2 so I can have a title in cell A1. I'm sure it is very easy, I just don't know how to do it.

Cheers

[falcon5nz]

Ideas anyone?

[D_Rennie]

Im not the best with this stuff though it seams that you have a heading of "list" in cell A1

[falcon5nz]

That gets deleted in the clean up section

[D_Rennie]

if you take Rows("1:1").Delete out does it leave the heading like you want.

[falcon5nz]

No. Ideally I want to have the headings:
Head-stamp in A1
Manufacturer in B1
Country of Origin in C1
With the list of headstamps in Column A etc
I have attached a quick book with what I have (Sheet:Headstamp Table) and what I need (Headstamp ID)

[JBeaucaire]

Try this, it will clear the existing Headstamp ID sheet and reinsert a new, unduplicated, sorted set of values in column A. You'll have to add the column B and C stuff since none of that exists anywhere in the original data.

Please Login or Register  to view this content.

[JBeaucaire]

Actually, now that I thought about it, I realized you might want to KEEP the existing Manufacturer and Country entries each time you rerun the sheet to create a new listing of Headstamps. Yes?

If so, use this version. It will backup the existing Headstamp ID sheet, create a new one with new a new list of IDs and then fit the old B & C values back into the sheet from the backup sheet.

Please Login or Register  to view this content.

[falcon5nz]

Thank you JBeaucaire. That second code is EXACTLY what I want, BUT (theres always a but) it is picking up the info from column A. How do I change it so it only collects the cells from column B across?

Cheers for all your help guys

Nick

[JBeaucaire]

This line of code:

Please Login or Register  to view this content.

...means start in column "1" and go to the last column (LC), so just change the 1 to a 2.

[falcon5nz]

Brilliant. Guys you have no idea how thankful I am.