Macro Copy Workbook Query

C

carla 7

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!
 
W

Wigi

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
 
C

carla 7

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top