Copy and paste same cell from a number of sheets into a new sheet

[Oliver Burdekin]

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/tutorial...29-8f0336e05a0a/treeview-explorer--dialo.aspx

 

[Per Jessen]

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
news:[email protected]...

 

[Gord Dibben]

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

 

[Ryan H]

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