Macro/vba

QuestionEdit

I am trying to record a Macro to organize data for me, including creating Pivot Tables. The data I am working with always starts in cell A12 and stretches across to AI12, however, the amount of rows it takes up varies. If I record the macro in a sheet that is populated until row 250, the macro doesn't work when I use it in a different sheet that is populated until row 300. Is there something I can do to have one macro that captures all of the data in every sheet?

Additionally, I am wondering - I insert the pivot tables below the data. When I run the macro in another sheet, will it place the tables in proportion to the data (ie, one cell below it) or will the table try to insert in the exact same cell is the sheet the macro was recorded in?

AnswerEdit

Laura,

you can use code like this to determine the number of rows

lastrow = cells(rows.count,1).End(xlup).row

then you can do

set r = Range("A1:AI" & lastrow)

to define the range of the table.

in your recorded code, do that at the top and then substitute the r into arguments which used a hard coded range

In you pivot table code, look where it says where to put the table. that is probaby a hard coded address. If it is, then you will have to use code similar to above and replace the hard coded address.

Another thing to look at is

Range("A1").CurrentRegion this is the same as clicking in A1 and then doing Ctrl+Shift+8 (or Ctrl + *)

then higlighted region expands out until it reaches a blank row and column.

Also, your code may use a lot of Selections. You can adjust either of the above to be

r.Select or Range("A1").CurrentRegion.Select

if those fit better into your code.

=====< added comment >======Edit

Laura, sorry you were unhappy with my response. Richard Rost is the author of THE COMPLETE IDIOT'S GUIDE to2010. He may be able to help you in a way where you can understand the answer. Also Isaac prides himself on being able to answer in simple terms - able to communicate at your level. So perhaps you can understand him better. Best of Luck.