vba - dynamic data range for pivot table

[MainChasm]

Hi guys

So I know 0 about vba, but I am learning as I'm developing this macro to help me at work.
I am trying to create a pivot table for sheet "monitor" and other pivot table for sheet "copy"
But I am not being able to put data range dynamic

This is what i've done so far

With current code, the pivot table is correctly created in Sheet "monitor".
But if I run it in Sheet "copy" it does not work an returns error :Run-time error '1004': The Pivot Table field name is not valid.
In order to solve it I changed the code line.
SrcData = ActiveSheet.Name & "!" & Range("A3:L2000").Address(ReferenceStyle:=xlR1C1)

Changed Range("A3:Z2000") to Range("A3:L2000")
And it worked.
But my table is uptaded several times in the day and it can have more or even less columns.

So I remembered to select range with Shift + Ctrl + Right and then Shift + Ctrl + Down
or Simply Ctrl + All

Range("A3").Select
Set rStart = Selection
Selection.CurrentRegion.Select
SrcData = ActiveSheet.Name & "!" & rStart.Address(ReferenceStyle:=xlR3C1)

At first it gave me Run-time error '1004' The command requires at least two rows of source data
and I changed
Set rStart = Selection
Selection.CurrentRegion.Select

to
Set rStart = Selection.CurrentRegion.Select

and this time Run Time Error '424' object required

Now I am stuck here and don't know what to do.
Tried some other things but without success.
Because when debug does not highlight
Set rStart = Selection.CurrentRegion.Select
it highlights
'Create Pivot Cache from Source Data
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

Does someone have an idea to work this around?
Thank you for your help and time.

File in attach

Best regards
MainChasm

[sktneer]

When setting a range, why are you selecting it using the .Select keyword in the end?
Set rStart = Selection.CurrentRegion.Select

Change that line to...

Set rStart = Selection.CurrentRegion

and this will take care of the error you are getting.

[MainChasm]

you are right.
I deleted the "Select" and is working now
That did not come to my mind, because I thought it had to be selected in order to perform the rest of the code.

sktneer thank you very much for the help

[sktneer]

No problem! Glad I could help.

[MainChasm]

Hi sktneer,

I made a copy of my previous excel, because I am making some changes.
And somehow my pivot table now is not working and I made no change to the code.
In old excel the code works and in this new one it does not.
Could you take a look please the error is. Runtime error 91 Object variable or With block variable not set

Thank you for the help

[sktneer]

If you are talking about the sub-routine called "UMTS_Monitor_CopyPaste_Pivot_Table" on Module2, declare the variable SrcData as String not as a Range.
The correct declaration should be...
Dim SrcData As String

[MainChasm]

Hey, yes you are correct.
I came to say I made that change and it worked without knowing if was right or not and just noticed you had already replyed.
You are confirming that I made right and that's the correct way thank you

Thank you

[sktneer]

You're welcome!