macro for summary sheet

[superik84]

Hi!

I have 250 sheets with info as given below and would like it to be summarized in a one sheet. I would like this to look like:
TOTAAL
MV BASIS/ MV KORT/ mv zomer/ MV VAK/
11/ A/ 84,00/ 63,00/ -/ -/
etc etc etc

(where '/' is end of entry)

The next sheet has info concerning (busline) 11b and so on..

Hope someone can help me!


kind regards,

Erik

This info is of (busline) 11a and is in fixed cells on each sheet. Numbers are in column C and D, starting with C4 - D4.

thanks!

11
a RITTEN DRU's
GGD Stad
totaal MV basis 84,00 40,33
MV kort 63,00 30,42
MV zomer - -
MV vak - -
Z basis+kort 63,00 27,92
Z zomer 46,00 20,67
Z&F dagen 34,00 14,73

0 - 6 uur
MV basis 2,00 0,87
MV kort 2,00 0,87
MV zomer
MV vak
Z basis+kort 1,00 0,43
Z zomer 1,00 0,43
Z&F dagen 1,00 0,43

6 - 9 uur
MV basis 15,00 7,20
MV kort 12,00 5,65
MV zomer
MV vak
Z basis+kort 7,00 2,97
Z zomer 7,00 2,97
Z&F dagen 3,00 1,30
9-12 uur
MV basis 18,00 8,45
MV kort 12,00 6,00
MV zomer
MV vak
Z basis+kort 12,00 5,27
Z zomer 9,00 4,15
Z&F dagen 6,00 2,60

12 - 15 uur
MV basis 18,00 8,85
MV kort 12,00 6,00
MV zomer
MV vak
Z basis+kort 17,00 7,67
Z zomer 9,00 4,20
Z&F dagen 6,00 2,60

15 - 18 uur
MV basis 18,00 9,00
MV kort 12,00 6,00
MV zomer
MV vak
Z basis+kort 14,00 6,38
Z zomer 8,00 3,72
Z&F dagen 6,00 2,60
18 - 24 uur
MV basis 13,00 5,97
MV kort 13,00 5,90
MV zomer
MV vak
Z basis+kort 12,00 5,20
Z zomer 12,00 5,20
Z&F dagen 12,00 5,20


thanks,

Erik

 

[superik84]

thanks to CK1999 I got this macro so far:

counter = 2
vcol = 1
Dim sh As Worksheet
For Each sh In Worksheets
Sheets("summary").Range("A1").Name
Sheets("summary").Cells(counter, vcol) = sh.Range("A1").Value
Sheets("summary").Cells(counter, vcol + 1) = sh.Range("A2").Value
counter = counter + 2
vcol = vcol + 1
Next sh

However, it gives an error on the first row.
Sheets("summary").Range("A1").Name

what am I doing wrong?

Really hope someone can help me!

E

 

[PHV]

it gives an error
Any chance you could post the error message ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[superik84]

error 438

not supported by this object.

thanks,

E

 

[PHV]

What happen if you comment this line out ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[superik84]

OK,

that works in some way. However, it now posts my info in a diagonal line down. It starts in EF10, FG12, GH14, and so on.

I changed counter into +1 and removed Vcol = vcol +1

now it starts in row AB, would like it to do so in row BC but don't see how to do so.. and what if I want to skip the D-row? and put the next information in the E-row?

An other problem is that it starts 6 rows down instead of 4.

regards,

E

 

[superik84]

ok, only problem remaining is that some information is given, without me asking for it.

the macro looks like this at the moment:

Sub Macro1()
'
' Macro1 summary

counter = 1
vcol = 1
Dim sh As Worksheet
For Each sh In Worksheets

Sheets("summary").Cells(counter, vcol + 1) = sh.Range("A1").Value
Sheets("summary").Cells(counter, vcol + 2) = sh.Range("A2").Value
Sheets("summary").Cells(counter, vcol + 3) = sh.Range("C4").Value
Sheets("summary").Cells(counter, vcol + 4) = sh.Range("C5").Value
Sheets("summary").Cells(counter, vcol + 5) = sh.Range("C6").Value
Sheets("summary").Cells(counter, vcol + 6) = sh.Range("C7").Value
Sheets("summary").Cells(counter, vcol + 7) = sh.Range("C8").Value
Sheets("summary").Cells(counter, vcol + 8) = sh.Range("C9").Value
Sheets("summary").Cells(counter, vcol + 9) = sh.Range("C10").Value

counter = counter + 1

Next sh


End Sub




However,

for some reason it gives information in D-J1, B-F2, and D-J3! why? and how do I remove this?

thanks!

E

 

[ck1999]

Superik84,

Please go back to the other thread and use the last code I posted for using the formula references and not the values since you want cell references.
Also this code skips the summary sheet so you do not get data from this.

Whatever row you want your data to start in make counter = to that row number

you want data to start in row 3 then counter = 3 if you want data to start in row 4 the counter = 4

The reason I think you are seeing some blank rows is because the data you are gathering is blank in some of your workbooks. You are only currently referencing cells A1,A2,C4,C5,C6,C7,C8,C9,C10. are these cells filled in in all the worksheets.

This code does the following.

Goes into each worksheet in your workbook (>200 if I remember) and then for each worksheet and references these sheets' cells in the summary worksheet.

If any of these cells are blank then you will get blank on the summary sheet.

ck1999