Create a table using existing data via VBA, using the CURRENTREGION function

Question

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.

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

`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.

what is the A1 doing after Sheets("data")? did you mean sheets("data").Range("A1")? — Dannyg9090
– Dannyg9090, Commented May 22, 2015 at 15:28
Yep, that fixed that bit of the code! it's saying the next bit Sheets("Data").Listobjects.Add(x1SrcRange, Range("TABLE"), ,1Xyes).Name="Data" is invalid syntax - which i know, but i want to know how to correct it! — Miller86
– Miller86, Commented May 22, 2015 at 15:31
replace the range("Table") with just TABLE, thats the first thing noticed so far — KKowalczyk
– KKowalczyk, Commented May 22, 2015 at 15:37

basodre · Accepted Answer · 2015-05-22 15:50:42Z

7

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

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

answered May 22, 2015 at 15:50

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Miller86 Over a year ago

Thanks! I think this is pretty much exactly what I need!

snb · Accepted Answer · 2015-05-22 20:24:34Z

0

This seems to be sufficient

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

answered May 22, 2015 at 20:24

snb

3431 silver badge4 bronze badges

Collectives™ on Stack Overflow

Create a table using existing data via VBA, using the CURRENTREGION function

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related