Select Multiple Active Sheets (not by name)

MisterT · Jul 16th 2004

Hi,

I am trying to create a clean generic macro to copy selected sheets to a new workbook and then paste those sheets as values.

I have done this for the active sheet, but cannot seem to find the terminology to copy all of the selected worksheets (grouped) without using the worksheet names (making it workbook specific).

Any suggestions very gratefully received

Code

Sub DTPRider()


    ActiveSheet.Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Worksheets(1).Activate
    Range("B1").Select
End Sub

Display More

Thanks in advance,

Mike

Anonymous · Jul 16th 2004

Try the following. It assumes the sheets have been selected and you want them to be copied to a new workbook.
[vba]
Sub TryThis()
Dim w As Worksheet
ActiveWindow.SelectedSheets.Copy
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub
[/vba]

MisterT · Jul 20th 2004

Thanks very much, Derk.

That is absolutely perfect.

Select Multiple Active Sheets (not by name)

Participate now!

Share