Vba+Excel - Pivot tables and Dynamic range

[Tovarocks]

Hi!,

Well Im new on this forum, I found good things and im starting to make stuffs on Excel and VBA, I just did this macro that's send an email with specific range from two sheets of my workbook.

The problem is the next:

My two range comes from a pivot table, if I don't update it, it takes the range perfectly, but if I update it and its has less data than before, my range gets the white cells, how can I fix it?


1-Im using Excel 2010, Microsoft Office Professional Plus 2010.
2-I just want to get only the cells with data.

The Code is Inside the workbook

Thanks in Advance.

[Tovarocks]

The file was upload in the previous post.

[Paul_Hossler]

Since I don't want to type all that in and don't have any data to test with, you'll have to post a workbook

1. At the bottom right corner, there's [Go Advanced]. Click that

2. The [Add Files] and [Select Files] and [Upload] and [Done]

Capture.JPG


Capture2.JPG

Capture3.JPG

[Tovarocks]

This is what I mean with "my problem"

First image is before update pivot table and the second is after update pivot table:

[Tovarocks]

Please help!

[p45cal]

try replacing all this:

Sheets("Ruteo").Select


ActiveCell.SpecialCells(xlCellTypeLastCell).Offset(0, 0).Select


Rango = Selection.Cells.Row


Range("A1:C" & CStr(Rango)).Select


Set rng = Nothing
On Error Resume Next


Set rng = Selection
On Error GoTo 0

Range("A1").Select

Sheets("Info Cedi").Select


ActiveCell.SpecialCells(xlCellTypeLastCell).Offset(0, 0).Select


Rango2 = Selection.Cells.Row


Range("A1:C" & CStr(Rango2)).Select


Set rng2 = Nothing
On Error Resume Next
Set rng2 = Selection
On Error GoTo 0

Range("A1").Select

with this:

Set rng = Sheets("Ruteo").Cells(1).PivotTable.TableRange1
Set rng2 = Sheets("Info Cedi").Cells(1).PivotTable.TableRange1

[Tovarocks]

Error '1004'

(You can not get the property "PivotTable" range class).



Thanks for your help bro.

[p45cal]

Have you tried it on your sample file? It worked here (Excel 2010).
Your sample file had a pivot table in the very top left corner and included cells(1) which is cell A1.
Does your real file have cell A1 as part of a pivot table?

[Tovarocks]

I did it on my real file, and I changed the 1 for 3 because my pivot table in the real file start in the cells 3, but I have a title for my pivot table, here is the example in each sheet.

[p45cal]

I did it on my real file, and I changed the 1 for 3 because my pivot table in the real file start in the cells 3

It doesn't start in cells(3) (which is cell C1) but in cells(32769) (=cells(3,1), =cells("A3")).
To include the title too, then:

Set rng = Sheets("Ruteo").Cells(3,1).PivotTable.TableRange1
Set rng = Range(Sheets("Ruteo").Range("A1"), rng.Cells(rng.Cells.Count))

Set rng2 = Sheets("Info Cedi").cells(3,1).PivotTable.TableRange1
Set rng2 = Range(Sheets("Info Cedi").Range("A1"), rng2.Cells(rng2.Cells.Count))

or something like that.

[Tovarocks]

Workssss!, thanks you very much bro, I don't know how I did the code soooo long hehe.