LOOPING THROUGH WORKSHEETS!

[jay dean]

Hello -

In each worksheet of the workbook Test.xls, ONE cell is manually
selected.
I need a macro that will loop through all the sheets in the workbook
except the worksheet labeled "Display", and concatenate the value of the
selected cells in the sheets, then place the concatenated string in
Range("D12") of the workshsheet labeled "Display".

Any help would be appreciated.
Thanks
Jay

 

[Per Jessen]

Hi Jay

Try this:

Sub BBB()
Dim MyString As String
For Each sh In ActiveWorkbook.Sheets
If sh.Name <> "Display" Then
MyString = MyString & " " & sh.ActiveCell.Value
End If
Next
Sheets("Display").Range("D12") = MyString
End Sub

Best regards,
Per

 

[Jacob Skaria]

Sub MyMacro()

Application.ScreenUpdating = False
Dim MyString As String
For Each sh In ActiveWorkbook.Sheets
sh.Activate
If sh.Name <> "Display" Then
MyString = MyString & " " & ActiveCell.Value
End If
Next
Sheets("Display").Range("D12") = MyString
Application.ScreenUpdating = True

End Sub

If this post helps click Yes

 

[jay dean]

Hello Per -

I tried it but it gave me the error:
"Object doesn't support this prpoerty or method", then highlighted the
liene "MyString = MyString & " " & sh.ActiveCell.Value" when I clicked
debug.

I think it is not accepting the "ActiveCell" property for the loop?


Thanks
Jay

 

[Jacob Skaria]

'Enhanced..

Sub MyMacro()

Application.ScreenUpdating = False
Dim MyString As String
For Each sh In ActiveWorkbook.Sheets
sh.Activate
If sh.Name <> "Display" Then
MyString = MyString & " " & ActiveCell.Value
End If
Next
Sheets("Display").Activate
Application.ScreenUpdating = True
Sheets("Display").Range("D12") = MyString

End Sub


If this post helps click Yes

 

[Mike H]

Try

Sub Stance()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Display" Then
ws.Select
tempstring = tempstring & ActiveCell.Value
End If
Next
Sheets("Display").Range("D12") = tempstring
End Sub


Mike