Easy problem on range selection

[christchaaya]

Hi everyone. Sorry to bother you with this question but I'm new to VBA. I have an error that I can't solve that is in the red line. The problem is that excel don't recognize the variable x in "Range("$A$8: x")". This macro should be used for a periodic task and everytime the last cell of the table is different so i can't just put a normal range. I tried everything that i could think about to confer to x the range i wanted... HELPPP!!!!

Sub PtrHLLE()
Dim x As Range
Range("d" & Cells.Rows.Count).End(xlUp).Select
ActiveCell.Offset(0, 1).Select
Range("x").Value = ActiveCell
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:x"), , xlYes).Name = "Tabla1"
Range("Tabla1[#All]").Select
ActiveSheet.ListObjects("Tabla1").TableStyle = "TableStyleMedium1"
Range("F8").Select
ActiveCell.FormulaR1C1 = "PERIODE"
Selection.Interior.ThemeColor = xlThemeColorAccent6
Range("f9").Value = InputBox("Cual es el periode?")
Range("f9").Select
Selection.AutoFill Destination:=Range("Tabla1[Periode]")
End Sub

 

[nuked]

what are you trying to do with this line:

Range("x").value = ActiveCell?

Do you mean:

Dim x as string

x = ActiveCell.Address
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:" & x), , xlYes).Name = "Tabla1"


or maybe

Dim x as range

set x = ActiveCell
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$8:" & x.address), , xlYes).Name = "Tabla1"




If not, you'll need to clarify your problem.