Create a dynamic range in a macro

[Buzza20]

Hi, I have written the following macro to create a table, however the raw data will regularly change the number of columns and rows so I want to be able to change this section Range("$A$1:$F$991") to a dynamic range to account for all columns or rows that may have data in them. Any help would be great.
Cheers, Aaron.

Sub Share_Report()
'
' Share_Report Macro
'


Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$991"), , xlYes).Name = _
"Table1"


End Sub

 

[Yongle]

Try

Sub CreateTable()
    Dim tbl As ListObject
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
End Sub

Naming the table "Table1" may cause the code to fail (ie if created previously)
- the table is automatically named Table1 if the name does not already exist

Assigning the table to a variable means that you can refer to the table without knowing the name
whole table - tbl.Range.Address(0, 0)
excluding header - tbl.DataBodyRange.Address(0, 0)
count of rows - tbl.Range.Rows.Count
etc

 

[Buzza20]

Thanks Yongle, that worked great. However as you mentioned I've got references to "Table1" further on in the code. How do I replace these with a variable so that it doesn't matter if the Table is called something else? See below.

Sub Share_Report()
'
' Share_Report Macro
'


'
Dim newsheet As String
Dim tbl As ListObject


Range("A1").Select
Application.CutCopyMode = False
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
Range("Table1[[#Headers],[Agency]]").Select
Sheets.Add
newsheet = ActiveSheet.Name

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=6).CreatePivotTable TableDestination:=newsheet & "!R3C1", _
TableName:="PivotTable1", DefaultVersion:=6
Sheets(newsheet).Select
End Sub

 

[Yongle]

Simply adjusting .. and your code becomes:

Sub Share_Report()

    Dim newsheet As String, tbl As ListObject
[COLOR=#008080]
[/COLOR]    Range("A1").Select           [I][COLOR=#008080]'why select ?[/COLOR][/I]
    Application.CutCopyMode = False
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
    
    Range([COLOR=#ff0000]tbl.Name[/COLOR] & "[[#Headers],[Agency]]").Select           [I][COLOR=#008080]'why select ?[/COLOR][/I] 
    
    Sheets.Add
    newsheet = ActiveSheet.Name

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            [COLOR=#ff0000]tbl[/COLOR], Version:=6).CreatePivotTable TableDestination:=newsheet & "!R3C1", _
            TableName:="PivotTable1", DefaultVersion:=6

   Sheets(newsheet).Select           [I][COLOR=#008080]'why select ?[/COLOR][/I] [COLOR=#008080]([I]In[/I][/COLOR][COLOR=#008080][I]serted sheet is the active sheet)[/I][/COLOR]
End Sub

This should do the same thing

Sub Share_Report()

    Dim tbl As ListObject, [B]ws[/B] As Worksheet

    Application.CutCopyMode = False
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)

    Set [B]ws[/B] = Sheets.Add
                
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        tbl, Version:=6).CreatePivotTable TableDestination:=[B]ws[/B].Range("A3"), _
        TableName:="PivotTable1", DefaultVersion:=6
    
End Sub

(now can refer to new sheet like this: ws.Activate )

 

[Yongle]

Have a look at both of these...

vba-guide-to-listobjects-Excel-tables
vba-guide-excel-pivot-tables

 

[Buzza20]

That worked perfectly - thanks so much
I'm only new to macros and vba so I really appreciate you help

 

[Yongle]

thanks for the feedback