Open Multi Files and Copy and Paste Special

I have code to open multiple file and paste the data below once another

1) I cannot get the section of code to work to paste the data as values
2) I also want to find "Total" in Col D and clear data 10 rows down and two columns to the left of "Total" and one col to the right of total

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have attached sample source data (I have valued the formula's , except the sum formulas)

Your assistance is most appreciated

Hi Dave

I added this code below

Please Login or Register  to view this content.

I get a run time error "wrong number or invalid property assignment

I have attached sample source data


Kindly test & amend

Try
But not tested
at least the method is ok

Please Login or Register  to view this content.

BTW:

Please Login or Register  to view this content.

When Open a workbook then thisworkbook refer to the newly oppend book! Am I right?

Thanks Sintek

I now get subscript out of range and code below is highlighted

Please Login or Register  to view this content.

Thanks for the help, Mohadin-code works perfectly


With regard to point 2 in Post #1, I need to find "Total" in Col D and clear data 10 rows down and two columns to the left of "Total" and one col to the right of total


It would be appreciated if you can provide me with the code to do this

Hi Sintek & Dave

My apologies. I had the incorrect sheet name on my live data. It was referring to the source data sheet and not the active workbook sheet

I just picked this up just now

Please Login or Register  to view this content.

Many thanks for your input and help

I have tried to write code to find Total and clear the data to the right and left of it and 10 rows below it, but nothing happens


I have highlighted the cells in yellow that must be cleared


It would be appreciated if you could kindly amend my code

Many Thanks Sintek


I mentioned 10 rows below just as an example in case there is perhaps some data up to 10 rows below

Great
And thank you
Sorry I forget about Point 2
Be happy

Originally Posted by Howardc1001

Thanks for the help, Mohadin-code works perfectly


With regard to point 2 in Post #1, I need to find "Total" in Col D and clear data 10 rows down and two columns to the left of "Total" and one col to the right of total


It would be appreciated if you can provide me with the code to do this

Great
And thank you
Sorry I forget about Point 2
Be happy