Returning Two Values From Each Worksheet

[CalumMurdo Kennedy]

Hi,

I have an unspecified number of worksheets (as the User will be adding to
them at regular intervals) and I need to cycle through them all and take two
cell values from each one and paste those values in a hidden worksheet
called "Combined". The two values I need are in D6 and D8 on each worksheet
and I need to create a summary on the "Combined" sheet (just in a long
list).

Does anyone have any ideas on how to acheive this? Any help is greatly
appreciated.

p.s. does anyone know if it is possible to set the BCC email address using
the xl send mail dialog?

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk

 

[pfsardella]

Sub DoWhat()

Dim intI As Integer
Dim wSht As Worksheet
Dim wShtC As Worksheet

Set wShtC = Worksheets("Combined")
wShtC.Cells.ClearContents
intI = 2

With wShtC
.Cells(1, 1).Value = "Sheet Name"
.Cells(1, 2).Value = "First Range"
.Cells(1, 3).Value = "Second Range"
End With

For Each wSht In ActiveWorkbook.Worksheets
If wSht.Name <> "Combined" Then
With wShtC
.Cells(intI, 1).Value = wSht.Name
.Cells(intI, 2).Value = wSht.Range("D6")
.Cells(intI, 3).Value = wSht.Range("D8")
End With
intI = intI + 1
End If
Next wSht

End Sub

HTH
Paul

 

[CalumMurdo Kennedy]

Hi Paul,

Thanks for that, it works an absolute treat and is very
very fast! I now have some hope of keeping my sanity!

Best Regards,

CalumMurdo Kennedy
www.taekwondo.freeserve.co.uk