VBA rename Table in Name Manger

hajiali

Well-known Member
Joined
Sep 8, 2018
Messages
623
Office Version
  1. 2016
Platform
  1. Windows
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:

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)"

Code:
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try this


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
  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
 
Upvote 0
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?
 
Upvote 0
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:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top