A bit of logic help needed. 1

[stillwillyboy]

In a sales report showing invoices, debits, credits and related numbers and dollar amounts, I need to move the debits and credits to the bottom of the sheet and then delete the empty row that held the debit and/or credit. I can get the item moved, but then the cell (row) that received the pasting becomes the active cell and my loop gets messed up. After pasting, how do I get back to the next cell to see if it has a debit or credit? My code so far is below.

TIA,

Bill

Sub CutAndPasteDebitsAndCreditsToBottomOfBranchSheet()
Application.StatusBar = "Moving Debits and Credits"
Application.ScreenUpdating = False
Range("A1").Select
Do
If ActiveCell.Offset(0, 1) = "Credit Memo" Then
ActiveCell.EntireRow.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Formula = "Grand Total"
Range("A1").Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 

[SkipVought]

Hi,

What's the purpose of moving debis & credits to the bottom of the sheet?

By the BOTTOM of the sheet, do you mean row 65,536, or the bottom of some range?

Skip,

_[red][/red]

 

[stillwillyboy]

Just to break them out from the invoices so the supervisors for each shop can read their info easier. Also, with the DM's and CM's up top, they add and/or subtract from the totals for each customer. When this happens, then the shop super's sales figures won't match mine (before adjustments).

I didn't mean bottom of sheet. Should have said bottom of information of invoices.

 

[SkipVought]

Why not have a hidden column with a sequence number that you could SORT to get it the way you want. I try to avoid cut, paste and delete like the plague.

Also, you can create space between parts of your sheet by varying row height rather than inserting empty rows which screws up various Excel features like SORT and FILTER.

Skip,

_[red][/red]

 

[PHV]

Replace this:
ActiveCell.EntireRow.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
with only this:
ActiveCell.EntireRow.Copy Range("A65536").End(xlUp).Offset(1, 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[stillwillyboy]

PHV and Skip,

Got it working. Thanks.

Skip, I agree with you on the cutting and pasting, but given that the amount of info on each sheet won't be too big, I think it'll do it here.

I created three proc's. One to copy the Credits, one to copy the Debits and then one to delete the Credits and Debits from the rows above from where they were copied. I then called all three for each different sales branch. Maybe not the best coding, but it seems to work.

Thanks again for all the help. Past, present and future!

Bill