Looping thru the active selection

[Guest]

How can I loop through all the selected sheets in an Excel
workbook. I have a partial macro called Duplicate. Also I
have already created the form frmReplace and the function
ReplaceText. I want to be able to copy all the selected
sheets to just after the last sheet in the active
selection and rename them according to the ReplaceText
criteria.


Sub Duplicate()
Dim sht As Object
frmReplace.Show

**Somehow get the active selected sheets and
**copy to just after the end of the last selected sheet.
ActiveSheets.Copy After:= **no of the last selected sheet
For Each sht In **select the sheets that were just copied
sht.Name = ReplaceText(sht.Name, frmReplace.tbFind,
frmReplace.tbReplace)
Next sht
End Sub

 

[Dick Kusleika]

You can use the SelectedSheets property of the Windows collection object.

Sub Duplicate()
Dim sht As Object
frmReplace.Show

**Somehow get the active selected sheets and
**copy to just after the end of the last selected sheet.
ActiveSheets.Copy After:= **no of the last selected sheet
For Each sht In **select the sheets that were just copied
sht.Name = ReplaceText(sht.Name, frmReplace.tbFind,
frmReplace.tbReplace)
Next sht
End Sub

I'm not sure how this will work. You show the userform in this sub. If the
userform's ShowModal is True, then the rest of sub won't execute until the
uf is closed and frmReplace.tbFind won't be available at that time. If
ShowModal is False, the sub will continue to execute but the user won't have
enough time to complete the textboxes.

I think this sub should just have one line

frmReplace.Show

and you should have a commandbutton on the form with this in its Click event

Private Sub CommandButton1_Click()

Dim sht As Object
Dim sSelShts As Sheets

Set sSelShts = ActiveWorkbook.Windows(1).SelectedSheets

For Each sht In sSelShts
sht.Copy after:=sSelShts(sSelShts.Count)
ActiveSheet.Name = ReplaceText(sht.Name, Me.tbFind.Text,
Me.tbReplace.Text)
Next sht

Unload Me

End Sub

 

[Bob Phillips]

Something like

Dim sh As Worksheet
Dim lastSh as worksheet

For Each sh In ActiveWindow.SelectedSheets
Set lastSh = sh
Next sh

For Each sh In ActiveWindow.SelectedSheets
sh.copy after:=Worksheets(lastSh.name)
Next sh


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)