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.
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
Microsoft Excel
Last Comment
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
That will be due to the code later on in the VBA:
Your sheet was cleared because of the code:
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
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
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
'**** 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
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.
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.
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.
The post was reversed, sorry about that.
ASKER
Andrew, I'm good with what you originally sent me. It works great.... much appreciate your efforts and talents here.
B.
B.
Sheets("sheet2").Cells(1,1