Copy specific cell in various workbooks to another workbook, but in consequtive cells

Hi All
I have recorded a macro that gets data from 1 cell in INV001.xlsx (Excel 2013), and copies it to cell J5 in Summary.xlsm.This works, So I copied it and edited the copied part to get the same cell in INV002.xlsx and copy it to cell J6 in Summary.xlsm. It works.
My problem is: I never know how many INV***.xlsx files there will be, and if I, in my primitive way, want to provide for 60 INV***.xlsx files, the macro simply gets too long. I have a macro that lists all my INV***.xlsx files which it gets from the correct folder. I know how to code the FOR...NEXT procedure to loop thru the macro as many times as there are INV***.xlsx files, but I do not know how to reference the cells to which the data must be copied to (J5, J6, J7, etc) in a format similar to .cells(X.Y) (which I think my solution might be)

Any suggestions?

To get the next cell in the J Column;

Range("J" & rows.count).End(3)(2)

John,
Many Thanx. I see the logic in it, but as a novice VBA fiddler, can you briefly explain the (3)(2) at the end of the statement?

Andre

You're welcome Andre. The (3) is a short-cut I use. It is a constant which means the same as (xlUp). The (2) is another way (older) to use the offset command. If you try typing the code and you as enter the "(" you will see the choices you have. Used together they will select the next unused cell.

John

Many thanx for your input. I have actually got this part in my head. And now I can see how powerful this VBA code can be. I look forward to playing around with it a lot more, only thanx to guys like you that are able AND willing to help newbies like me. I really appreciate it.

You must have yourself a great day!

Greetings from South Africa!

André

Andre

Glad I could help out. Thanks for the feedback. Yes VBA is a very powerful tool. And excel is much more complex than I first thought. It seems the more I learn, the more I realize there still more to learn. You have a great day too.

John,
Good point!
I have got this quote from this site. I think Steve from Scandinavian region.

"Be Happy if you sometimes test the limit of excel, because more often than not you will find your own limits tested by excel"

AB33

Thanks for sharing that quote with us. It couldn't be said better.

John

may I ask one quick question? My procedure is now working, except for one think. I need to loop it until a counter I have set as LONG equals the number of files in another spreadsheet. In this other spreadsheet, The number of files are stored in C2, and I have been trying the following:
DIM INVCNT as LONG (INVCNT is where I want to store the number of invoices)
The spreadsheet containing C2 is open at this point. How do I store the value of C2 in INVCNT?

Regards

André

INVCNT = Sheets("Other spreadsheet").Range("C2").VALUE

John

Thanks. It Works. Brilliant!

André

You're welcome again Andre. Glad it works for you.