Macro Copy Query

[ferol05]

Help Badly needed here! I am trying to find a macro that will do the following:
Copy the value of a cell M6 for all worksheets in a workbook, and paste them to a new workbook but the cell value for each worksheet of the original workbook should be pasted in the next cell down in the pasted workbook.

Help is greatly appreciated.

[slurpee55]

This is basically the code for a macro that you need. In it I had two workbooks, Book1 (containing the data) and Book2 (receiving the data). I had the data placed in column A1, A2 and A3 of Book2. So you will need to place this code in the receiving workbook, and change the names of the books and sheets as needed. If you want it to go into another column, change the "A2" and "A3" references (and add as many others as needed) and, when you run the macro, have your cursor in the cell at the top of the column you want the data in.

Sub Macro1()
    Windows("Book1.xls").Activate
    Range("M6").Select
    Selection.Copy
    Windows("Book2.xls").Activate
    ActiveSheet.Paste
    Range("A2").Select
    Windows("Book1.xls").Activate
    Sheets("Sheet2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book2.xls").Activate
    ActiveSheet.Paste
    Range("A3").Select
    Windows("Book1.xls").Activate
    Sheets("Sheet3").Select
    Range("M6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Book2.xls").Activate
    ActiveSheet.Paste
End Sub

[slurpee55]

Oh, one other thing - you must have both worksheets open at the time you want to run this.