Copy Single Cell value from Multiple Sheets to one Sheet

[Sukhdev Singh]

Dear All,
I have around 100 sheet from which I wish to take only single cell data in sheet1 and all 100 sheet are in same format. Please guide how can I do it.

I Need D2 columns data from all the sheet to column E in Sheet 1.

Thanks,
Best Rgds,
Sukhdev Singh

 

[Luke M]

Try running this macro. Change names/range addresses as needed.

Sub MergeData()
Dim ws As Worksheet
Dim destWS As Worksheet

'What sheet are we copying to?
Set destWS = Worksheets("Sheet1")

Application.ScreenUpdating = False
'Loop through all worksheets
With destWS
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> .Name Then
            'Copy cell's value from D2 to col E
            .Cells(.Rows.Count, "E").End(xlUp).Offset(1).Value = ws.Range("D2").Value
        End If
    Next ws
End With
Application.ScreenUpdating = True
           
End Sub

 

[Sukhdev Singh]

Hi Luke,
Thanks for providing this great help. Its working perfectly and this is the things which I needed.
Thanks once again!
Best Rgds,
Sukhdev Singh