How do I run a macro only on currently selected cells?

Question

I am a complete beginner at macros. I frequently have to create and rename hundreds of tables and I recorded the macro to try and automate the process, but I cannot figure out how to adjust it so that it runs only on the selected cells. The name of the table is always the same as the header. Can someone please help me?

Here is my recorded Macro:

Sub Table_Creation()
'
' Table_Creation Macro
' Makes tables easier
'
' Keyboard Shortcut: Ctrl+t
'
    Range("CV1:CV6").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$CV$1:$CV$6"), , xlYes).Name = _
        "Table432"
    Range("Table432[[#All],[dayton_oh]]").Select
    ActiveSheet.ListObjects("Table432").TableStyle = "TableStyleMedium1"
    Range("Table432[[#Headers],[dayton_oh]]").Select
    Selection.Copy
    ActiveSheet.ListObjects("Table432").Name = "dayton_oh"
End Sub

A similar post offered the solution of changing the range to selection, but I guess I did it incorrectly and received an error message. Any help is appreciated!!

Thank you

In general, you want to [avoid using select in your code](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — cybernetic.nomad, Dec 15 '22 at 20:10
Why don't you simply drop that first line `Range("CV1:CV6").Select`? — Dominique, Dec 16 '22 at 07:18

WeAreOne · Answer 1 · 2022-12-15T23:23:23.123

Sub Table_Creation()
' Makes tables easier
'
' Keyboard Shortcut: Ctrl+t
    
    'declaration of variables
    Dim MySelection As Range
    Dim MyTableName As String
    
    'fill variables
    Set MySelection = Selection
    MyTableName = MySelection.Cells(1, 1).Value 'first cell in your selection will be the table name
    
    'creating table with your table name and style
    ActiveSheet.ListObjects.Add(xlSrcRange, MySelection, , xlYes, , "TableStyleMedium1").Name = MyTableName
End Sub

Try on a copy of the workbook first, so you are sure it will not mess up your sheets, just in case it doesn't do what you want.

How do I run a macro only on currently selected cells?

1 Answers1