How to Pull Data from Multiple Worksheets in Excel

[Renuga82]

How to pull or combine specific data from specific cell for a example cell V39 from different excel sheets but same number cell V39 to a new excel sheet ? The data from all sheets located in same cell (V39). So, I need to gather the specific data from all the sheets and send to new sheet. Please help me by giving me a easy method other then copy and paste from each sheet. 

[#2 · Accepted solution]

@Renuga82 

Sub text_from_cells()

Dim ws As Worksheet
Dim result As String
Dim i As Long
Dim j As Long

i = Cells(1, 1).Value
j = Cells(1, 2).Value

Sheets("Tabelle1").Cells(i, j).Value = ""

For Each ws In ThisWorkbook.Worksheets

    result = result & " " & ws.Cells(i, j).Value

Next ws

Sheets("Tabelle1").Cells(i, j).Value = Trim(result)

End Sub

You can try this code. In the attached file you can click the button in cell D2 to run the macro. Enter the row number of the cell in A1 and the column number in B1. For example if you want to return the entries of cells B7 enter 7 ( for row 7) in A1 and 2 (for column B) in B1.

[#3]

Thank you for your reply to help me in this task. Please can you help me to know how run this macro? I understand your file that you have attached but i don't know how to run the macro. Hope you can help me thank you.

[#4]

@Renuga82 

You are welcome. After opening the workbook you have to click "enable content" and "enable editing". Then you can click the button in cell D2 to run the macro.