Solved Using macro to move row from one sheet to another

August 11, 2020 at 07:26:32
Specs: Windows 10
Hello,

I hope somebody can help in excel spreadsheet.

I would like to use functionality to move row based on value. Basically when it will be as "closed" then move all row to another sheet.

It is starting from A11 and each row is ending in P11

I hope somebody will help. THanks a lot

message edited by pajdulacek


See More: Using macro to move row from one sheet to another


✔ Best Answer
August 12, 2020 at 07:50:41
Try the following code. Note that you used the lower case version of "closed" so that is what the code is written to look for. If you are really using Closed as your trigger word, then edit the code to match.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nxtRow As Long

'Determine if Column A was changed to "closed"
  If Target.Column = 1 _
     And Target.Row > 10 _
     And Target = "closed" Then

'If yes, determine next open row on Completed Tasks and move row
     Application.EnableEvents = False
      With Sheets("Completed Tasks")
         nxtRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
           Target.EntireRow.Copy _
             Destination:=.Range("A" & nxtRow)
           Target.EntireRow.Delete
      End With
      
   End If
 
 Application.EnableEvents = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
August 11, 2020 at 11:57:20
Are you asking for this happen to a number of rows at one time, such as a scan of an existing data set or are you asking for the each individual row to move as soon as a cell is changed to "closed"?

I'm also a little confused by your comment "starting from A11 and each row is ending in P11"

How can "each row end in P11"? P11 is a single cell.

Please keep in mind that we can't see your worksheet from where we are sitting so we don't know how your data is laid out. You need to be specific in describing what your data looks like and what you want this macro to do so that we can help.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
August 11, 2020 at 12:08:09
Hello DerbyDad03,

I am asking for each individual row to move as soon as a cell is changed to "closed".

I am sorry I did not describe it perfectly. The rows with data are starting from 11, and Status where I have drop down list made with status is in A column. Each row contain data up to P column. I would like to build function to move row to another sheet ( called "completed tasks" ) in same workbook, just next sheet.

The Data contains information such as status, date, reference, etc. basically all typos.

If you can help me with that it will be amazing. Thanks a lot for any help


Reply ↓  Report •

#3
August 11, 2020 at 12:19:35
please see workbook I am talking about in link below:

https://drive.google.com/file/d/1io...

What I would like to do then is, when status will be changed to "Closed", then row will be moved to another sheet and it will be stored there for traceability.


Reply ↓  Report •

Related Solutions

#4
August 12, 2020 at 07:50:41
✔ Best Answer
Try the following code. Note that you used the lower case version of "closed" so that is what the code is written to look for. If you are really using Closed as your trigger word, then edit the code to match.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim nxtRow As Long

'Determine if Column A was changed to "closed"
  If Target.Column = 1 _
     And Target.Row > 10 _
     And Target = "closed" Then

'If yes, determine next open row on Completed Tasks and move row
     Application.EnableEvents = False
      With Sheets("Completed Tasks")
         nxtRow = .Range("A" & Rows.Count).End(xlUp).Row + 1
           Target.EntireRow.Copy _
             Destination:=.Range("A" & nxtRow)
           Target.EntireRow.Delete
      End With
      
   End If
 
 Application.EnableEvents = True
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#5
August 12, 2020 at 08:30:21
Hello,

It works perfectly! Thanks a lot for your help, appreciated!


Reply ↓  Report •

Ask Question