VBA Programming in Excel
 

Hello,

Like most people on this thing I am pretty new to using VBA with Excel.
I have done a few things and now I am stuck on a task. Here's my
problem.

I have a Workbook that has 2 Sheets that are like master sheets and the
rest are individual projects that feed into these spreadsheets. I want
to take a certain cell from each of these worksheets named "Project x"
and sum up the total on a sheet "Employee Billings". I can do that in
excel however I am going to continue to add new projects so I want it
to update when I click a button after a new sheet is added with new
information. Anyone have any suggestions on how to accompnish this?

I know how to add buttons and all I just don't know how to code it.

Any help would be great!

Thanks,

Pat

VBA Programming in Excel
 

for each sh in thisworkbook.worksheets
s = lcase(sh.name)
if s < "master1" and s < "master2" then


end if
Next

If the project X cell is in the same place in all data sheets, put in two
dummy sheets that are blank

First
Last

put all data sheets except the master sheet between First and Last in the
tab order.

In the master sheet use a 3D reference

=Sum(First:Last!B9)

or if a range of cells

=Sum(first:Last!B2:B20)

when you add new sheets between First and Last, they will be included in the
sum.

--
Regards,
Tom Ogilvy


" wrote:

Hello,

Like most people on this thing I am pretty new to using VBA with Excel.
I have done a few things and now I am stuck on a task. Here's my
problem.

I have a Workbook that has 2 Sheets that are like master sheets and the
rest are individual projects that feed into these spreadsheets. I want
to take a certain cell from each of these worksheets named "Project x"
and sum up the total on a sheet "Employee Billings". I can do that in
excel however I am going to continue to add new projects so I want it
to update when I click a button after a new sheet is added with new
information. Anyone have any suggestions on how to accompnish this?

I know how to add buttons and all I just don't know how to code it.

Any help would be great!

Thanks,

Pat

VBA Programming in Excel
 

Pat,

Insert a blank sheet named "First" after your master sheet (which should the first sheet), and
another at the end of your worksheets named "Last"

Then when you need to sum up cells, use a formula like this.

=SUM(First:Last!B4)

Any new sheets inserted between First and Last will automatically be added to your SUM formula.

HTH,
Bernie
MS Excel MVP


wrote in message oups.com...
Hello,

Like most people on this thing I am pretty new to using VBA with Excel.
I have done a few things and now I am stuck on a task. Here's my
problem.

I have a Workbook that has 2 Sheets that are like master sheets and the
rest are individual projects that feed into these spreadsheets. I want
to take a certain cell from each of these worksheets named "Project x"
and sum up the total on a sheet "Employee Billings". I can do that in
excel however I am going to continue to add new projects so I want it
to update when I click a button after a new sheet is added with new
information. Anyone have any suggestions on how to accompnish this?

I know how to add buttons and all I just don't know how to code it.

Any help would be great!

Thanks,

Pat

VBA Programming in Excel
 

Awesome, works great. I saw that a bunch of times on the internet,
just wasn't thinking in that direction. Thanks again!


Pat

Bernie Deitrick wrote:
Pat,

Insert a blank sheet named "First" after your master sheet (which should the first sheet), and
another at the end of your worksheets named "Last"

Then when you need to sum up cells, use a formula like this.

=SUM(First:Last!B4)

Any new sheets inserted between First and Last will automatically be added to your SUM formula.

HTH,
Bernie
MS Excel MVP


wrote in message oups.com...
Hello,

Like most people on this thing I am pretty new to using VBA with Excel.
I have done a few things and now I am stuck on a task. Here's my
problem.

I have a Workbook that has 2 Sheets that are like master sheets and the
rest are individual projects that feed into these spreadsheets. I want
to take a certain cell from each of these worksheets named "Project x"
and sum up the total on a sheet "Employee Billings". I can do that in
excel however I am going to continue to add new projects so I want it
to update when I click a button after a new sheet is added with new
information. Anyone have any suggestions on how to accompnish this?

I know how to add buttons and all I just don't know how to code it.

Any help would be great!

Thanks,

Pat