Excel Macro: Copy specific cells from multiple sheets to specific cells in separate sheet

Question

I am new to Excel macros but have recently tried them out and find them really useful. The problem is my programming knowledge is limited to Matlab so I'm wondering if someone can suggest a macro for the following problem.

I have collected study data and I want to move it all to one Excel spreadsheet. Each sheet has data for on participant, and there are 67 sheets. The sort of macro which would be useful is one which looks in the same five cells (always D36:D40) of 67 sheets and copies it to a specific part of a row in the new sheet (AKx, ALx, AMx, ANx, AOx where x is the next row down, starting from 3, for every sheet data is copied from).

I feel by code being provided for my specific situation it will be easier for me to understand what the code does and thus I can slowly start to learn some macro code.

Answer 1

You don't need a macro for that. You can just include the file name in the cell referencing.

The formula looks like this:

='file:///C:/[path and filename].xlsx'#$'[sheetname]'.G22

The easiest way to accomplish this is to have both sheets open. In your main sheet, type the "=" and ALT-TAB to the other sheet, click on the cell you wish to reference. Hit return. The value from that cell will appear in your main sheet.

So your main file, can point to those 60 some-odd sheets, and whatever cells you want. Any time you update those files, your main sheet will always be up to date.