Copy single cell value from multiple worksheets

MSPECS77 said:

I'm not sure if there is a thread relating to this but the ones I have come across did not help, but I'm sure that's to do with my lack of knowledge on VBA!!
I want help on creating a macro that will copy one cell value from a set of worksheets to a summary worksheet. I'm using MS Excel 2003.
The cell value in each worksheet = O8
The worksheets range from '200501' to '201009' or indexed from 25 to 93
The summary worksheet is called 'RAW DATA'
I need the data to paste into cell C3 downwards! and also need it to recognize if the cell above is empty.
Alternative: If there us a formula that can do the above and I can use the autofill function then please let me know of one.
Thanks

Click to expand...

Sub MSPECS77()



   Dim X As Integer
   
   For X = 200501 To 201009

    Sheets(X).Range("O8").Copy Sheets("RAW DATA").Cells(Rows.Count, "C").End(xlUp)(2)
      

   Next X


End Sub

MSPECS77 said:

Hi John, thnx for the reply and yes I did make a start on it. I had to change the For X = 200501 TO 201009 to For X = 25 TO 93 as it did not recognize the 200501 as an integer. Then I get stuck because I need it to do a paste special for values as it was carrying over the formula of the cells. How would you include that in the statement?

Click to expand...

MSPECS77 said:

Hi John,

I'm afraid I get a 1004 run-time error. Any other ideas?

Click to expand...