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

Bright01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndrewSwingewoodCommented:
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"
0
Bright01Author Commented:
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.
0
AndrewSwingewoodCommented:
I would just add the sheet reference into the output Cells() lines.  Assuming your sheet output worksheet is called Output_dest then the code would be modified to read:
sheets("Output_Dest").Cells(O_R, Output_CS).Value = .Offset(0, 2).Value
sheets("Output_Dest").Cells(O_R, Output_CE).Formula = "=" & .Offset(0, 5).Address
sheets("Output_Dest").Cells(O_R, Output_CE).NumberFormatLocal = _
                        "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""??_);_(@_)"

Open in new window


Does this clarify things?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Bright01Author Commented:
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.
0
AndrewSwingewoodCommented:
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?
0
Bright01Author Commented:
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.
0
AndrewSwingewoodCommented:
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.
0
Bright01Author Commented:
Andrew,  I'm good with what you originally sent me.  It works great.... much appreciate your efforts and talents here.

B.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.