Hi Tim

Form my own experience, I would normally use a macro to "clean" the copied data.

I have helped a colleague who used to take hours to "clean" the data on a weekly basis. It was error-prone and not productive.

The macro only took a few seconds to do the job.

If you need to do this frequently, it would be worth the while to consider using a macro.

Sunny

Pls show us the macro ? Tks!

Hi David

Unfortunately I am unable to show you the macro as it contains sensitive information.

It' OK Sunny.. we understand that..

Thanks, Sunny.

Could you say, generally speaking, what does the macro include? I have to do this with PDFs from various sources, so obviously one macro wouldn't fit every situation. Is it merely sorting rows (or columns) and moving data from one cell to another, or does it include something more sophisticated that would make me say "Wow! That's good!" (Many answers do!)

Hi Tim

You are right about having many macros as the data is different for each situation. For most people, completing their job in only a few seconds (instead of hours) is guaranteed to create a WOW effect. 

Most of the time the macro is not fantastic (I have to hard-code many lines) but it does contain some custom functions and get the job done.

Generally the macro does the following (not necessary in the following order):

• Delete all unwanted rows (page number,headers,address etc)
• Add custom headers
• Extract some data from the middle of a specific row after a certain text
• Concatenate text
• Break some rows into different columns and formatting them
• Adding grand totals to the output
• Generate a Pivot Table (if required)

Sunny
  

Thanks for this Sunny.

No magic answers here, but at least it tells me I've not been wasting too much of my time already. (I'd hoped there might have been some useful UDFs you were aware of.)

KR

TimC