Returning Two Values From Each Worksheet
 

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

Returning Two Values From Each Worksheet
 

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
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
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

Returning Two Values From Each Worksheet
 

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


-----Original Message-----
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
----------------------------------------------------------
----------------------------------------------------
Be advised to back up your WorkBook before attempting to
make changes.
----------------------------------------------------------
----------------------------------------------------
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


.