Combine Multiple Macros
 

I have two Macros:

Dim sh2 As Worksheet, finalrow As Long
Dim i As Long, lastrow As Long
Set sh2 = Sheets("123-2205587527")
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 3).Value = "2205587527" Then
lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).EntireRow.Copy Destination:=sh2.Cells(lastrow + 1, 1)


Range("M2").Select
FIRST_ROW = ActiveCell.Row
Selection.End(xlDown).Select
LAST_ROW = ActiveCell.Row
Selection.Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 & "]C:R[-2]C)"
Range("M18").Select

I want to combine them so I can have the consolidated Macro look up a selected sheet within the workbook, find the selected column with values in it, get a SUM of that column, return value of the SUM to another sheet within the same workbook titled "Totals."

The goal is to be able to gather the totals of many sheets into one for reporting purposes.

Is this possible?

Any/all assistance will be greatly appreciated.

Thank you

Combine Multiple Macros
 

On Wednesday, 30 October 2013 03:53:08 UTC+11, frankjh19701 wrote:
I have two Macros:



Dim sh2 As Worksheet, finalrow As Long

Dim i As Long, lastrow As Long

Set sh2 = Sheets("123-2205587527")

finalrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To finalrow

If Cells(i, 3).Value = "2205587527" Then

lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row

Cells(i, 1).EntireRow.Copy Destination:=sh2.Cells(lastrow + 1, 1)





Range("M2").Select

FIRST_ROW = ActiveCell.Row

Selection.End(xlDown).Select

LAST_ROW = ActiveCell.Row

Selection.Offset(2, 0).Select

ActiveCell.FormulaR1C1 = "=SUM(R[" & FIRST_ROW - LAST_ROW - 2 &

"]C:R[-2]C)"

Range("M18").Select



I want to combine them so I can have the consolidated Macro look up a

selected sheet within the workbook, find the selected column with values

in it, get a SUM of that column, return value of the SUM to another

sheet within the same workbook titled "Totals."



The goal is to be able to gather the totals of many sheets into one for

reporting purposes.



Is this possible?



Any/all assistance will be greatly appreciated.



Thank you









--

frankjh19701

It's not too easy to follow what you want to do, but I think you could probably do it without using macros - use instead formulas and built-in Excel functions. If you want to sum only those values in a column that occur in a row with a specified value in a certain other column, you can use the SUMIF function rather than getting a macro to copy those rows so that they're adjacent. For example,
=SUMIF(C:C, "2205587527", B:B)
adds those entries in column B that occur in a row having "2205587527" in column C. You could put this formula in a cell somewhere on the relevant sheet, or else put it straight onto a Totals sheet, though then you have to modify the formula to include the name of the sheet you're referring to - so if you're doing the summing in sheet 123-2205587527, you'd change the formula in the Totals sheet to
= SUMIF('123-2205587527'!C:C, "2205587527", '123-2205587527'!B:B)
You could put one formula like this on the Totals sheet corresponding to each sheet whose values you want to sum.

Am I interpreting your problem requirements correctly?

Howard

I want to be able to "Clear" the sheet and re-use the workbook each month. If I use Formulas instead of Macros, the formulas would be lost when I "Cleared" the sheet. I think I figured it out. I'm using a Macro to Sum a specific column and then send the value to a specific sheet/column/row.

This macro gets the information
Sub Auto_Open()
'
' Auto_Open Macro
'Sub Sorting()

Dim sh2 As Worksheet, finalrow As Long
Dim i As Long, lastrow As Long
Set sh2 = Sheets("123-Johnson")
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 3).Value = "Johnson" Then
lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).EntireRow.Copy Destination:=sh2.Cells(lastrow + 1, 1)

End If
Next i


This Macro gets the SUM and sends the value to another sheet

Sub Test2()
Dim LRow As Long

With Sheets("123-Johnson")
LRow = .Cells(Rows.Count, "M").End(xlUp).Row
Sheets("Monthly Totals").Range("B4") = _
WorksheetFunction.Sum(.Range("M:M"))
End With

This Macro "Clears" the sheet of the values.

Sub clearcellsonsheets()
For i = sh2 To sh79
ms = "123-Johnson" & i
Sheets("123-Johnson").Range("A2:AH100000").ClearContents
Next I

And then I repeat the process for the next month.

Thank you for you efforts.