Please help: Excel VBA move completed projects to next worksheet (big worksheet)

Question

Hello all,&nbsp;I am new to VBA coding and I have a big spreadsheet.&nbsp;I would like to cut and move an entire row to the worksheet named "Historic Sales" once I give the Value "completed" in Column Z "Status". It should delete the moved row out of the Worksheet, as it should only show in Historic Sales afterwards.&nbsp;I tried a couple codes online, but nothing seems to work. I don't understand VBA Coding much and cannot seem to figure it out.Could somebody please help me? I would be VERY thankful.

peiyezhu · Answer

https://techcommunity.microsoft.com/t5/excel/move-rows-to-sheet-with-drop-down-selection/m-p/3753037#M182713

celinaexcel · Answer

peiyezhu&nbsp; I looked into it, and also followed the Links in that thread, however it doesn't seem to be working. I am not sure I understand what Information I have to switch to make it work for my case.

peiyezhu · Answer

however it doesn't seem to be working.VBA Coding ?If with VBA,I don't know how to do more research.Maybe filter by keywords instead of moving will be more easier to process.

celinaexcel · Answer

I've been trying this Code, but when I then write Done into the Area by Z nothing is moving.Dim xRg As RangeDim xCell As RangeDim I As LongDim J As LongDim K As LongI = Worksheets("Overview").UsedRange.Rows.CountJ = Worksheets("Completed Projects").UsedRange.Rows.CountIf J = 1 ThenIf Application.WorksheetFunction.CountA(Worksheets("Completed Projects").UsedRange) = 0 Then J = 0End IfSet xRg = Worksheets("Overview").Range("Z4:Z" &amp; I)On Error Resume NextApplication.ScreenUpdating = FalseFor K = 1 To xRg.CountIf CStr(xRg(K).Value) = "Done" ThenxRg(K).EntireRow.Copy Destination:=Worksheets("Completed Projects").Range("A" &amp; J + 1)xRg(K).EntireRow.DeleteIf CStr(xRg(K).Value) = "Done" ThenK = K - 1End IfJ = J + 1End IfNextApplication.ScreenUpdating = TrueEnd Sub

patrick2788 · Answer

It's easy to find bad code online. I recommend studying Han's solution here for a similar request:https://techcommunity.microsoft.com/t5/excel/moving-rows-to-different-sheets-based-on-future-dates/m-p/2646721#M110300

Forum Discussion

Please help: Excel VBA move completed projects to next worksheet (big worksheet)

Share

Resources