Excel macro needed to create a table whose size varies month to month 1

[RBLampert]

I want to create a macro that will turn a block (rows and columns) of data into a table. Ought to be easy, except that the number of rows of data will vary from month to month, and I want the macro to deal with that on its own, so I don't have to define the table's size manually.

This code will correctly define the columns and rows to be included in the table:

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

But then using the macro recorder to create the table results in the following lines of code:

ActiveSheet.ListObjects.Add(xlSrcRange, [highlight #FCE94F]Range("$A$1:$K$84")[/highlight], , xlYes).Name = _
"Table4"
Range("Table4[#All]").Select

Problem: next month, the table won't run from A1 to K84. It may run from A1 to K50 or A1 to K107 (those are arbitrary row numbers).

What do I need to have in place of the highlighted text above? Or, for that matter, that whole line of code?

 

[SkipVought]

Hi,

Where is this table coming from?

How does this data get into your sheet?

 

[SkipVought]

I'd guess that you're getting a workbook from somewhere each month. Consider this external data.

In a master workbook, have a QueryTable that you can point to this external data to IMPORT the data into the already established ListObject(1).QueryTable.

You can then SaveAs if you need a monthly workbook, or append the data to a cumulative table for historical analysis (which I'd prefer). All kinds of possibilities.

 

[RBLampert]

Um, great! Except that I've never used Query Tables before.

You're right that the data is coming from an external source. My initial desire for creating the table was so that I could (have another macro--several, actually) enter a formula in the top data row of a column and have it be copied all the way down the column to the last data row, but not beyond. If I can get VBA to code the process of double-clicking the drag handle on the top cell to copy the formula down the worksheet, then maybe I don't need to table.

[Added a few minutes later.] OK, the macro recorder saves the code, but it records the last row number in the current worksheet. That's of no value for a macro.

 

[SkipVought]

I hate doing these situations where in my experience, there is probably a number of better ways.

But here's an answer to your original question. BTW in my 20+ years of working with Excel and VBA, I can count on one hand the number of times I have ADDED via VBA a ListObject, PivotTable, QueryTable in a production workbook.

Set xlSrcRange = Selection.CurrentRegion      'I'd recommend something other than Selection!!!

With ActiveSheet
   .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "Table4"
End With

There's probably a much MUCH better way to accomplish the ultimate goal.

 

[SkipVought]

" My initial desire for creating the table was so that I could (have another macro--several, actually) enter a formula in the top data row of a column and have it be copied all the way down the column to the last data row, but not beyond."

Well if the Table is a ListObject (Structured Table) when you enter a formula in ANY of the rows, the formula will propagate to ALL the rows of the table.

 

[SkipVought]

"[Added a few minutes later.] OK, the macro recorder saves the code, but it records the last row number in the current worksheet. That's of no value for a macro."

What code did you record? Please post!

 

[RBLampert]

The original post contains the relevant code that was recorded. I know that makes it useless for a macro: that was the whole point of asking the question.

I'm also sure you're right that there are better ways to do what I'm doing. Despite the fact that I've been using Excel since the early '90s, I have never had to do the kinds of things I'm trying to do now and almost never had to use VBA code, much less write it! I'm doing the best I can, asking for help when I need it, but I don't have the time to spend learning about all of these other capabilities. Plus, everything I'm doing here is simply preparing the data for the analysis I really need to do--and that's where things are going to get really challenging for me.

I'll try the code you provided above. If that does the trick, and I don't know why it wouldn't, we'll call this question answered.

 

[RBLampert]

Your code provided above the detail I needed. Didn't need that first line, just the With statement. Works like a champ now.

 

[Gavona]

Without using the structured table approach then this code can do the job. The bold bit is the key. I actually store the formula outside the range, and use the code to copy the formula to all rows and then convert it to values. This helps with calculation speed with big tables as there is only one copy of each formula stored.

Sub FormulaCopy (MyTitleCell as Range)
Dim MyRange As Range
[b]Set MyRange = Range(MyTitleCell.Offset(1, 0), MyTitleCell.Offset(MyTitleCell.CurrentRegion.Rows.Count - 1, 0))[/b]

MyTitleCell.Offset(-2, 0).Copy Destination:=MyRange
If Application.Calculation = xlCalculationManual Then MyRange.Calculate
MyRange.Copy
MyRange.PasteSpecial _
    Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
Application.CutCopyMode = False
End Sub

Gavin

 

[RBLampert]

Thanks, Gavin. I'll keep this code in mind in case the structured table approach doesn't work out.