Macro Workbook Copy Query

[carla 7]

Help deperately needed...I am trying to create a macro that will do the
following:
Copy the value of cell M4 for each worksheet in a particular workbook and
paste it to a new workbook, BUT the cells should be pasted one cell down
consecutivley in th the destination workbook.

Can anyone help me this?

Thanks

 

[Wigi]

Sub simpleloop()

Const sCell As String = "$M$4"

Dim ws As Worksheet
Dim wb As Workbook

Set wb = Application.Workbooks.Add

For Each ws In ThisWorkbook.Worksheets

wb.Sheets(1).Range("B" & Rows.Count).End(xlUp).Offset(1).Value =
ws.Range(sCell).Value

Next

End Sub

 

[carla 7]

Macro did not work....I have a workbook with many worksheets. However, each
worksheet has is formatted the same. Cell M4 contains a value (2007 revenue
figures) for a company, each company is respectively placed in a worksheet.
The first company is GM, so cell M4 contains GMs 2007 gross revenue amount.
The second company is Crysler, so cell M4 contains Crysler's 2007 gross
revenue amount. There are over 100 companies.

What I want is a macro that will copy the value from cell M4 in each
worksheet from that workbook which contains revenue detail, and paste them to
a new workbook, BUT the values should be entered one cell down in a
consecutive manner in that new (destination) workbook.

Sorry I was not clearer earlier. I appreciate the feedback.