Forum Discussion

doomiloom1's avatar
doomiloom1
Copper Contributor
Apr 28, 2019

mark the whole next column when i press enter (using macro?)

hi everyone

i have a file that i'm working on everyday 

it has about 40 columns, and everyday i have to give unique 'table' names to every column, and everyday the names are different so i cant use anything automatic sadly.

 

so right now, every morning i load the file, mark column A, give it a unique 'name', press 'enter', use the mouse again to go and mark the next column B, use the mouse again to go to the table name area, give it a name, enter... etc.... etc..

 

as u can see it takes long, and for no reason.

 

i was wondering if theres a way to create some Macro that ill enable it only when im using this file, and every time ill press "enter" it'll automatically mark the next column (a-b-c-d) and go back to the table name area - and then all ill have left to do is just type the next name and press enter everytime ... 

when im done - i disable the macro.

 

 

please????????

sorry for my english

 

 

thanks.

adi.

excel
Macros and VBA

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 29, 2019

    doomiloom1 Perhaps this little macro helps?

    Option Explicit

Sub RenameAllTables()
    Dim sNewName As String
    Dim oLo As ListObject
    For Each oLo In ActiveSheet.ListObjects
        Application.Goto oLo.Range.Cells(1, 1)
        sNewName = ""
        sNewName = InputBox("Please enter the new name for this table", "New table name", oLo.Name)
        If Len(sNewName) > 0 Then
            oLo.Name = sNewName
        End If
    Next
End Sub
    • doomiloom1's avatar
      doomiloom1
      Copper Contributor
      Apr 29, 2019

      JKPieterse 

      thanks for the response

       

      tried it

      it doesn't seem to do anything ?   .. ?

      i enabled marcos and everything. 

      still nothing really happens when i "run" it

       

       

      my main goal is when i press "enter" - excel will automatically mark the next column, for example - B:B and will go to the "table name" area, then ill put the name and press "enter" again and everything keeps going, until i decide to just stop the macro

       

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Apr 30, 2019
        The code assumes you already have tables and just have to rename them, that is what I understood form your question. If not, Why are you creating a table for columns which are next to each other? that is poor design and can lead to confusion. I always have at least one empty column between two tables.

Resources