Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
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 "jay dean" skrev i meddelelsen ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
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. -- gmorris ------------------------------------------------------------------------ gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87724 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
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 --------------- Jacob Skaria "jay dean" wrote: 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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
'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 --------------- Jacob Skaria "jay dean" wrote: 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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
LOOPING THROUGH WORKSHEETS!
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 "jay dean" wrote: 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping through worksheets | Excel Programming | |||
Looping through worksheets | Excel Programming | |||
Worksheets looping. | Excel Programming | |||
Looping through Worksheets | Excel Programming | |||
looping through worksheets | Excel Programming |