Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through selected sheet tabs
I am trying to write a program to go through just the
selected sheets in a workbook and then loop through the selected cells in each sheet. Sub Replace2() Dim c As Object, sht As Worksheet For Each sht In 'Is there a way to get an array of 'the sheets that are selected. For Each c In sht.Selection 'I thought this is how 'you get the range of 'selected cells. Debug.Print c Next c Next sht End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through selected sheet tabs
Gary -
For Each sht in ActiveWorkbook.Worksheets If you are going to work on the selected range, you need to activate each sheet first. Do you want the same range on every sheet? Then do something like this on the first sheet: Dim sRng as string Dim c as range If Typename(Selection) = "Range" then sRng = selection.address End If ' in the loop... For each c in sht.Range(sRng) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Gary Adamson wrote: I am trying to write a program to go through just the selected sheets in a workbook and then loop through the selected cells in each sheet. Sub Replace2() Dim c As Object, sht As Worksheet For Each sht In 'Is there a way to get an array of 'the sheets that are selected. For Each c In sht.Selection 'I thought this is how 'you get the range of 'selected cells. Debug.Print c Next c Next sht End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through selected sheet tabs
Gary
If you want to "hard code" your sheet selection then you can use Dim Sh As Worksheet For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) 'your code here Next Alternatively, if you want to use the currently selected sheets then you can use Dim Sh As Worksheet For Each Sh In ActiveWindow.SelectedSheets 'your code here Next Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through selected sheet tabs
Assuming you have previously selected ranges in each of your sheets, try:
Dim Sh As Worksheet dim c as Range For Each Sh In ActiveWindow.SelectedSheets For each c in Selection 'your code Next c Next Sh " wrote: Gary If you want to "hard code" your sheet selection then you can use Dim Sh As Worksheet For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) 'your code here Next Alternatively, if you want to use the currently selected sheets then you can use Dim Sh As Worksheet For Each Sh In ActiveWindow.SelectedSheets 'your code here Next Peter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through selected sheet tabs
If you do not change the active sheet, the selection remains whatever is selected on
the real active sheet. I ran these one-line loops in the debug window, with a two worksheet workbook. Without activating each sheet, the selected range is unchanged: for each ws in worksheets : debug.Print selection.address(external:=true) : next [Book1]Sheet1!$A$1:$C$3 [Book1]Sheet1!$A$1:$C$3 activating the sheet causes its selected area to become the selection: for each ws in worksheets : ws.activate : debug.Print selection.address(external:=true) : next [Book1]Sheet1!$A$1:$C$3 [Book1]Sheet2!$D$4:$F$6 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ gocush wrote: Assuming you have previously selected ranges in each of your sheets, try: Dim Sh As Worksheet dim c as Range For Each Sh In ActiveWindow.SelectedSheets For each c in Selection 'your code Next c Next Sh " wrote: Gary If you want to "hard code" your sheet selection then you can use Dim Sh As Worksheet For Each Sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3")) 'your code here Next Alternatively, if you want to use the currently selected sheets then you can use Dim Sh As Worksheet For Each Sh In ActiveWindow.SelectedSheets 'your code here Next Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
Printing Selected Sheet Tabs | Excel Discussion (Misc queries) | |||
Email selected tabs of spreadsheet? | Excel Discussion (Misc queries) | |||
how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet. | Excel Worksheet Functions | |||
Hide selected worksheet tabs | Excel Programming |