Excel Macro/VBA

[warren66]

I've created a macro, and it does the business for me. However, the is a line in the macro which pre-selects the cells. The snag is, I want to use the macro with other sheets, and the cells I would want to select would be different in each case.

As it stands, my code reads:

SourceData:= Array("Sheet1!R1C1:R27C16")).CreatePivotTable TableDestination:=""

At the moment, I have to edit the R27C16 bit to reflect the location of the last cell.

How do I get the macro to prompt the user to select the cells, or altenatively select all the cells from R1C1 ( or A1) to the last cell i.e. the lowest row, and the rightmost column?

There must be a guru out there who can help me out!!

Thanks in advance

Warren

 

[finglem]

Warren

Have a look in Help for the CURRENT REGION and END properties.

Using these should enable you to get to where you need to be

 

[warren66]

Thank you Finglem, but I could do with a little more help please.

I have identified the code needed, namely

Range(Range("A1"), ActiveCell.SpecialCellsxlLastCell)).Select

but I don't know where it fits, and what I delete from my existing code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:= _
Array("Sheet1!R1C1:R27C16")).CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10


Please could you be more specific.

Thanks again


Warren66

 

[SkipVought]

Warren,

Use a Dynamic Named Range, via Insert/Name/Define using the OFFSET function as described in this FAQ

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[warren66]

Thanks for your help, guys; Skipvoight's suggestion has been the easiest to implement, so I've gone down that line.

Regards

Warren