Cycle through tabs (with changing names) to copy text data

[Babbabooie]

I have a spreadsheet that has text data in a cell in the same location on several tabs. It a comments section from a survey. The tabs will have a different name (be renamed) each time the spreadsheet is used.

I need to be able to do a summary of the single cell text data on one page. (summarize the text comments that are in the cell on each tab)

THANKS!!

[TMS]

Something like (untested):

Sub SummariseData()
Dim ws As Worksheet
Dim lNR As Long
For Each ws In Worksheets
    If ws.Name <> "Summary" Then
        With ws
            lNR = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
            .Range("C5").Copy = Sheets("Summary").Range("A" & lNR)
        End With
    End If
Next 'ws
End Sub

Regards, TMS

[Babbabooie]

Trying to create a page that summarizes comments for a survey.

Trying to post the Question first then go to all of the tabs and copy the appropriate cell for the comments and paste them underneath the question. Having an issue with the "comm" variable....

Any help is appreciated! Thanks!


Sub SummarizeComments()

Dim ws As Worksheet
Dim i As Integer
Dim det As String
Dim lNR As Long
Dim ltrID(4) As String
Dim Question As String
Dim comm As Long



ltrID(1) = "Q1"
ltrID(2) = "Q2"
ltrID(3) = "Q3"
ltrID(4) = "Q4"


For i = 1 To UBound(ltrID)
det = ltrID(i)

Select Case det
Case "Q1"
Question = "Your work place is free of conflicts."
comm = j3
Case "Q2"
Question = "Your work place is free of harassment."
comm = J4
Case "Q3"
Question = "Your work place is free of discrimination."
comm = J5
Case "Q4"
Question = "Your work place is free of favoritism."
comm = J6

End Select


For Each ws In Worksheets
If ws.Name <> "Comments" Then
With ws
lNR = Sheets("Comments").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets(ws.Name).Activate
Range(comm).Select
Sheets("Comments").Activate.Range ("A" & lNR)
ActiveSheet.Paste
Application.CutCopyMode = False
End With
End If
Next ws

Next i

End Sub

[Norie]

Looks like comm should be a string and you should be assigning it values like "J4", "J5" etc.

[TMS]

Isn't this follow up a completely different question? Should be a new thread.

And I agree with Norie.

Regards, TMS