Returning Two Values From Each Worksheet

  • Thread starter Thread starter CalumMurdo Kennedy
  • Start date Start date
C

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
 
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
 
Back
Top