使用CURRENTREGION函数通过VBA使用现有数据创建表

So I'm 90% of the way into creating my first big macro project. Thanks for all your help so far everyone! I've come up against an issue however - which I think should be the last issue I should face before putting the macro into use.

所以我90%的方式来创建我的第一个大宏项目。感谢大家的帮助!我遇到了一个问题 - 我认为应该是在使用宏之前我应该面对的最后一个问题。

I've written a macro which copies the data presented in a master spreadsheet, creates a new workbook in the target location, then creates a table using the information provided.

我编写了一个宏来复制主电子表格中显示的数据,在目标位置创建一个新工作簿,然后使用提供的信息创建一个表。

What I want to do is use CurrentRegion to allow the data to alter month-to-month. I think the code should go something like this

我想要做的是使用CurrentRegion允许数据每月更改。我认为代码应该是这样的

`GeneratePivotTables Macro

'Converts information stored on sheet "Data" to a table

Dim TABLE As Range

Set TABLE = Sheets("Data").A1.CurrentRegion

Sheets("Data").Listobjects.Add(x1SrcRange, Range("TABLE"), ,1Xyes).Name="Data"

I know this as presented is incorrect, but I'd like some help just parsing this correctly! My aim is to then be able to use the table "Data" to create pivot tables.

我知道这显示不正确,但我想要一些帮助只是正确解析这个!我的目标是能够使用“数据”表来创建数据透视表。

2 个解决方案

#1

The following code converts the CurrentRegion around cell A1 into a table. See if you can modify it for your needs.

以下代码将单元格A1周围的CurrentRegion转换为表格。看看您是否可以根据需要进行修改。

Sub ConvertRangetoTable()
    Dim rngTable As Range

    Set rngTable = Sheets("Data").Range("A1").CurrentRegion

    Sheets("Data").ListObjects.Add(xlSrcRange, rngTable, , xlYes).Name = "All_Data"
End Sub

#2

This seems to be sufficient

这似乎就足够了

Sub M_snb()
    Sheet1.ListObjects.Add(1, [A1].CurrentRegion, , 1).Name = "snb_001"
End Sub

#1