Extract Data from non adjacent cells and strip out formatting

I'm using this code to extract data from multiple worksheets to put into blank sheet (provided by arlu1201), which works brilliantly

Please Login or Register  to view this content.

Is there any way to extend this to include non adjacent cells. For example, the data I actually want to extract appears in Column A and then in Columns D to F on the same row, so I want to extract the data from each row as follows:

A39, I39:M39
A40, I40:M40
A41, I41:M41

and so on, missing out columns B to H (if possible) to appear on the same row in my destination sheet. At the moment, I'm using A39:M58 to extract all the columns and then deleting those I don't want.

Also, is there a way to strip out any cell formatting at the same time, so the data in the destination sheet is free from colour fills and borders etc, like a 'paste values' effectively?

Many thanks

Um, the macro is running, but not actually adding any data to the summary sheet. It seems to highlight a group of cells as though there is supposed to be some data there, but there is actually nothing!

Do you have data in range 39-58? You have also column A of sheet master with a header, so the code should copy in to the next empty row. It would be easy to spot what is going on if you could attach a sample.

Okay, I've attached a sample. I've taken the original code and added the .pastevalues, to strip out the formatting and that works fine. If you run the Macro you will see what it currently does. What I want to achieve though is the same thing, only without Coulmns B through H being copied over, so effectively I want column A, together with columns I to M. I also don't want any gaps in the output, so the data in the summary sheet should keep Column A and put what are columns I to M in Columns B to F, if that makes sense, so all the data appears in adjacent cells.

Many thanks

Hangman,
Well,see the attached and let me know what is wrong or missing from the code. I have not done anything, just run my code.

Okay, I wanted to include all the columns from I to M inclusive, so when I ran it, changed the range parameters, which didn't work. So to do that I would have to edit it to look like this.

Please Login or Register  to view this content.

Not that it is too much of a problem, but is there a way to specify I to M in a single statement, as I may need to do this for multiple column ranges across the sheets, e.g. I to M, R to U, Z to AC and so on, so it would be neater than having to add each column individually if that makes sense?

Many thanks

Hangman,
Back to square 1. Unless you want to copy non contagions range, you can use your original code.

Please Login or Register  to view this content.

Okay, that works...

Many thanks for your help, very much appreciated...

Sorry,
Replace the union line with this line

Please Login or Register  to view this content.

Perfect, that is working exactly as I wanted it to now...

Many thanks again for your help, greatly appreciated...

Hangman,
You are welcome!