Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-493655%22%20slang%3D%22en-US%22%3Emark%20the%20whole%20next%20column%20when%20i%20press%20enter%20(using%20macro%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-493655%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%20everyone%3C%2FP%3E%3CP%3Ei%20have%20a%20file%20that%20i'm%20working%20on%20everyday%26nbsp%3B%3C%2FP%3E%3CP%3Eit%20has%20about%2040%20columns%2C%20and%20everyday%20i%20have%20to%20give%20unique%20'table'%20names%20to%20every%20column%2C%20and%20everyday%20the%20names%20are%20different%20so%20i%20cant%20use%20anything%20automatic%20sadly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20right%20now%2C%20every%20morning%20i%20load%20the%20file%2C%20mark%20column%20A%2C%20give%20it%20a%20unique%20'name'%2C%20press%20'enter'%2C%20use%20the%20mouse%20again%20to%20go%20and%20mark%20the%20next%20column%20B%2C%20use%20the%20mouse%20again%20to%20go%20to%20the%20table%20name%20area%2C%20give%20it%20a%20name%2C%20enter...%20etc....%20etc..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eas%20u%20can%20see%20it%20takes%20long%2C%20and%20for%20no%20reason.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20was%20wondering%20if%20theres%20a%20way%20to%20create%20some%20Macro%20that%20ill%20enable%20it%20only%20when%20im%20using%20this%20file%2C%20and%20every%20time%20ill%20press%20%22enter%22%20it'll%20automatically%20mark%20the%20next%20column%20(a-b-c-d)%20and%20go%20back%20to%20the%20table%20name%20area%20-%20and%20then%20all%20ill%20have%20left%20to%20do%20is%20just%20type%20the%20next%20name%20and%20press%20enter%20everytime%20...%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20im%20done%20-%20i%20disable%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%3F%3F%3F%3F%3F%3F%3F%3F%3C%2FP%3E%3CP%3Esorry%20for%20my%20english%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks.%3C%2FP%3E%3CP%3Eadi.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-493655%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500026%22%20slang%3D%22en-US%22%3ERe%3A%20mark%20the%20whole%20next%20column%20when%20i%20press%20enter%20(using%20macro%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329296%22%20target%3D%22_blank%22%3E%40doomiloom1%3C%2FA%3E%20Perhaps%20this%20little%20macro%20helps%3F%3C%2FP%3E%0A%3CPRE%3EOption%20Explicit%0A%0ASub%20RenameAllTables()%0A%20%20%20%20Dim%20sNewName%20As%20String%0A%20%20%20%20Dim%20oLo%20As%20ListObject%0A%20%20%20%20For%20Each%20oLo%20In%20ActiveSheet.ListObjects%0A%20%20%20%20%20%20%20%20Application.Goto%20oLo.Range.Cells(1%2C%201)%0A%20%20%20%20%20%20%20%20sNewName%20%3D%20%22%22%0A%20%20%20%20%20%20%20%20sNewName%20%3D%20InputBox(%22Please%20enter%20the%20new%20name%20for%20this%20table%22%2C%20%22New%20table%20name%22%2C%20oLo.Name)%0A%20%20%20%20%20%20%20%20If%20Len(sNewName)%20%26gt%3B%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20oLo.Name%20%3D%20sNewName%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0AEnd%20Sub%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500769%22%20slang%3D%22en-US%22%3ERe%3A%20mark%20the%20whole%20next%20column%20when%20i%20press%20enter%20(using%20macro%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20the%20response%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etried%20it%3C%2FP%3E%3CP%3Eit%20doesn't%20seem%20to%20do%20anything%20%3F%26nbsp%3B%20%26nbsp%3B..%20%3F%3C%2FP%3E%3CP%3Ei%20enabled%20marcos%20and%20everything.%26nbsp%3B%3C%2FP%3E%3CP%3Estill%20nothing%20really%20happens%20when%20i%20%22run%22%20it%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20main%20goal%20is%20when%20i%20press%20%22enter%22%20-%20excel%20will%20automatically%20mark%20the%20next%20column%2C%20for%20example%20-%20B%3AB%20and%20will%20go%20to%20the%20%22table%20name%22%20area%2C%20then%20ill%20put%20the%20name%20and%20press%20%22enter%22%20again%20and%20everything%20keeps%20going%2C%20until%20i%20decide%20to%20just%20stop%20the%20macro%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-502526%22%20slang%3D%22en-US%22%3ERe%3A%20mark%20the%20whole%20next%20column%20when%20i%20press%20enter%20(using%20macro%3F)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502526%22%20slang%3D%22en-US%22%3EThe%20code%20assumes%20you%20already%20have%20tables%20and%20just%20have%20to%20rename%20them%2C%20that%20is%20what%20I%20understood%20form%20your%20question.%20If%20not%2C%20Why%20are%20you%20creating%20a%20table%20for%20columns%20which%20are%20next%20to%20each%20other%3F%20that%20is%20poor%20design%20and%20can%20lead%20to%20confusion.%20I%20always%20have%20at%20least%20one%20empty%20column%20between%20two%20tables.%3C%2FLINGO-BODY%3E
doomiloom1
New Contributor

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.

3 Replies

@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

@Jan Karel Pieterse 

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

 

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
48 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies