Macro to Get A Range then Creates Pivot 1

[Adnane]

Hi,

i have a sheet that have a constant number of cols but the varies in row numbers. actually what i need is to insert a bit of code in my macro that would detect the range of the database and create a pivot table based on this range.

Thanks

Adnane

http://www.microsoft.com/northafrica

 

[Bowers74]

Assuming that the cells in the furst column and the top row of your Pivot Data are contiguous, you could use something like this to get the range address:

Sub PivotDataRange()
Dim PivotRange As Range
Set PivotRange = Range([A1].End(xlToRight), [A1].End(xlDown))
MsgBox PivotRange.Address
End Sub

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

Hi,

First you need to create a Name Reference.

Activate the sheet having you data table. There can be NOTHING else on this sheet but your data. Your table myst start in A1.

Menu Item - Insert/Name/Define

In the Define Name window textbox, enter a descriptive name for your database (no spaces). I often use a name like dbCustomers.

In the RefersTo textbox paste this formula.

=offset(A1,0,0,counta(A:A),counta(1:1))

If you already have the PivotTable defined, activate the wizard in the PivotTable and go BACK to the data source. Replace the absolute reference with the name that you just assigned. If you are creating a new PivotTable, use the name for the data source.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JohnAcc]

to Find the number of rows in a list of data in a spreadsheet:

Sub FindNumberOfRows
Dim NumOfRows As Long
NumOfRows = Range(Range("A65536&quot _
.End(xlUp), "A2&quot.Rows.Count
End Sub

Assuming you have a title in Row 1!

You can then use the variable NumOfRows in the rest of your code

Rgds

John

 

[JohnAcc]

Hi Skip,

What does the:

=offset(A1,0,0,counta(A:A),counta(1:1)) in the refers to box do ?

Rgds

John

 

[Bowers74]

You could even turn my procedure into a Function:

Function TableRange(Top_Left As Range)
Dim Table As Range
Application.Volatile
Set Table = Range(Top_Left.End(xlToRight), Top_Left.End(xlDown))
TableRange = Table.Address
End Function

Where Top_Left is the top left cell of your table.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[SkipVought]

John,

Look at the Help on OFFSET.

I basicly defines the range from A1 down (counta(A:A)) and across (counta(1:1))

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Adnane]

Well thank you all. but let me please give you an example of what i'm doing hopefully it'll be clearer to you (i'm still a beginner in VBA)

i have a sheet that looks like this:

the first cells (A1, A2,...) are filled with data i'm not intersted in (this varies from 4 to 7 lines in general. then there's an empty line and then comes my table. here's the macro i have so far that does a small calculation first and then has to ouptut a pivot table respecting a specific format (but this is ok, i'll deal with it).

Sub Macro8()
Cells(1, 2).Select
Selection.End(xlDown).Select

Cells.Find(What:="Opty Prod Net Extended Price", After:=ActiveCell, LookIn _
:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


ActiveCell.Next.Activate
ActiveCell.FormulaR1C1 = "Total Opty Revenue"

ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2]+RC[-3],rc[-1])"


While Not ActiveCell.Offset(1, -1).Value = ""

ActiveCell.Offset(1, 0).Select

ActiveCell.FormulaR1C1 = "=IF(RC[-1]=0,RC[-2]+RC[-3],rc[-1])"

Wend
End Function

Hope you'll be able to help me out with this.

Regards,


Adnane

http://www.microsoft.com/northafrica

 

[Bowers74]

Skip . . .

It's all yours

I can definately see a FAQ reference coming here!

;-)


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JohnAcc]

Hi There,

I have figured out the refers to and that works fine when i initially set up the pivot i.e it picks up all the data regardless of the number of rows.

BUT - when i refresh the pivot table i get a blank pivot table ?

I am testing this with simple data 5 or 6 rows down and 2 columns across.

Why does this happen ?

 

[Adnane]

Hi,

By adding this to my above macro, i'm able to name the range that should be included in the pivot

Set PivotRange = Range(ActiveCell.End(xlToLeft), ActiveCell.End(xlUp))
PivotRange.Name = "My_Range"

but i don't know how to use a named range in the create pivot function

Thanks

Adnane

http://www.microsoft.com/northafrica

 

[Adnane]

There you go!! it works

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"my_range&quot.CreatePivotTable TableDestination:="", TableName:="PivotTable6" _
, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
Application.Run "OnSheetActivate"

Adnane

http://www.microsoft.com/northafrica

 

[Bowers74]

Isn't that almost exactly what I said in my first post???





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Adnane]

Well yes Mike, i took what you gave me and added a bit of code to do what exactly needed. without your post (and the others' of course) i wouldn't have done this.

Thanks so much

here's a star for you

Adnane

http://www.microsoft.com/northafrica

 

[Bowers74]

Thanks for the

;-)

But if the others helped too, I don't want all of the credit! The other posts that helped you should receive stars as well.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!