Automatically transferring of rows between worksheets in Excel 2010

[nunny1982]

Afternoon All,

New joiner here so please be patient as I try and explain what I am trying to do! I currently have a work book with mulitple worksheets and on two of these I have identical tables. On one of them I have a risk register and I am trying to make it so that only open risks are on here. I would like to be able to make Excel automatically transfer the entire row to the other table when the 'open/closed' box is changed to 'closed'.

I have tried googling it and have found a few similar queries; however the code given does not seem to work in my case (maybe an older version of Excel - I'm not sure)

Is anyone able to help me with this at all?

Many thanks in advance for any help given.

 

[DutchDiggy]

Can you supply with more detail?
*Sheetnames
*Table names
*Ranges of the tables (how many columns, are there headers)
*open/closed boxes? Checkboxes? Or validation-cells?
*Do they have to go back and forward? I mean when closed and moved to other table, they can be opened again and move back to first table?

 

[nunny1982]

Hi DutchDiggy,

Sure - the details you have asked for are:

Sheetnames: Actions & Closed actions. In VBA they are Sheet2 and Sheet16
Table names: Action & Closed actions
Ranges of tables: 11 columns, each has a header (No., Category, Description, Piority, Status, Action Owner, Target Date, Days remaining, Open/Closed, Date Closed, Comments)
Open/closed boxes?: Priority, status, and action owner contain lists. It would be great if it would be possible to use a list in the Open/Closed box too but I am not sure if this would affect the transferring on information.
They only have to go one way. There will be no requirement to move them back.

Thanks.

 

[DutchDiggy]

Hi, this is what I came up with

I've put a validation in column I to choose between Open / Closed (REMEMBER: use Uppercase for first character, if you use 'open' in stead of 'Open' it'll corrupt)

when you apply Closed to a cell in column I it will automatic enter the closing date and time in column J, copy the row to the other sheet ("Closed actions") and delete it from sheet "Actions"

enter code in the sheet-section of the VBA (right click sheet tab --> code)

Private Sub Worksheet_selectionchange(ByVal Target As Range)


Dim LastOpenRow As Double
Dim WsAct As Worksheet
Dim OpenClosed As Range


Set WsAct = ThisWorkbook.Sheets("Actions")
LastOpenRow = WsAct.Range("A" & Rows.Count).End(xlUp).Row
Set OpenClosed = WsAct.Range("I2:I" & LastOpenRow)
For Each DC In OpenClosed 'add date to column J
    If DC.Value = "closed" Then DC.Offset(0, 1).Value = Now
Next DC
For Each OC In OpenClosed 'copy row to other sheet
    If OC.Value = "closed" Then OC.Offset(0, -8).Resize(1, 11).Copy Destination:=ThisWorkbook.Worksheets("Closed actions").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next OC
For Each DR In OpenClosed 'delete rows (separate FOR because else copy For will be corrupted)
    If DR.Value = "closed" Then DR.EntireRow.Delete
Next DR


End Sub

 

[DutchDiggy]

okay... just a little adjustment.. now it won't matter how closed is entered, as closed, Closed, CLOSED

Private Sub Worksheet_selectionchange(ByVal Target As Range)


Dim LastOpenRow As Double
Dim WsAct As Worksheet
Dim OpenClosed As Range


Set WsAct = ThisWorkbook.Sheets("Actions")
LastOpenRow = WsAct.Range("A" & Rows.Count).End(xlUp).Row
Set OpenClosed = WsAct.Range("I2:I" & LastOpenRow)
For Each DC In OpenClosed 'add date to column J
    If UCase(DC.Value) = "CLOSED" Then DC.Offset(0, 1).Value = Now
Next DC
For Each OC In OpenClosed 'copy row to other sheet
    If UCase(OC.Value) = "CLOSED" Then OC.Offset(0, -8).Resize(1, 11).Copy Destination:=ThisWorkbook.Worksheets("Closed actions").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next OC
For Each DR In OpenClosed 'delete rows (separate FOR because else copy For will be corrupted)
    If UCase(DR.Value) = "CLOSED" Then DR.EntireRow.Delete
Next DR


End Sub

 

[nunny1982]

Thanks for that DutchDiggy!

I have put in the code and, after a slight tweak as I realised that I hadn't mentioned an empty column before the table started (sorry about that) it is now working. Is it possible, though, to have the worksheet then auto-populate the next action number in the next row on the 'Actions' sheet so that the person going into the workbook does not have to look up the last one to be closed?

Thanks again. I do appreciate the help very much.

 

[DutchDiggy]

For that last request I need some info. What do you mean by auto-populate in the next row? To just allways have the Maximum No. Of the two sheets, added by 1, already in the most downward row in 'Actions' in column B? ( first columnof the table) so users never have to bother about what's the next action number? Just asking because actions won't ever be closed in a perticular order I guess. That can be done. Will check that in a while. I think the best option is to lock column B and have the number to be inserted the moment something is entered in any column in the next row (or lower, for some reason people sometimes want to skip a row)

 

[nunny1982]

The question in your post is exactly what I am trying to do please?

 

[nunny1982]

Sorry DutchDiggy - last question I promise!

I have added another tab and copied the table across, but this time I have added an extra column (before the open/closed column). I have tried and tried and tried to amend the code; however I am still getting the error: 'Runitime error '1004': Application-defined or object-defined error'

When I click on 'debug' it highlights the line of code: LastOpenRow = WsAct.Range("B" & Rows.Count).End(x1Up).Row

I am sure it is very simple once you know; however I cannot find out how to extend the range to include the extra column? I have already made the adjustment that it looks in the next column for 'closed' and the next column for inserting the date. It is just this bit I am struggling with?

 

[DutchDiggy]

I was planning to update the code for the automated numbering within the hour, but see your last question.

WsAct is a variable set in the beginning of the code. I guess you made a new tab and make it do exactly as your Actions tab.

to later understand the code better in the complete workbook, I'd change all codeline which contain WsAct to WsAct2 of any other shortcut name you'd like.

then change the--> SET WsAct2 = thisworkbook.sheets("Whatever name you gave the newest sheet")

### I also hope you've copied the code the corresponding sheet-code and didn't onky alter the code in "Actions"-sheet coding