VBA rename Table in Name Manger

[hajiali]

Hello All,

I have a Macro that duplicates a sheet and unprotected it and copies and paste value from on cell to another also that Sorts it. using the following code:

Sub importdata_Unprotect_DAILYBIDsheet()response = MsgBox("HAS ALL EMPLOYEES BEEN ASIGNED A BID LINE?", vbYesNo)
If response = vbNo Then
    MsgBox "Please Complete the Bid Tab Before attemping to Import", vbOKOnly
    Exit Sub
End If
    MsgBox "Importing data will take a few moments we will notify you once completed", vbOKOnly
    Sheets("Daily Bid").Select
    Sheets("Daily Bid").Copy After:=Worksheets(Worksheets.Count)
    Sheets("Daily Bid (2)").Select
    Sheets("Daily Bid (2)").Unprotect "PaperPushers"
    Sheets("Daily Bid (2)").Tab.ColorIndex = 45
    Call PasteSpecial_ValuesOnly
[COLOR=#ff0000][B]    Call sortampmtable
[/B][/COLOR]Sheets("Daily Bid (2)").Name = "DAILY BID UPDATE" 
    MsgBox "Importing is completed", vbOKOnly
End Sub

all works except the CALL Sortampmtable sorting code as below gives me an error due to the new sheet renames the Tables and adds a number. table in new Duplicate sheet goes from "_AMBid" to "_AMBid + (number)"

Sub sortampmtable()
Workbook.Worksheets("Daily Bid (2)").ListObjects("_AMBid").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Daily Bid (2)").ListObjects("_AMBid").Sort.SortFields. _
        Add Key:=Range("_AMBid[[#All],[Line]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Daily Bid (2)").ListObjects("_AMBid").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=99
    ActiveWorkbook.Worksheets("Daily Bid (2)").ListObjects("_PMBid").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("Daily Bid (2)").ListObjects("_PMBid").Sort.SortFields. _
        Add Key:=Range("_PMBid[[#All],[Line]]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Daily Bid (2)").ListObjects("_PMBid").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

is there a way I can add a code to first code to rename the table to something specific.

 

[DanteAmor]

Try this

Sub importdata_Unprotect_DAILYBIDsheet()
  response = MsgBox("HAS ALL EMPLOYEES BEEN ASIGNED A BID LINE?", vbYesNo)
  If response = vbNo Then
      MsgBox "Please Complete the Bid Tab Before attemping to Import", vbOKOnly
      Exit Sub
  End If
  MsgBox "Importing data will take a few moments we will notify you once completed", vbOKOnly
  Sheets("Daily Bid").Select
  Sheets("Daily Bid").Copy After:=Worksheets(Worksheets.Count)
  Sheets("Daily Bid (2)").Select
  Sheets("Daily Bid (2)").Unprotect "PaperPushers"
  Sheets("Daily Bid (2)").Tab.ColorIndex = 45
  Call PasteSpecial_ValuesOnly
  Call sortampmtable
  Sheets("Daily Bid (2)").Name = "DAILY BID UPDATE"
  MsgBox "Importing is completed", vbOKOnly
End Sub


Sub sortampmtable()
  Dim obj As ListObject
[COLOR=#0000ff]  For Each obj In Worksheets("Daily Bid (2)").ListObjects[/COLOR]
    With obj.Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("" & obj.Name & "[[#All],[Line]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
    End With
[COLOR=#0000ff]  Next[/COLOR]
End Sub

 

[hajiali]

Thank you so Much that worked perfect.

 

[hajiali]

One last part of this is to Rename whatever the new table name will be for "_AMBid" in the Daily Bid Update sheet to Always rename it "_AMBID1" Because this is reference to in other sheets?

 

[DanteAmor]

One last part of this is to Rename whatever the new table name will be for "_AMBid" in the Daily Bid Update sheet to Always rename it "_AMBID1" Because this is reference to in other sheets?

Try the next shorter macro:

Sub sortampmtable()
  Dim obj As ListObject
  For Each obj In Worksheets("Daily Bid (2)").ListObjects
    obj.Name = Left(obj.Name, 6) & "1"
    obj.Sort.SortFields.Clear
    obj.Sort.SortFields.Add Key:=Range("" & obj.Name & "[[#All],[Line]]"), Order:=xlAscending
    obj.Sort.Header = xlYes
    obj.Sort.Apply
  Next
End Sub

 

[hajiali]

THANK YOU SO MUCH DanteAmor !!!!!!

 

[DanteAmor]

THANK YOU SO MUCH DanteAmor !!!!!!

I'm glad to help you. Thanks for the feedback.