Copy cell from multiple sheets, into one row in one sheet

[karldou]

Hi,

As part of a small project i am looking to copy the contents of cell B25 from every sheet in the workbook. and then paste into row A in Sheet1.

I have created a macro that will copy B25 from Sheet2 then paste into row A in Sheet1, and then look at Sheet3. The problem is, the number sheets in the workbook will vary from month to month, and the sheet names will also change.

Worksheets("sheet2").Range("a4").Copy
Worksheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False

Any help will be greatly appreciated!

[Mavyak]

Sub get_b25()
Dim w As Worksheet
Dim wTarget As Worksheet

Set wTarget = Me.Worksheets("Sheet1")

For Each w In Me.Worksheets
If w.Name <> wTarget.Name Then
w.Range("B25").Copy Destination:=wTarget.Range("A65536").End(xlUp).Offset(1, 0)
End If
Next w
Set w = Nothing
Set wTarget = Nothing
End Sub


That goes in the ThisWorkbook module.

Enjoy!

[karldou]

Thats brilliant Mavyak! it works a treat. I think it was probably one of your other posts that helped me get to where i was originally.

Thanks once again!

[mdmackillop]

That goes in the ThisWorkbook module.
I would normally put such code in a standard module. ThisWorkbook would usually comprise Event macros.

[karldou]

Hi Mavyak, I have been looking more closely at the outputs of the script.
I have noticed that there are many duplicates being displayed.

Basically, the data from cell B25 from Sheet 2 is appearing 4 times in the output column, Sheet 3's contents is appearing 3 times, Sheet 4 5 times etc etc.
I can't understand how it is getting the data multiple times and in such a random order! Is there anything that could be added to make sure it only performs the task on a sheet once, and then move on to the next sheet?

Many thanks for you help
Karl

[mdmackillop]

I don't see what would cause that. Have you tried stepping through the code? Have you any Worksheet Events running which could interfere with execution?