Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Programming | Excel Programming | |||
Help with Excel VBA Programming | Excel Programming | |||
Excel Programming | New Users to Excel | |||
ADO programming and Excel | Excel Programming | |||
Excel Programming in VB | Excel Programming |