compiling data from worksheets

[Tom F]

Suppose there is a worksheet as depicted below.


Directions: Place an x in the cell to indicate Mark x for yes. Leave blank for no.

x 1. Age 10 - 20

2. Age 21 – 30

3. Age 31 – 40

x 4. male

5. female


The worksheets are saved as files in a folder.
How would you compile the data and print the results similar to the one below?



Directions: Place an x in the cell to indicate Mark x for yes. Leave blank for no.

12 1. Age 10 - 20

6 2. Age 21 – 30

4 3. Age 31 – 40

10 4. male

12 5. female

22 Total worksheets


Many thanks,
Tom F

 

[DonkeyOte]

Tom - I am not too hot on code for cycling through files in folders but I have seen people using similar code - so use the Search Function for that.

The approach I would take would be to have a loop that opens the files one by one and then checks the cells in the open file for x's and if an x is found it adds 1 to the value in your "consolidating" file (say MASTER.xls) - if the files are not identical that's ok - you would just need to amend the code where it's pasting the additional 1 to the value etc

So say

B1 = 1. Age 10 - 20
B2 = 2. Age 21 – 30
B3 = 3. Age 31 – 40
B4 = 4. male
B5 = 5. female

and obviously A1,A2,A3 = the count.

Sub COUNTX()

'here you want to do the loop through the files in the folder - so one file is now open and is active (that's important)...

IF Range("B1") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("a1") + 1
Range("a1") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF

IF Range("B2") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A2") + 1
Range("A2") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF

IF Range("B3") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A3") + 1
Range("A3") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF


IF Range("B4") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A4") + 1
Range("A4") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF


IF Range("B5") = "x" then Windows("MASTER.xls").activate
NEWTOTAL = Sheets("sheet1").range("A5") + 1
Range("A5") = NEWTOTAL
Windows("...YOUR OPEN FILE IN THE LOOP.xls").ACTIVATE
END IF

'then you want to close the file from the loop and then loop to the next file...

End Sub

_________________
LASW10
This message was edited by lasw10 on 2002-10-25 08:18

 

[Tom F]

There is a lot to work on and now there is a place to start.

THANKS !!!