Excel data from same cells in different worksheets

[canadian86]

Hi,

I have 3 sheets called:

Sheet1
Sheet2
Sheet3

In all 3 sheets, I have a mailing address in cells:

A1
A2
A3
A4

I want to extract cells A1-4 from each sheet and put it ALL in one sheet. Essentially, I am trying to compile all the addresses in one sheet.

Any ideas how I can do that?

Thanks!

 

[VoG]

Maybe like this

Sub GetData()
Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3"))
    ws.Range("A1:A4").Copy Destination:=Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next ws
End Sub

 

[canadian86]

Is there any way to do with with VB? I have all the sheet names and am looking for an output that looks like this:


-- removed inline image ---
<table style="width: 439px; height: 128px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:7204;width:148pt" width="197"> <col style="mso-width-source:userset;mso-width-alt:1682; width:35pt" span="3" width="46"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl234" style="height:15.0pt;width:148pt" height="20" width="197">
</td> <td class="xl234" style="border-left:none;width:35pt" width="46">Sheet1</td> <td class="xl234" style="border-left:none;width:35pt" width="46">Sheet2</td> <td class="xl234" style="border-left:none;width:35pt" width="46">Sheet3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl234" style="height:15.0pt;border-top:none" height="20">Address Line 1 (located in cell A1)</td> <td class="xl234" style="border-top:none;border-left:none">
</td> <td class="xl234" style="border-top:none;border-left:none">
</td> <td class="xl234" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl234" style="height:15.0pt;border-top:none" height="20">Address Line 2 (located in cell A2)</td> <td class="xl234" style="border-top:none;border-left:none">
</td> <td class="xl234" style="border-top:none;border-left:none">
</td> <td class="xl234" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl234" style="height:15.0pt;border-top:none" height="20">Address Line 3 (located in cell A3)</td> <td class="xl234" style="border-top:none;border-left:none">
</td> <td class="xl234" style="border-top:none;border-left:none">
</td> <td class="xl234" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>

-- removed inline image ---

 

[canadian86]

I was thinking if there's a way to do it using the INDIRECT function? I can't seem to make it work.. but if I could have the cell look into a sheet name that is located in a certain cell, and pull data from a specific cell in that sheet.