Copying varying numbers of cells from a number of spreadsheets into a Master spreadsheet

[Graeme M]

I'm trying to pull test fault data from a number of Test spreadsheets into a Master spreadsheet. The starting location for the data is the same (J4) in each of the test sheets and is stored in a single column but the amount of data varies (but is known and stored at H3). eg Test QA5 has 3 entries that I want to copy over and Test QA6 has 13 entries. In each of the TEST QA files, the number of entries I need copied to the Master is stored at H3 and the entries are from J4 to Jx (where x depends on the number of entries). If it helps, the remaining data in column J is blank. Example below is Test QA6. Many thanks.

 

[DanteAmor]

Hi and welcome to MrExcel.

If in the master file, in cell H3 you have the number 2, then you want 2 entries from Q5 and 2 entries from Q6, something like this:

Libro3
	A							H	I	J	K	L
1
2
3								2
4										1	<-- from Test QA5
5										2	<-- from Test QA5
6										1	<-- from Test QA6
7										1	<-- from Test QA6
8
Master

Do you want the solution with VBA?

 

[Graeme M]

Thank you DanteAmor, I found a rather inelegant way of doing it, on the basis that any cells below those of interest were blank - see my Master s/s below. I'm not familiar with VBA but if it offers a better solution then I guess I need to get familiar with it!