Help deperately needed! Does anyone know of macro which does the following?
Copy the value from a particular cell in a worksheet, let's say M4, for
every worksheet in that same workbook, and then paste it to a new workbook.
HOWEVER, the cell values from the source workbook should be pasted one cell
consecutively down in the destination workbook.

For example, If cell M4 is $1200 in worksheet"Rangers!" and cell M4 is $900
in worksheet "Cardinal!" and they are in the same workbook, then both of
these values could be pasted in a new workbook, "Sheet1!" in cell B2 = $1200
(from M4 wksheet "Rangers!") and cell B3 = $900 (from M4 wksheet "Cardinal!").

I hope this clarifies the previous post.

Any Help is greatly appreciated!

Hello


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




--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"carla 7" wrote:

Help deperately needed! Does anyone know of macro which does the following?
Copy the value from a particular cell in a worksheet, let's say M4, for
every worksheet in that same workbook, and then paste it to a new workbook.
HOWEVER, the cell values from the source workbook should be pasted one cell
consecutively down in the destination workbook.

For example, If cell M4 is $1200 in worksheet"Rangers!" and cell M4 is $900
in worksheet "Cardinal!" and they are in the same workbook, then both of
these values could be pasted in a new workbook, "Sheet1!" in cell B2 = $1200
(from M4 wksheet "Rangers!") and cell B3 = $900 (from M4 wksheet "Cardinal!").

I hope this clarifies the previous post.

Any Help is greatly appreciated!

I get a syntax error at: wb.Sheets(1).Range("B" &
Rows.Count).End(xlUp).Offset(1).Value =


"carla 7" wrote:

Help deperately needed! Does anyone know of macro which does the following?
Copy the value from a particular cell in a worksheet, let's say M4, for
every worksheet in that same workbook, and then paste it to a new workbook.
HOWEVER, the cell values from the source workbook should be pasted one cell
consecutively down in the destination workbook.

For example, If cell M4 is $1200 in worksheet"Rangers!" and cell M4 is $900
in worksheet "Cardinal!" and they are in the same workbook, then both of
these values could be pasted in a new workbook, "Sheet1!" in cell B2 = $1200
(from M4 wksheet "Rangers!") and cell B3 = $900 (from M4 wksheet "Cardinal!").

I hope this clarifies the previous post.

Any Help is greatly appreciated!