Excel grouping then inserting rows and summing up....
 

Hi there,

I want to sort data using column Q by name order. Once these have
been sorted I want a macro to copy all the rows where the cell in Q
matches a first name string which should be able to be programmed into
the macro. I then want it cut and pasted to the end of the
spreadsheet i.e 5 rows after the end row.

One this has been done I would like the SUM formula entered into both
sections but to SUM the data in column S.

I know you guys would find this easy to complete however.

The reason I need the above to be completed is because the macro below
stops working when there is no names at all for my string below,

Sheets("Customer Orders").Select
Range("Q1").Select
Do While ActiveCell.Value < "John Smith"
ActiveCell.Offset(1, 0).Select
Loop

If there is no John Smith it stops working.........and doesnt move on
to the next part.

If someone could help with a macro as per my above request it will be
very dynamic and will help me a great deal.

THanks

Andrea

Excel grouping then inserting rows and summing up....
 

On May 30, 9:08 am, "
wrote:
matches a first name string which should be able to be programmed into
the macro.

What do you mean? Like the example you provided?
Do While ActiveCell.Value < "John Smith"

Or only on the first name.

Excel grouping then inserting rows and summing up....
 

the way you've got this set up:

Do While ActiveCell.Value < "John Smith"
ActiveCell.Offset(1, 0).Select
Loop

then if it can't find John Smith, then yes, it's going to bomb right
out. because you're not giving it any other option.
try something like:

dim myLastRow as long

myLastRow = worksheet.cells(5000,17).row 'finds the last value
'in
column Q (17)

Do While ActiveCell.row <mylastrow
if activecell.value<"John Smith" then
activecell.copy
myLastRow.offset(1,0).value.paste 'syntax might be wrong
else
'do nothing
end if
Loop


hth!
susan


On May 30, 10:08 am, "
wrote:
Hi there,

I want to sort data using column Q by name order. Once these have
been sorted I want a macro to copy all the rows where the cell in Q
matches a first name string which should be able to be programmed into
the macro. I then want it cut and pasted to the end of the
spreadsheet i.e 5 rows after the end row.

One this has been done I would like the SUM formula entered into both
sections but to SUM the data in column S.

I know you guys would find this easy to complete however.

The reason I need the above to be completed is because the macro below
stops working when there is no names at all for my string below,

Sheets("Customer Orders").Select
Range("Q1").Select
Do While ActiveCell.Value < "John Smith"
ActiveCell.Offset(1, 0).Select
Loop

If there is no John Smith it stops working.........and doesnt move on
to the next part.

If someone could help with a macro as per my above request it will be
very dynamic and will help me a great deal.

THanks

Andrea

Excel grouping then inserting rows and summing up....
 

typo & mistake!

myLastRow = worksheet.cells(5000,17).row

should be

myLastRow = worksheet.cells(5000.17).end(xlup).row

sorry
susan

On May 30, 11:30 am, Susan wrote:
the way you've got this set up:

Do While ActiveCell.Value < "John Smith"
ActiveCell.Offset(1, 0).Select
Loop

then if it can't find John Smith, then yes, it's going to bomb right
out. because you're not giving it any other option.
try something like:

dim myLastRow as long

myLastRow = worksheet.cells(5000,17).row 'finds the last value
'in
column Q (17)

Do While ActiveCell.row <mylastrow
if activecell.value<"John Smith" then
activecell.copy
myLastRow.offset(1,0).value.paste 'syntax might be wrong
else
'do nothing
end if
Loop

hth!
susan

On May 30, 10:08 am, "



wrote:
Hi there,

I want to sort data using column Q by name order. Once these have
been sorted I want a macro to copy all the rows where the cell in Q
matches a first name string which should be able to be programmed into
the macro. I then want it cut and pasted to the end of the
spreadsheet i.e 5 rows after the end row.

One this has been done I would like the SUM formula entered into both
sections but to SUM the data in column S.

I know you guys would find this easy to complete however.

The reason I need the above to be completed is because the macro below
stops working when there is no names at all for my string below,

Sheets("Customer Orders").Select
Range("Q1").Select
Do While ActiveCell.Value < "John Smith"
ActiveCell.Offset(1, 0).Select
Loop

If there is no John Smith it stops working.........and doesnt move on
to the next part.

If someone could help with a macro as per my above request it will be
very dynamic and will help me a great deal.

THanks

Andrea- Hide quoted text -

- Show quoted text -

Excel grouping then inserting rows and summing up....
 

See how this works.

Sub MoveNamesAround()

Dim WB As Workbook
Dim WS As Worksheet
Dim Rng As Range
Dim C As Range
Dim NumRows As Integer
Dim Counter As Integer
Dim FindCounter As Integer

Set WB = ActiveWorkbook
Set WS = WB.Worksheets("Customer Orders")

NumRows = WS.Range("Q65536").End(xlUp).Row + 5

WS.Range("Q2", "Q" & WS.Range("Q65536").End(xlDown).Row).Sort
Key1:=Range("Q2"), Order1:=xlAscending

Response = InputBox("Enter Customer Name", "Enter Customer Name")
If Response = "" Then End

Set Rng = WS.Range("Q1", "Q" & WS.Range("Q65536").End(xlUp).Row)

FindCounter = 1

With Rng
Set C = .Find(Response, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
Do
WS.Range("Q" & NumRows + FindCounter) = C.Value
C.Value = ""
Set C = .FindNext(C)
FindCounter = FindCounter + 1
Loop While Not C Is Nothing And Rng.Address < firstAddress
End If
End With

WS.Range("S" & WS.Range("S65536").End(xlUp).Row + 1).Formula = _
"=sum(S2:S" & WS.Range("S65536").End(xlUp).Row & ")"

End Sub