Hi there,

I have a number of worksheets that all have the same layout. I would like to be able to extract or copy the same cell from every worksheet in the workbook and create a new worksheet containing the data. Is possible to write a macro for this?

For example, if cell B3 on every worksheet has customer DOB in it and I have 24 worksheets, I would like to extract B3 from all worksheets and create a new worksheet containing the data. So the new worksheet would have 24 DOB's in it and nothing else.

Any help appreciated,

Olly


Submitted via EggHeadCafe - Software Developer Portal of Choice
Treeview Explorer / Dialog for SmartPhone
http://www.eggheadcafe.com/tutorials...er--dialo.aspx

Hi

Look at this:

Sub CopyDOB()
Dim newSh As Worksheet
Dim DestCell As Range
Set newSh = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
Set DestCell = newSh.Range("A2")
For Each sh In ThisWorkbook.Worksheets
If sh.Name < newSh.Name Then
DestCell = sh.Range("B3").Value
Set DestCell = DestCell.Offset(1, 0)
End If
Next
End Sub

Regards,
Per

"Oliver Burdekin" skrev i meddelelsen
...
Hi there,

I have a number of worksheets that all have the same layout. I would like
to be able to extract or copy the same cell from every worksheet in the
workbook and create a new worksheet containing the data. Is possible to
write a macro for this?

For example, if cell B3 on every worksheet has customer DOB in it and I
have 24 worksheets, I would like to extract B3 from all worksheets and
create a new worksheet containing the data. So the new worksheet would
have 24 DOB's in it and nothing else.

Any help appreciated,

Olly


Submitted via EggHeadCafe - Software Developer Portal of Choice
Treeview Explorer / Dialog for SmartPhone
http://www.eggheadcafe.com/tutorials...er--dialo.aspx

Sub Summary_Sheet()
Dim WS As Worksheet
Worksheets.Add.Name = "Summary"
For i = 2 To Worksheets.Count
Set WS = Worksheets(i)
Cells(i - 1, 1).Value = WS.Range("B3").Value
'Cells(i - 1, 2).Value = WS.Name
Next i
End Sub



Gord Dibben MS Excel MVP

On Fri, 08 Jan 2010 09:34:39 -0800, Oliver Burdekin wrote:

Hi there,

I have a number of worksheets that all have the same layout. I would like to be able to extract or copy the same cell from every worksheet in the workbook and create a new worksheet containing the data. Is possible to write a macro for this?

For example, if cell B3 on every worksheet has customer DOB in it and I have 24 worksheets, I would like to extract B3 from all worksheets and create a new worksheet containing the data. So the new worksheet would have 24 DOB's in it and nothing else.

Any help appreciated,

Olly


Submitted via EggHeadCafe - Software Developer Portal of Choice
Treeview Explorer / Dialog for SmartPhone
http://www.eggheadcafe.com/tutorials...er--dialo.aspx

This code will get the value of B3 on each of your worksheets in the workbook
and put them into a new worksheet starting with A1. Hope this helps! If so,
let me know, click "YES" below.

Sub GetData()

Dim lngWks As Long
Dim i As Long

' get number of worksheets
lngWks = Worksheets.Count

' add a new worksheet
Worksheets.Add After:=Sheets(lngWks)

' add worksheet values to new worksheet
For i = 1 To lngWks
ActiveSheet.Cells(i, "A").Value = Sheets(i).Range("B3").Value
Next i

End Sub
--
Cheers,
Ryan


"Oliver Burdekin" wrote:

Hi there,

I have a number of worksheets that all have the same layout. I would like to be able to extract or copy the same cell from every worksheet in the workbook and create a new worksheet containing the data. Is possible to write a macro for this?

For example, if cell B3 on every worksheet has customer DOB in it and I have 24 worksheets, I would like to extract B3 from all worksheets and create a new worksheet containing the data. So the new worksheet would have 24 DOB's in it and nothing else.

Any help appreciated,

Olly


Submitted via EggHeadCafe - Software Developer Portal of Choice
Treeview Explorer / Dialog for SmartPhone
http://www.eggheadcafe.com/tutorials...er--dialo.aspx
.