Another Dynamic Range in Macro Question

[stwilhite]

Howdy. I am new, not just to this forum (although I've trolled it for years when I need help), but new to macros and VBA. As well, there are a lot of threads about this topic, but I can't seem to find one that answers my specific question, or, and more likely, I'm not savvy enough with VBA to make it work.

I'm using Windows 7 and Excel 2010.

The scenario is that I want a macro to create a pivot table, but the sheet that has the data will have a dynamic number of rows every day. The number of columns remain static, but not the rows.

Below is what I recorded and contains the bare minimum of the end result, which of course will includes more steps. But for the purpose of this request, that's as simple as it gets.

Sub Test()
'
' Test Macro
'


'
    Selection.CurrentRegion.Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Raw!R1C1:R193C73", Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable7", DefaultVersion _
        :=xlPivotTableVersion10
    Sheets("Sheet1").Select
    Cells(3, 1).Select
End Sub

You will notice the name of the sheet is "Raw", which I have to name before running the macro as the tab is also dynamically named each day. That's another problem I can't figure out, but one thing at a time. If I can figure out the cleanest way to change the R193C73 to RasmanyasthereareC73, I'd be happy!

Thanks!

 

[pbornemeier]

Assuming you run the code with the input worksheet as the activesheet, this code will amswer both of your requests:

Sub Test()

    Dim lLastDataRow As Long
    Dim sInputSheetName As String
    
    sInputSheetName = ActiveSheet.Name
    
    With Worksheets(sInputSheetName)
        lLastDataRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=sInputSheetName & "!R1C1:R" & lLastDataRow & "C73", _
        Version:=xlPivotTableVersion10).CreatePivotTable _
        TableDestination:=ActiveSheet.Name & "!R3C1", TableName:="PivotTable7", _
        DefaultVersion:=xlPivotTableVersion10
    Cells(3, 1).Select

End Sub

If the number of columns is also dynamic then that can be accounted for in a similar fashion.

 

[stwilhite]

Okay, Mr. pbornemeier, if that is your real name! Thanks!!! Worked perfectly!

 

[pbornemeier]

Glad it worked. Thanks for letting me know.