dcsimg
Results 1 to 8 of 8

Thread: Rename Active Sheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    4

    Rename Active Sheet

    I have a report I need to run a macro on in excel but it has a new name every day. Is there a way to have this code simply select the active sheet (for the renaming). Alternately, we could do away with the entire renaming process if there's a way to obtain the unique records from column "V", as in the code.

    Sub PareCustodians()
    '
    ' PareCustodians Macro
    '

    '
    Sheets("19-07-18 RID Stocklist").Select
    Sheets("19-07-18 RID Stocklist").Name = "Stocklist"
    ActiveWindow.LargeScroll ToRight:=1
    Columns("V:V").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Stocklist").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    Range("A1:A416").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
    "B1"), Unique:=True
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A416" _
    ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:A416")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWorkbook.Save
    End Sub

  2. #2
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,304

    Re: Rename Active Sheet

    Something like this?


    Set xlwbook = xl.Workbooks.Open("C:\TestIt.xlsx")
    Set xlsheet = xlwbook.Sheets.Item(1)
    Set xlWorksheet = xl.Sheets(1)
    Please remember next time...elections matter!

  3. #3
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,992

    Re: Rename Active Sheet

    Tyson is right with number, here a sample to copy from one sheet to another

    Code:
    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
            Dim Path As String = "E:\a9.xlsx" 'your File
            Dim Excel As ApplicationClass = New ApplicationClass
            Dim WorkBook As Workbook = Excel.Workbooks.Open(Path)
            Dim WorkSheets As Sheets = WorkBook.Sheets
    
            'Sheet to copy from
            Dim WorkSheet As Worksheet = CType(WorkSheets(1), Microsoft.Office.Interop.Excel.Worksheet)
            'sheet to copy to
            Dim WorkSheet2 As Worksheet = CType(WorkSheets(2), Microsoft.Office.Interop.Excel.Worksheet)
            'your ranges
            WorkSheet.Range("V1:V500").Copy(WorkSheet2.Range("V1:V500"))
            WorkSheet.Range("A6:B10").Copy(WorkSheet2.Range("C1:D5"))
    
            WorkBook.Save()
            Excel.Application.Quit()
        End Sub
    hth
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    4

    Re: Rename Active Sheet

    Quote Originally Posted by TysonLPrice View Post
    Something like this?


    Set xlwbook = xl.Workbooks.Open("C:\TestIt.xlsx")
    Set xlsheet = xlwbook.Sheets.Item(1)
    Set xlWorksheet = xl.Sheets(1)
    I don't know where this applies in the code I gave. The workbook is already open in my example. Does your first line open a workbook of a particular name? What I'm trying to do is automate an operation on a daily report that has a different name every day. I need this to be generic to whatever name the sheet or workbook may have. The data will be of the same format in every report though.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,098

    Re: Rename Active Sheet

    Welcome to VBForums

    I get the impression that you are working entirely in the VB Editor inside Excel (which is VBA), rather than in VB.Net (which is what the replies above are based on), so I have moved this thread from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Rename Active Sheet

    to answer the original question
    Code:
    activesheet.name = "123"
    but it would be better to rewrite most of the code to avoid using select or active anything, work with fully qualified ranges
    like
    Code:
    Set newsht = Sheets.Add(After:=ActiveSheet)
    newsht.Name = "mynew sheet name"
    Sheets("Stocklist").Range("v:v").Copy newsht
    to replace
    Columns("V:V").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Stocklist").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Columns("A:A").Select
    ActiveSheet.Paste

    we could do away with the entire renaming process if there's a way to obtain the unique records from column "V",
    yes, i am sure that would also be simple enough if i understood what you want to do, there is probably several options for this
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    4

    Re: Rename Active Sheet

    Thank you. I'll check it out and let you know soon.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jul 2019
    Posts
    4

    Re: Rename Active Sheet

    Ok, this is my first foray into looking into the inner workings of macros. It works to rename the sheet. This is really only part and parcel of a larger project I'm working on when things are slow. I'll try the suggestion you made too. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width