Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Automatically transferring of rows between worksheets in Excel 2010

This is a discussion on Automatically transferring of rows between worksheets in Excel 2010 within the Excel Questions forums, part of the Question Forums category; Afternoon All, New joiner here so please be patient as I try and explain what I am trying to do! ...

  1. #1
    New Member
    Join Date
    Oct 2013
    Posts
    15

    Default Automatically transferring of rows between worksheets in Excel 2010

    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.

  2. #2
    Board Regular DutchDiggy's Avatar
    Join Date
    Sep 2013
    Location
    Holland
    Posts
    211

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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?
    And yet he learned so little....

    NOTE (copy/pasted from DeBeuz) Sometimes I forget to replace the Dutch separator ";" -> "," and decimal point "," -> "." in my replies; Sorry.

  3. #3
    New Member
    Join Date
    Oct 2013
    Posts
    15

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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.

  4. #4
    Board Regular DutchDiggy's Avatar
    Join Date
    Sep 2013
    Location
    Holland
    Posts
    211

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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)

    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
    Last edited by DutchDiggy; Oct 29th, 2013 at 03:52 PM.
    And yet he learned so little....

    NOTE (copy/pasted from DeBeuz) Sometimes I forget to replace the Dutch separator ";" -> "," and decimal point "," -> "." in my replies; Sorry.

  5. #5
    Board Regular DutchDiggy's Avatar
    Join Date
    Sep 2013
    Location
    Holland
    Posts
    211

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

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

    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 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
    And yet he learned so little....

    NOTE (copy/pasted from DeBeuz) Sometimes I forget to replace the Dutch separator ";" -> "," and decimal point "," -> "." in my replies; Sorry.

  6. #6
    New Member
    Join Date
    Oct 2013
    Posts
    15

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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.

  7. #7
    Board Regular DutchDiggy's Avatar
    Join Date
    Sep 2013
    Location
    Holland
    Posts
    211

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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)
    And yet he learned so little....

    NOTE (copy/pasted from DeBeuz) Sometimes I forget to replace the Dutch separator ";" -> "," and decimal point "," -> "." in my replies; Sorry.

  8. #8
    New Member
    Join Date
    Oct 2013
    Posts
    15

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

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

  9. #9
    New Member
    Join Date
    Oct 2013
    Posts
    15

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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?

  10. #10
    Board Regular DutchDiggy's Avatar
    Join Date
    Sep 2013
    Location
    Holland
    Posts
    211

    Default Re: Automatically transferring of rows between worksheets in Excel 2010

    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
    And yet he learned so little....

    NOTE (copy/pasted from DeBeuz) Sometimes I forget to replace the Dutch separator ";" -> "," and decimal point "," -> "." in my replies; Sorry.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com