PDA

View Full Version : [SOLVED] Vba+Excel - Pivot tables and Dynamic range



Tovarocks
10-25-2016, 03:06 PM
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. : pray2:

Tovarocks
10-25-2016, 03:19 PM
The file was upload in the previous post.

Paul_Hossler
10-25-2016, 05:05 PM
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]

17416


17417

17419

Tovarocks
10-25-2016, 05:37 PM
This is what I mean with "my problem"

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

Tovarocks
10-26-2016, 08:22 AM
Please help! :(

p45cal
10-26-2016, 09:34 AM
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
10-26-2016, 10:39 AM
Error '1004'

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

:dunno

Thanks for your help bro.

p45cal
10-26-2016, 10:53 AM
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
10-26-2016, 11:29 AM
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
10-26-2016, 01:22 PM
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 3It 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
10-26-2016, 01:53 PM
Workssss!, thanks you very much bro, I don't know how I did the code soooo long hehe.