Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VBA Code To SUM Range On Specified Worksheets

I want to try and do is as follows:

- go through all the worksheets in the workbook
- if the sheets begin with A, C or P
- and if monthly index = 0
- sum the range of cells - B33:L51
- output the value into cell L52
- etc

So far this is what I got

For i = 1 To Sheets.Count
If Left(Sheets(i).Name, 1) = "A" Or Left(Sheets(i).Name, 1) = "C" Or Left(Sheets(i).Name, 1) = "P" Then

If (MonIndex = 0) Then
ElseIf (Index = 2) Then
ElseIf (Index = 3) Then
End If

My questions really are - how do u write the code for it to generically know to sum the range B33-L51 for like all sheets required. - and is it possible to output that value to a specific cell in each sheet?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
VBA Code- To Pull All The Names Of The Worksheets In A Workbook
I just started playing with creating my own macros. I've written a macro to pull all the names of the worksheets in a workbook, now what I want to do is write a piece of code that will pull data from a cell in those worksheets.

So for instance I have all my worksheet names in cell A1 to A10, I want to pull the data from cell E1 from every worksheet in A1 to A10. Can someone help me with a sample code to work with. I'm still reading Power Progamming haven't gotten so far yet.

View Replies!   View Related
VBA Code To Unmerge Cells On Specific Worksheets
I have a workbook with multiple worksheets. On some of those worksheets a have merged cells, so in order to paste new data I need to unmerge them first. Below is the code that I wrote for it. However it doesn’t work and returns “Run-time error '1004': Application-defined or object-defined error”. I looked at this post List Maximum Value From Each Worksheet and tried adding With – End With, but it failed too. As far as I understood the problem is that the Worksheets/Sheets object does not support UnMerge property, but I may be wrong. If I add ws.Activate line (test2) everything works fine.

However I was wondering if there is a way to do it WITHOUT activating the worksheets.

Sub test1()
Dim i As Integer, ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "5 - Top Network Facilities", _
"5b - Top Arb Facilities"
For i = 0 To 9
ws.Range(Cells(2 + i * 5, 1), _
Cells(6 + i * 5, 1)).UnMerge
Next i
Case Else
End Select
Next ws
End Sub

View Replies!   View Related
VBA CODE On Pasting On Last Row Of Data For Various Worksheets
I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.

How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).

View Replies!   View Related
Using Command Buttons To Change Vba Code On Worksheets
I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in

The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"

I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................

View Replies!   View Related
Sum And Filter VBA Code
I got a job in hand and do not know how to solve. 1.º Part of the problem

I have a table that is exported to excel from a program, and this table is always different sizes so I really need a VBA code.

In column F i need to have the difference between the values in column G and column C

Ex: G1 - C1 = Goes to M1
G2 - C2 = Goes to M2
and so on

2.º Part of the problem

With the values in column M and column L. I need the sum of all column values in Column M where L is the value of 2. These data are to Sheet2 - cell A1

View Replies!   View Related
Deleting Multiple Worksheets Which Ends With The Word Data Through Vba Code
I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.

View Replies!   View Related
VBA Code To See SUM And AVERAGE At The Same Time
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = ActiveCell.CurrentRegion
Application.StatusBar = _
"Current SUM is: " & _
Application.WorksheetFunction.Sum(rng) & " " & _
"Current AVERAGE is: " & _
End Sub

I now have the following problems:

I get a errot when i select cells with no number

Run-time error 1004
Unable to get Average property of the WorksheetFunction class

The sum and average stay in the status bar.

The code gets confused after i selected different columns.

I would like the code to work only when i select a range of cells.

Is it possible to move the text in the status bar more to the right?

View Replies!   View Related
VBA Code To Bold Row Which Contains SUM
I'm new to VBA and needed help with:
I need to 'bold' the row which contains sum, the problem is that the location of the row varies depending on how long/short the report is....
Here is the code I used to bold a particular row:

Range("A14").EntireRow.Font.Bold = True

But instead of A14, i need it to be a row of no more than 5 columns, one of which contains the word 'SUM'.

View Replies!   View Related
Sum Range In Macro Code
I am writing some code to help speed up data input into Excel. I take the info from the user through a form. Before pasting the data into a spreadsheet, I need to check that there is no data there already.

How can I check that the sum of range of cells in excel, as specified by the user in the form, is Zero?

I want to do this in the code rather than enter another cell in excel and sum it there

View Replies!   View Related
VBA - Select Same Range On All Worksheets
how to select the same range of cells on all worksheets via VBA?

I tried google several times but could not find anything that helps. I need to select a range of cells on all worksheets and then clear their content. However, google only tells me to select all sheets manually, which is not good since new ones are added every now and then.

Is there maybe a possiblity to select a range on all un-selected sheets?

View Replies!   View Related
Code That Will Sum The Total Of A Range Of Cells
I need a code that will sum the total of a range of cells which meets the following conditions.

If B2 is not empty then sum ("E2:R2") into cell T2.
if B3 " "" "" "" "" (""E3:R3") into cell T3

Do until "B" is empty

View Replies!   View Related
Code To Sum A Range If Codition Meets
I am tryin to sum up the range if the given coditions are met. But unfortunately it aint working properly. It is basically summing up the entire range.

Sub SysDown()
Dim Count As Long
Dim Rowend As Long
Dim Total As String
Count = 2
Rowend = 2
While Cells(Rowend, 3) ""
Rowend = Rowend + 1
Total = 0
While Count

View Replies!   View Related
Find Last Cell In The Range And Sum It Via Vba
complete the following partial code.

I am trying to determine the last cell for Non-ILEC piece and then want to insert one row below the last Non-ILEC cell and insert "Total BS Non-ILEC" and sum "H" Range just like the way its done in the partial code. The partial code I have posted is working perfectly fine with regard to ILEC piece. I need to further add the Non-ILEC piece in the partial code to accomplish my task ....

View Replies!   View Related
VBA - Sum A Range Once Meeting Multipule Criteria
I have been having problems all morning and have not been able to solve it!

I am sure this is very simple....

What I would like to do is assign the result of a sum to a variable. This part is easy what I cannot fathom or find any help with is the multipule criteria bit!

Here is the detail.
Column A - Employee Number
Column C - Area
Column F - Type
Column D - Amount paid.

I would like to sum the amount paid based on the 3 criteria, Employee Number, area & type and assign this to a variable to be used later in a sub I'm playing with.

View Replies!   View Related
Create A Named Range In VBA For Column A, Which Is A Sum Of B And C
I would like to create a named range in VBA for column A, which is a sum of B and C. The problem is that A can have 0 as a value. What I would really like to do is define the named range in column A as A2 to the last column with a value in column B(B10) which would make the named range in column a A2:A10.


View Replies!   View Related
VBA - Code- Send Worksheets Named In Col B To Email Addresses In Col A
I have a code that i got from here which will send the Workbooks named in col A to email addresses in col B, as below..

but i need it to send the named Worksheets from the currently active Workbook.

The name of the Worksheet will be contained in col A.

Sub SendWkbs()
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim iRowA As Integer, iRowB As Integer
Set wks = ActiveSheet
iRowA = 2
On Error GoTo EH
Do Until IsEmpty(wks.Cells(iRowA, 1))
iRowB = 2

View Replies!   View Related
Setting VBA Code Range
When I'm using the Macro recorder or even after I've completed a sequence is there a way to change the range so it code always covers the complete range of the data?

View Replies!   View Related
VBA Code To Copy Range IF
I have recorded the following Macro...

Sub CopyTest1()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

View Replies!   View Related
Selecting A Range Using VBA Code
I have attached a spreadsheet...I'm trying to select Range A1:N16 using VBA code...


When I use this code is only gets A1:N6...: is there anyway to get it to select the range all the way to the end of the data. I tried CurrentRegion and some other stuff but can't get it to work.: The range may go upto line 500 or 3 I just never know.

View Replies!   View Related
Set Range For This Vba Code
Sub ReplaceUnderScores()
Selection.Replace What:="_", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
End Sub

I want it to do this for range AL2 through AL2000. Ive tried a few codes trying to get it to work, but cant figure it out.

View Replies!   View Related
VBA Code To Transpose Every X Range
I'm trying to transpose 5 cells vertically into 5 cells horizontally, then do the next 5 verticals under the 5 horizontals. See attached spreadsheet for Column A (original data) and horizontal data (result). VBA code would be most useful, and original data could potentially be 600 cells long.

View Replies!   View Related
VBA Formula; Sum Up Part Of A Column Based On A Date Range
I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

View Replies!   View Related
VBA Code To Find The Last Instance Of A Value In A Range
I have a worksheet with a column that lists the city where an item is located. If the city is say Austin I need to find the last cell in the range that has Austin in it. I can find lots of examples where you can find all instances within the range and to find the last cell in the range that has any data in but nothing that tells me how to find the last cell in the range that has a specific value.

View Replies!   View Related
Calling A Named Range Into Vba Code
I have named ranges locally in the active file and a named range in a different file.

How do I call these ranges into the VBA code to evaluate them.

View Replies!   View Related
Sort Range Via VBA Macro Code
I recorded this macro - which was a simple copy and paste and then sort the results, however it works in excel 2007 and not in 2003. Even tried to record the same in excel 2003 and it still does not work. It seems to fail at the sorting stage

Sub sortprices()
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("D4:D16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Results").Sort
.SetRange Range("C4:D16")
. Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End Sub

View Replies!   View Related
VBA Macro Code To Check Spelling In Range
Is there any way to use formula or VBA to highlight cells which contain the correct spelling?

For example i have columns A filled with words in each cell but need to highlight which ones have the correct spelling, as there are more with incorrect so i need to visually see the correct spelling.

View Replies!   View Related
VBA- Compile Error For Named Range For This Code
I am getting a compile error for named range, the code is below:

View Replies!   View Related
Run VBA Code Any Time A Particular Range Of Cells Is Changed
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?

I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.

1. Is there a way to call VB (say, sheet1.myFunction())?

2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)

The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.

View Replies!   View Related
2007 VBA - Code For E-Mailing Files To Names In A Range
I have some code to email a single, different excel file to multiple Outlook recipients. I am using Outlook 2003, and Excel 2007. I always use Compatability mode in Excel as 2007 is not widely used.

The code is shown below. What I can't understand is why it fails at the code highlighted red. It tells me that there is Run-time Error '13': Type Mismatch, but I can't see the issue.

View Replies!   View Related
VBA Code To Return Multiple Found Range Objects
I read and used the Find_Range custom function provided by Aaron Blood. It's a great function, for which I have many uses, but, as I currently have it set up in conjunction with a userform, it doesn't work fully until I use it twice in a row. The first time it's used to return more than one row, it seems to stop short and only display a few of the appropriate rows. Not until it's used twice in a row does it display all the rows containing that product. What do I need to do to have it work fully each time?

I have a large worksheet from which I want to extract only the rows which contain a certain product, selected by a combobox, and paste the rows on another worksheet. I have a userform set up with an oversized listbox which displays the contents of the data worksheet. Below that I have a 'products' combobox, and a button to initiate the Find_Range. Another oversized listbox displays the results.

Here's the function contained in a module:

Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional MatchCase As Boolean = False) As Range
Dim c As Range, FirstAddress As String
With Search_Range
Set c = . Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _
SearchFormat:=False) 'Delete this term for XL2000 and earlier
If Not c Is Nothing Then
Set Find_Range = c
FirstAddress = c.Address................................

View Replies!   View Related
VBA Code To Lookup Value In Range & Update Cell In Row
I have a function that is called upon saving a UserForm. One of the Text Labels contains the ID field that I need to use to do a lookup into the first column of a Named Range and then I need to update the cell in the 4th column of that row.

From my research, I'm assuming it will go something like this:

Application.WorksheetFunction.VLookup(valve, Worksheets("TestWorksheet").Range("TestRange".Cells), 10, False).Value = "TestValue"

View Replies!   View Related
VBA Code To Set Unknown Range Based On Current Date In 1 Column
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).

View Replies!   View Related
VBA If Range Value = Something AND Another Cell Has No Entry- Prompt The User To Enter Data- My Code Is Not Working
I am trying to display a message box for the user if there is "agency" in cell o8 but nothing in p8... I tried the following code but it doesnt work..

If Range("o8").Value = "Agency" And Range("p8").Value = "" Then

MsgBox "Please provide name of agency in cell p8"

Sheet9.Shapes("cross").Visible = True


View Replies!   View Related
Vba To Sum Total Of Column Not Total Of Range
Sub sumbotton()
Dim ar As Range
Dim rng As Range
Set rng = Selection.CurrentRegion
Set rng = rng.Resize(rng.Rows.Count + 1)
For Each ar In rng.Areas
ar.Resize(1).Offset(ar.Rows.Count) = "=SUM(" & ar.Address & ")"
Next ar
End Sub

View Replies!   View Related
Sum In VBA: Sum The Numbers If The Match Occurs In A & K
With Range("H2:H5000")
.FormulaR1C1 = "= SUMPRODUCT(('Data Sheet'!R2C11:R5000C11=RC1)*('Data Sheet'!R2C81:R5000C81=""G - Prior policy incident handling errors""))"
.PasteSpecial Paste:=xlPasteValues
End With

Right now its simply counting, but what I need it to do is to sum col L if col A matches col K. Col L contains numbers, and I need a sum of those numbers if the match occurs in A & K.

View Replies!   View Related
Sum Do Not Include Rows Added After The Predefined Sum Range
I'm looking for: a cell (in column C) that sums Column C from C6 to the row above said cell. (A Total) So if new rows were added, these values would be included in the sum.

What I'm currently working with is a simple Sum formula, but this sum does not include rows added after the predefined sum range.

View Replies!   View Related
If...Sum Across Worksheets
I have a worksheet titled Current, which houses data about current Work Orders out of our shop.

In column E I have the departments listed
In column I, I have the numbers of hours worked on a particular job.

In another worksheet I am trying to sum the total hours of work performed for a particular department.


Here is the formula I tried to create, but it returns FALSE?

View Replies!   View Related
Sum Between Worksheets
I have a large data file, to make work easy for myself i have seperated each section into different worksheet. well my problem is. i want to use this very simple method for SUM "=SUM(A1+A2+A3)". If I am doing this on a same worksheet its no problem at all.

Is there any way to use this same simple method between different sheets?
like. Sum of A1 of sheet1 + A1 of sheet2 + A1 sheet3 here.. A1 is a cell and sheet 1 onwards are different sheets

View Replies!   View Related
Dificult Sum + Sum.if Date Range Criteria
0 x
-2 x
-1 x
3 x
-4 x
1 max
5 x
8 x
9 x
1 x

I need to sum the 1st column from the bottom, to the same line of the max on the right column.
First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments.
Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either
Third i tried =sum.if() but i don't have much experience with this function.

Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA
instead of:

why can't i use?

View Replies!   View Related
Add Sum To End Of Range To Sum All Cells Above
I'm getting a Type Mismatch (error 13) with the following:

Range("I1").End(xlDown).Offset(1, 0) = "= Sum(" & Range("I2", Range("I2").End(xlDown)) & ")"

Basically I want to do an autosum on the first blank cell for a list of numbers in a column

View Replies!   View Related
Conditional Sum Across Worksheets
I have two salespeople (referenced by A1) who I want to total their individual sales. Each worksheet has one salesperson and a total sales price. How do I search multiple worksheets (each having one of two salespeople) in a workbook to total only their sales? ie: cell A1 defines the salesperson and cell M1 has the sale price.

View Replies!   View Related
Sum Uniques Across Worksheets
I would like to sum uniques across worksheets. Below are the formulas that Domenic provided me and it works great but I could not modify the formula below that is in red to sum uniques instead.


Define Names:










View Replies!   View Related
Sum Fields Across Worksheets
I have a workbook that is used to calculate sales & commissions for our sales reps. The majority of the worksheets are "RepLast, RepFirst". All of these Rep worksheets include an identical format. I need to add another sheet which grabs the total quantity of products A for each rep and sums them on the new sheet. And repeats the process for products B to Z.

Is there a way to do this other than longhand naming each rep and then the field in the worksheet? We are regularly adding new reps (and losing some periodically), so I would prefer to not have to regularly update the longhand formulas (i.e. ='RepName1'!A25+'RepName2'!A25+'RepName3'!A25...etc) as that would seem prone to introduce calculation errors.

View Replies!   View Related
Indirect Sum Through Worksheets
I am trying to sum through multiple worksheets but maintain flexibility using INDIRECT but it is not working!

I have a worksheet for each month of the year Jan - Dec with a financial result. In order to get a Year To Date figure I would have a formula such as:

=sum(Jan:Jul!B3) for a July YTD.

However, I want to maintain flexibility such that I can enter the worksheet name in cell A1, e.g. Sep and then have a formula such as:


Thus allowing me to generate the correct YTD at any point. All I get is a #REF error.

View Replies!   View Related
Sum Values Between Worksheets
I have multiple worksheets of data that I need to be displayed in a new worksheet. I have been doing it by hand, but am wondering if there might be a quicker way. For example, I might want the sum of 2 cells from one worksheet to be displayed in a single cell on another worksheet.

View Replies!   View Related
Sum Same Cell From All Worksheets
I seem to recall that I was able to sum a particular cell from every sheet between a starting sheet and an ending sheet using a single formula but have been unable to locate how I did this in the past.

View Replies!   View Related
Sum Different Cells From Different Worksheets
I've seen several posts for how to sum the same cells over multiple worksheets, but what I'm trying to do is sum different cells over multiple worksheets. I know what the syntax would be if I was to manually type in the formula. For instance if I wanted to sum A1 to A5 on sheet 1, B2 to B3 on sheet 2, and A20 to D20 on sheet 3, it would be SUM(Sheet1!A1:A5, Sheet2!B2:B3, Sheet3!A20:D20). but how can I do that by simply highlighting the desired cells and using the summation button rather than manually typing in the formula?

View Replies!   View Related
Sum Same Ranges Of Multiple Worksheets IF...
I am trying to use an ActiveX CommandButton on Sheet40 to do the following:

Sheet40 (E31:AN39) = sum of (E31:AN39) for sheets 6-15 PROVIDED that cell D3= "y" in those sheets

So the steps are:
(1) Among sheets 6-15 select those in which cell D3 = "y"
(2) Set the range of (E31:AN39) in sheet40 to the sum of the same range in the selected sheets

ie cell E31 = sum of cells E31 in selected sheets... cell E32 same... cell AN39 same

View Replies!   View Related
Sum Of Values In Lists Across Several Different Worksheets
I have several worksheets across the bottom of the workbook, lables September, October, November, December, January, Febuary... Each of these worksheets contains a table of identical formatting. (See below)....

View Replies!   View Related
Sum Across Unknown Number Of Worksheets
The colours are just to mark the ranges

As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)

Is there a code I can run for this summing up to be done?

Yellow is where data is entered

Red is where the Yellows range sheets need to add up before it

The Blue is where all the Red range sheets need to add up

View Replies!   View Related
Copyright © 2005-08, All rights reserved