Subtotal Not Working In Macro

Jan 15, 2009

I've got a semi-recorded macro to format a report I run, and towards the end it deals with sorting, subtotalling, formatting text etc. The main function of the macro is to split a large report into several smaller reports defined by whichever manager name appears in the relevant column. The smaller reports need subtotalling by 2 different columns so I have the following macro code :



Cells.Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True

For some reason this only subtotals by the second column and not the first. I tried swapping them over so GroupBy1 then GroupBy2 but it's still the same. Anyone got any ideas why this might be? I appreciate it's not the smoothest code I could use but I don't know enough about code to do any better.

View 9 Replies


ADVERTISEMENT

Sort By Subtotal, Make The Subtotal Stand Out

Feb 5, 2007

way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.

What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?

View 12 Replies View Related

Subtotal Formula - To Show Up In The Subtotal Value Column

Oct 23, 2008

Im trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....

View 10 Replies View Related

Macro: Subtotal

Mar 18, 2007

I have a spreadsheet that has columns for "Invoice Number," "Order Number," "Invoice Date" (DD/MM/YYYY), & "Amount." It is formatted using a macro.

I need macro code that will group all invoices by month and subtotal each month, then put a grand total at the bottom. Also a blank row needs to seperate each month.

View 10 Replies View Related

Macro To Subtotal All Sheets

Mar 5, 2009

how to code a macro that totals columns d through z on several worksheets that have various numbers of rows. Each sheet has the same number of columns but different numbers of rows.

View 9 Replies View Related

Subtotal Columns With Macro

Dec 30, 2006

I want to know VB code for excel subtotal function. Actually what I want to achieve is as follows. I have data for particular financail year with almost 15 columns. One column contains date. I want to take subtotal for each month ( say for Jan, Feb and so on ) for each column.

View 2 Replies View Related

Error When Using Subtotal Function In Macro

Jan 14, 2013

I am trying to create a macro which will use the subtotal function to do a count on all the the new loans in column A. The could below is what I have tried but I am getting a run-time error 13 "Type Mismatch" error at the line highlighted in red in the code. How to write the Subtotal function in the macro.

Code:
Sub CountNewLoans()

Dim cnlFinalRow As Integer
Dim cnlRow As Integer

Dim x As String
Dim y As String

[Code] ......

View 6 Replies View Related

Macro To Delete Rows When Subtotal Equals To Zero

Aug 6, 2009

Any VBA code to delete rows in a subtotalled list?

The subtotal line and the associated rows should be deleted when subtotal equals zero.

View 9 Replies View Related

Pivot Table Subtotal Removal Macro?

Jun 1, 2012

Below is part of my recorder macro for removing subtotal on a specific field:

ActiveSheet.PivotTables("PivotTable2").PivotFields("PRODUCT MANAGER"). _
Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

I am new to coding in a pivot. I hope the no.of "False" depends on no.of "PRODUCT MANAGER" (There are 12 PRODUCT MANAGERs)

But the problem is the no.of PRODUCT MANAGERs vary with data.

So i am stuck on how i can input correct no.of "False" (here 12) inside the array part.

View 1 Replies View Related

Macro To Subtotal Varying Lists Of Numbers

Apr 17, 2014

I am fairly new to excel and I am having trouble trying to get a sub total function to work for me. I need the subtotal to for a list of about 20 columns in one line with a varying amount of rows. So the subtotal must stop when there is a blank at the top. I can get the macro to find the totals A on the spreadsheet and then offset to beneath the columns of numbers but the sub total formulas takes in every number. Here is the example:

456.00
8,798.00

45,465.00
789,789.00

45,465.00
789,789.00

45,646.00
7,897.00

Totals A

[Code] ........

View 9 Replies View Related

Macro To Sum Or Subtotal Ranges Of Varying Lengths In A Column

May 1, 2014

I've reached a point in this macro where I'm stuck with NO clue how to achieve this, or even whether it can be done. In the Before sheet below, I need to bold the cells in H & I if the word "Total" is in G. I could probably manage that part, but here's the weird part: I need to add a formula to H & I of each row with "Total" in G to sum the numbers above it, from the previous total down to the current total. But there's no telling how long each range might be on a given report; from 1 row up to 8 rows.

THEN, in the second row below the end of columns H & I, I need to put a formula totalling all of the preceding Totals

I wonder if there might be a way to tell it to put a formula in H by each cell that has "Total" in G, with the formula summing or subtotalling everything from the cell in H that has data in F to the cell just above the "Total".

Attached File : Acct Activity.xlsx‎

View 14 Replies View Related

Macro Not Working On Other Spreadsheet

Feb 7, 2014

The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.

Is this due to a security setting or missing code line items?

copy&paste in next blank cell2.xlsm

View 10 Replies View Related

Macro Not Working For Other Workbooks

Jan 28, 2014

Below is a code which I added to my 'Quick access toolbar' (and which is based off a hidden workbook). I use this macro to fulfill tasks to regular excel spreadsheets that are not macro enabled, however, I can't seem to get this macro to work for other workbooks. How to modify my code to work for other spreadsheets.

[Code] .....

View 4 Replies View Related

Unprotect Macro Not Working

Feb 2, 2014

It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.

[Code] .........

I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.

View 8 Replies View Related

FIND Not Working In Macro

Sep 2, 2008

I am using this code in a macro to find a substring from a string in cell G1.

View 11 Replies View Related

One Macro Stops Another From Working

Dec 2, 2011

I have this macro which sits in the workbook module:

Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range

On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete

[Code] .......

It's used to highlight the selected/active row.

But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:

Code:
Sub Send_Mail()
'this sends an email that sends a text message

Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String

[Code] .......

I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?

View 5 Replies View Related

Macro Recorder Not Working On Other PC

Jul 7, 2009

Have recorded a Macro which is doing a great job, but when i try to run the same macro from other pc its not running,pops an search error.

View 9 Replies View Related

Ctrl+down Not Working In Macro

Jul 22, 2009

I've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.

The macro I have now is:

Sub Copy()
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub

The column is only 25 rows tall.

View 9 Replies View Related

Save As Macro Not Working

Feb 17, 2010

I have a macro to export two worksheets and save as, but the save as part will not work, it just exports to Book1.

Ive checked the directory and everything is fine.

I want the value of a cell in one of the exported worksheet to be included in the save as name.

I'm just wondering if the code needs to somehow specify the worksheet to get the cell value from or will it default to the first worksheet. but either way my code is still not working and i can't tell why.

Should i just create a seperate macro for the save as?

Here is the ...

View 9 Replies View Related

IF Function Not Working For Entire Row While On Macro

May 10, 2014

I got a problem with a macro i'm working on. I got column D which contains text as "Figures", "Toys", "3DS", "PS3" etc. I also got column N where i need the macro to display "Toys" if it's a toy, figure, etc; or "Games" if it's "PS3", "PS4" etc.

I'm using the following code, to also select only blank rows (so to ignore row 1 which is table header). Problem is that the result shows only "Games".

VB:
Columns("N:N").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[-9]=""*toys*"",""Toys"",""Games"")"

View 4 Replies View Related

Macro That Should Hide Rows Not Working Well?

Apr 17, 2014

I'm having a problem with my macro .However, if i use the following script:

[Code]......

If i am correct all the rows with a value less than 1 should be hidden right? However, some rows are hidden when they shouldn't be.

Im working on a quotation with different products in it. And the rows with a 0 in it should be hidden and that's pretty much working. But it also hides some of the subtotals(which i dont want) and some stay unhidden.

View 6 Replies View Related

Macro For Creating Months - Not Working

May 20, 2014

I have macro for creating months. Code worked fine, but now I use merged cells in range where days should be created, and code no longer works (Error : "Cannot change part of merged cells").

Tried with unmerging cells, but then code inserts days in shorter range that I need, so code needs to be changed.

[Code] .....

Attached File : Macro_months.xls‎

View 14 Replies View Related

Taking Over Spreadsheet With Macro Not Working?

Jun 3, 2014

Debug error points to:

BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")

Sub is below:

Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer

[Code]....

View 2 Replies View Related

Saving As Stops Macro Working

Feb 6, 2008

i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?

View 10 Replies View Related

Workbook_SheetChange Evet Macro Not Working

Mar 5, 2008

I found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.

I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.

(i.e. =TEXT('Populator Tools'!$C$25+1,"mm-dd-yy") )

Am I completely off base with this assumption, or on the right track; and if I am on the right track how do I fix this so it works?

Code Below.

The Workbook_SheetChange Code Stored in This Workbook Module

View 9 Replies View Related

VBA Macro Not Working When Workbook Is Protected

Apr 23, 2008

I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:

View 14 Replies View Related

Macro In 2003 Is Not Working In 2000

Apr 25, 2008

I have constructed a macro, (with lots of help from the fine people on this site), and have distributed it to several people in the company. The macro was written in Excel 2003, and runs perfectly on all the machines running Excel 2003, but persons using Excel 2000 are getting a runtime error, variable not defined.

Is there a trick to getting a macro to run in both versions of Excel?

View 9 Replies View Related

Macro To Return To Where User Was Working

May 29, 2008

i have an excel doc. with three sheets...
i may start that macro on any sheet any cell
the 1st thing that macro does is to go to sheet1 and after running the whole code it calls another macro... macro2

now macro2 runs in an infinte loop and runs evry 20 sec. the problem is every 20 sec the cursor comes back to sheet3.. i want the macro to return to the place where the user was working.. best wud be that the user can work seem lessly...

View 9 Replies View Related

Adding Cell = Macro Not Working

Dec 9, 2008

I had a working macro before i added an extra row for another entry (No. of Hours spent). The entire data collection calculates the total price of a project (called PSR) in another cell underneath. The macro is suppose to move the entire data into another sheet to keep a history (in Sheet2). I've attached the file containing the workbook.

View 2 Replies View Related

Formula To Cell With Macro Not Working

Jan 26, 2009

Formula to cell with macro not working
I'm trying to enter this formula into cell in VBA macro.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved