asked on

Need assistance with a Macro

EE Pros,

I have a macro called sumup. It places data identified in the top of the WS to be placed on line 800 down in the WS. Every time I add a row, it screws up the alignment. I need the data to be placed on a different worksheet so I need an additional line (maybe Const Output_ new WS name.........), so to put the data output on another WS.

Thank you in advance.

B.

Sub sumup()
Const Input_Range = 5
'3
Const Output_Range = 800
'17
Const Output_CS = 7
'18
Const Output_CE = 10
Const Input_CM = 5

Dim O_R As Long
'Output Row
Dim I_R As Long
'Input Row
Dim Total As Double

'Total

'**** Clear the previous output ****
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
    'Column and Row Output
    Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range


'Where the checkmark is
I_R = Cells(Rows.Count, Input_CM).End(xlUp).Row
If I_R >= Input_Range Then
    'Input row
    For I = Input_Range To I_R
        With Cells(I, Input_CM)
            If .Value = "P" Then
                'Output results here
                Cells(O_R, Output_CS).Value = .Offset(0, 2).Value
                Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
                Cells(O_R, Output_CE).NumberFormatLocal = _
                        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
                
                If Not IsError(.Offset(0, 5).Value) Then Total = Total + .Offset(0, 5).Value
                O_R = O_R + 1
            End If
        End With
    Next
    Cells(Output_Range - 1, Output_CS).Value = "Total"
    With Cells(Output_Range - 1, Output_CE)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With
    'Else
    
    'MsgBox "Please select what you wanted! Thanks!"
End If

End Sub

Open in new window

AndrewSwingewood

Can you not just reference the destination sheet in the Cells() function or am I missing something:
Sheets("sheet2").Cells(1,1) = "Whatever value you require"

Bright01

ASKER

Andrew,

Thanks for jumping in. I think it may be that easy. I need to simply reference the other destination sheet where I want the output. But I don't know how to do that. Can you show me where that goes in the code I sent? Don't need all the lines just the place where I should put it. and do I include "Whatever value you require"............?

B.

ASKER CERTIFIED SOLUTION

AndrewSwingewood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

Almost..... I followed your directions and it did exactly what you thought. It outputted the data selected to the new WS. However, where in the code does it give me the ability to specify the Output Column (right now it's G and I'm trying to get it in "A") and the Output Row (right now it is 800 and I want it to be 3). When I change the

Const Output_Range = 800 to 3

and

Const Output_CS = 7 to 1

It deletes all the records on the original WS.

Any ideas on how to control the output Column and Row?

Thanks again for the help here.

B.

AndrewSwingewood

That will be due to the code later on in the VBA:

 Cells(Output_Range - 1, Output_CS).Value = "Total"
    With Cells(Output_Range - 1, Output_CE)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With

Open in new window

You will need to add the output sheet to this code too:

 sheets("Output_Dest").Cells(Output_Range - 1, Output_CS).Value = "Total"
    With sheets("Output_Dest").Cells(Output_Range - 1, Output_CE)
        .FormulaR1C1 = "=SUM(R[1]C:R[" & O_R & "]C)"
        .NumberFormatLocal = _
                "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"
    End With

Open in new window

You can then specify whatever output const you want.
Your sheet was cleared because of the code:

'**** Clear the previous output ****
O_R = sheets("Output_Dest").Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
    'Column and Row Output
    Range(sheets("Output_Dest").Cells(Output_Range, Output_CS), sheets("Output_Dest").Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation O_R = Output_Range

Open in new window

If this is to clear the output sheet, update the code to read:

'**** Clear the previous output ****
O_R = Cells(Rows.Count, Output_CS).End(xlUp).Row
If O_R >= Output_Range Then
    'Column and Row Output
    Range(Cells(Output_Range, Output_CS), Cells(O_R, Output_CE)).ClearContents
End If
'Column Explanation
O_R = Output_Range

Open in new window

Does this help?

Bright01

ASKER

Andrew,

I was with you up until the last point on "clearing the output sheet". I think the two are reversed in the example above (i.e. what I had before and what I needed to change it to). When I did change it, it actually prevented me from using the check boxes on the original input WS so I reverted back to the original way I had it. Also, it's confusing how you actually target the output. The code you advised me on on the first round of discussion accurately sent the selections to the new WS...but still down on line 800 in Column G. I can make that work... not as elegant as I'd like...but it does work.

B.

AndrewSwingewood

If you want to post the actual sheet and vba, I can modify the code and send it back to you to better explain the changes.

The post was reversed, sorry about that.

Bright01

ASKER

Andrew, I'm good with what you originally sent me. It works great.... much appreciate your efforts and talents here.

B.