Macro to move row to another worksheet

[emjeff99]

Hi! I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.

Is this even possible???

As always, thanks! You all are so awesome!!!

 

[Smitty]

But this won't delete the empty rows. Any medicament for that?

No, it won't, but it will re-order everything so you don't have to worry about it. Unless you want to reset the used range as well.

And if i have 100 000 rows then 2 loops will kill everything. Even Chuck Norris :D

Why can 't you sort first, then cut the block that you need, instead of trying to loop through 100k rows?

 

[lall]

No, it won't, but it will re-order everything so you don't have to worry about it. Unless you want to reset the used range as well.



Why can 't you sort first, then cut the block that you need, instead of trying to loop through 100k rows?

XL can't handle so large amount of data. I can't use even simple filter. So imagine what cutting/coping pasting will look like..
Absolutely every operation i make has to be row by row..or something like that.

If i just sort the data, it will leave empty rows behind.
Let's say i have 100 000 rows. And 90% of them are 0 values. It means necessary but later (so i put them on hold on to sheet5). And i get 10 000 rows that i need. Next move will be that i save the sheet2. Now if there are 90 000 empty rows, the file is 15mb big :D
Without that, few hundred kilobytes only.

 

[jmthompson]

I never tested this, but why not use the loop I posted to find and delete empty rows?

Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, "R").Value) = "" Then
Cells(i, "A").EntireRow.Delete
End If
Next i

 

[lall]

I never tested this, but why not use the loop I posted to find and delete empty rows?

Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 2 Step -1
If (Cells(i, "R").Value) = "" Then
Cells(i, "A").EntireRow.Delete
End If
Next i

For now, it seems to be the only way yeah..
So i'll probably use it until i can work out faster way.

Thank you =)

 

[Kyle310]

Right-click the Excel icon (next to the File menu). That will open the ThisWorkbook module. Paste the code in the new window that opens on the right.

The code will run automatically whenever the changes you wanted happen in either sheet. If you enter "C" in C4:C200 in the To Do sheet, the row will be cut and pasted into the Completed sheet. If you delete the "C" in that sheet it will be cut back.

That sounds perfect for what I'm looking for too!
The difference is that I would like the entire row to move to sheet 3 when ANY date is put into column AB.

You rock!

 

[Dimi V]

Hi! I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.

Is this even possible???

As always, thanks! You all are so awesome!!!

I have a very similar situation as the very 1st post. This is my 1st Post and am only new here and to excel and have only just learnt over the last couple days how much you can actually do with this program I really want to learn more now...

[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]I have a workbook with two worksheets in it also. The first “UnPaid” (UnPaid Bills) and the second “Paid". In both worksheets I have the same header (Row 1), I have Suppliers and other info starting in row 2 - 200. [/FONT][FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]
[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]What I'd like to have happen is when I put a receipt number (letter and/or number) in column E (Receipt # column), it moves the entire row to the “Paid" worksheet, deleting it and the blank row from the “UnPaid" worksheet. Then if I remove the receipt number from the “Paid” worksheet due to accidentally inputting an entry, it moves it back to the “UnPaid” worksheet.[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]
[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]Also if Possible a Macro to sort it by due date (column B), this I can do by pressing the sort button if asking to much.[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]
[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]I mainly need the first function.[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]
[/FONT]
[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]Thanks in advance

[/URL][/IMG][FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]
[/FONT][FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]

[/FONT][/FONT]

 

[Smitty]

Welcome to the Board!

I'd keep your invoice detail in a single worksheet and indicate there if it's paid/unpaid. Then use Pivot Tables to summarize if invoices are paid or not.

That way your data stays intact and you just use the Pivot Tables for summaries.

 

[bidoman]

Heeelllooo MREXCEL forum family,

Though i understand that this may be repetitive, i too am in search for some insight on "how to macro my row to another worksheet";

however in my case its "how to move more than one row of data and not to record blank rows or overlapping data (deletion of previous store data)."

Currently using Excel2010, NOT experienced using VBA, Through trial and error was able to utilize Bill Jelen's Podcast 1505&1808 VBA, but cannot store more than one row.

http://postimg.org/image/6ejm1ywdf/

http://postimg.org/image/xqufwqimj/

Sub NextInvoice()
    Range("F4").Value = Range("F4").Value + 1
    Range("A15:E22").ClearContents
End Sub


Sub SaveInvWithNewName()
    Dim NewFN As Variant
    PostToRegister
    ' Copy Invoice to a new workbook
    ActiveSheet.Copy
    NewFN = "C:\Users\StuffofLegend\Desktop\TEST ARCHIVE\Inv" & Range("F4").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub


Sub PostToRegister()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("invoice")
    Set WS2 = Worksheets("register")
    
    ' Figure out which row is the right row
    nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' Write the important values to register
    WS2.Cells(nextrow, 1).Resize(1, 7).Value = Array(WS1.Range("F3"), _
        WS1.Range("F4"), WS1.Range("B15"), WS1.Range("C15"), _
        WS1.Range("A15"), WS1.Range("E15"), WS1.Range("F15"))


End Sub

Moving on from manual written invoices to the digital; Any help would be greatly appreciated!

 

[Dimi V]

Welcome to the Board!

I'd keep your invoice detail in a single worksheet and indicate there if it's paid/unpaid. Then use Pivot Tables to summarize if invoices are paid or not.

That way your data stays intact and you just use the Pivot Tables for summaries.

Thanks for the suggestion Smitty.

So I have 5-10 bills/invoices coming in per day with anything from 7 to 30 days to pay them. Im really just using this as a register I can keep track of upcoming bills/invoices I need to pay each day, then once payed, record the receipt # and have it on file for easy access.

I don't really need to see anything else other than which are UnPaid and which are Paid but kept separate to limit the confusion of the large amount of entries.

Would the code from the first post work if changed a little for my situation and if so can anyone help with the code?

 

[Dimi V]

See if this does what you want:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range
Set rng = Target.Parent.Range("C4:C200")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
Select Case Target.Text
Case "C"
Target.EntireRow.Cut Sheets("Completed").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Case Is = ""
Target.EntireRow.Cut Sheets("To Do").Cells(Rows.Count, "A").End(xlUp).Offset(1)
End Select
End Sub

HTH,

So I had a look at Pivot Tables and while I can see that they are great, they are definitely not what I need.

The "UnPaid" worksheet will be the main worksheet I will be using and looking at, The "Paid" worksheet is just to look back on once in a while to make sure an invoice has been received and paid. So, as the invoice is paid and receipt number recorded, it would move entire row to "Paid" worksheet

I honestly just need something like the code above just with those little changes. Instead of a "C" in column C, mine would be a random Alphanumerical figure in Column E (so anything other than a blank cell). Only other difference is instead on "Completed" and "To Do" worksheets, mine will be "Paid" and "UnPaid".

Please somebody help me!!!!!, I have been trying to work this out for over a week now, I have even bought a book on VBA programming to see if I can learn how to (its over 300 pages, so this could take me while).