INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Activate a dynamically named workbook

Activate a dynamically named workbook

(OP)
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?

CODE

'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 

RE: Activate a dynamically named workbook

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

RE: Activate a dynamically named workbook

Hi,

CODE

'
    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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Activate a dynamically named workbook

(OP)
Thanks Skip

It worked like a dream

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close