Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 6,953
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Programming [email protected] Excel Programming 3 April 13th 06 08:57 AM
Help with Excel VBA Programming zeus Excel Programming 1 December 15th 05 09:22 PM
Excel Programming Sprad-Dog New Users to Excel 2 July 13th 05 07:12 PM
ADO programming and Excel Mark \(InWales\)[_26_] Excel Programming 2 March 15th 05 10:32 PM
Excel Programming in VB sapta Excel Programming 0 September 10th 03 05:52 AM


All times are GMT +1. The time now is 04:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"