Activate a dynamically named workbook

[EliseFreedman]

Hi There

I have successfully used the following code to filter a report based on filters that the user will select at runtime. The filtered range will be pasted to a new workbook. I would like to make it more userfriendly by automatically making the new workbook the active workbook. The problem is that I don't know what the new workbook will be called (it just opens as book 7, book 8 etc. How can I make the newly created workbook the active one?

 'Copy/paste the visible data to the new worksheet
        My_Range.Parent.AutoFilter.Range.Copy
        With WSNew.Range("A1")
            ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
            ' Remove this line if you use Excel 97
            .PasteSpecial Paste:=8
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
            .Select
        End With

        ' If you want to delete the rows that you copy, also use this
        ' With My_Range.Parent.AutoFilter.Range
        '     On Error Resume Next
        '     Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
              '               .SpecialCells(xlCellTypeVisible)
        '     On Error GoTo 0
        '     If Not rng Is Nothing Then rng.EntireRow.Delete
        ' End With

    End If

    'Close AutoFilter
    My_Range.Parent.AutoFilterMode = False

    'Restore ScreenUpdating, Calculation, EnableEvents, ....
    My_Range.Parent.Select
    ActiveWindow.View = ViewMode
    If Not WSNew Is Nothing Then WSNew.Select
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With

With WSNew.Range("A1")
           WSNew.Move

End With

 

[simian336]

This will list all the sheet names

For i = 1 To Sheets.Count
Debug.Print Sheets(i).Name
Next i

This will give you the latest sheet name (assuming it was created on the far right tab).

Debug.Print Sheets(Sheets.Count).Name

Simian

 

[SkipVought]

Hi,

'
    Dim wbNew As Workbook, wsNew As Worksheet
'add new workbook
    Set wbNew = Workbooks.Add

    Set wsNew = wbNew.Worksheets(1)
    wsNew.Name = "The New Sheet Name"
    
'Copy/paste the visible data to the new worksheet
    My_Range.Parent.AutoFilter.Range.Copy
    
    With wsNew.Range("A1")
        ' Paste:=8 will copy the columnwidth in Excel 2000 and higher
        ' Remove this line if you use Excel 97
        .PasteSpecial Paste:=8
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        wbNew.Activate
        wsNew.Activate
        .Select
    End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[EliseFreedman]

Thanks Skip

It worked like a dream