Dropdown a value above

[christchaaya]

Hello everybody.

I'm new to the forum and to macros as well. I'm sorry to bother you with such an easy question but I can't figure out how to solve this problem even if i spent hours on it. This is the code :

Sub HLLE()
Range("E" & Cells.Rows.Count).End(xlUp).Select
Range(Selection, "a8").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection), , xlYes).Name = "Tabla1"
Range("Tabla1[#All]").Select
ActiveSheet.ListObjects("Tabla1").TableStyle = "TableStyleMedium1"
Range("f8").Value = "PERIODE"
Selection.Interior.ThemeColor = xlThemeColorAccent6
Range("f9").Value = InputBox("Cual es el periode?")
Selection.AutoFill Destination:=Range("Tabla1[PERIODE]")
End Sub

This should convert a range to table, add a column called "Periode" and color the interior of the title of this column with orange; then it should ask me the value to put in the first row of this new column and at the end autofill all the column with that value.

First problem is in ActiveSheet.ListObjects.Add(xlSrcRange, Range(Selection), , xlYes).Name = "Tabla1" because range(selection) because "Range(selection)" doesn't work but I can't define the range with the name of the cells becuase this macro will be used for a periodic task and everytime the sheet will have different data ( it will always start in a8 but i don't know in which cell it will end). The second problem is in Selection.AutoFill Destination:=Range("Tabla1[PERIODE]") and i can't figure why.

Thank you in advance