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

*** Sent via Developersdex http://www.developersdex.com ***

 

[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

 

[gmorris]

Something like this should at least get you started:

Sub ConcatCellContents()

Dim Sh As Excel.Worksheet
Dim TheString As String

For Each Sh In Excel.Worksheets
If Sh.Name <> "Display" Then
Sh.Activate
TheString = TheString + ActiveWindow.Selection
End If
Next

Range("Display!D12").Formula = TheString

End Sub
--- Automerged consecutive post before response ---
Wow, we must have posted just about the same time on this! I guess
either solution will work, as there are dozens of ways to do things like
this.

 

[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





*** Sent via Developersdex http://www.developersdex.com ***

 

[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