Effective way to copy data

Hi, Good morning,

If it is possible, I'm looking to do a code that will do the following steps :

-Open a Workbook (data)
-Search for the year 2013 in all the cells
-Go two rows above it and two columns to the left and select the range to the lowest row and last column
-Paste value in my other workbook
-Repeat (loop) for all the worksheets of the data workbook

I need your ideas concerning the two steps in bold. I know you can do the following concerning the value "2013" :

Please Login or Register  to view this content.

However, I have no idea how to select and copy the whole area. The thing is that it is not a defined area and it can moved through time and change except for the years.

Thank you

It will appear multiple times but I may use another value to make it more convenient.

Thank you Norie. Your answers are always fast and to the point.

Sounds interesting.

Does it reduce the size of the code and the speed of execution ?

Ok, I might take a look into it if my first idea on how to build this code fail.

Thank you Norie.

Okay, I need some backup help. I tried to work with xlDown. However, I need to repeat end(xldown).end(xldown).end(xldown).... to get to the end of my data because of the blanks. So, instead, I'm trying to make a count to the bottom row and use it to define my range to copy. But, I just can't seem to define the right syntax.

Please Login or Register  to view this content.

My problems are from the two code in bold font.