+ Reply to Thread
Results 1 to 6 of 6

i need help!!!!!!!!!!!!!!!!

  1. #1
    Registered User
    Join Date
    02-25-2005
    Posts
    3

    i need help!!!!!!!!!!!!!!!!

    Help!! How do I move completed tasks from one worksheet to another worksheet in the same workbook? Specifically, I have a sheet that shows what the status of certain projects are. When they are completed I would like to be able to just have the managers type completed in a cell and have all that infomation in that particular row move to a second worksheet that only shows the completed tasks. The first worksheet would be what tasks are incomplete and the second would be completed. Please keep in mind I am an idiot when it comes to anthing above basic formulas

  2. #2
    Forum Contributor
    Join Date
    01-11-2004
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    388
    Hi!

    With a bit of patience, effort and VBA it can be done fairly painlessly!

    Try this on a workbook you have both saved and renamed (so that you don't accidentally save a changed copy on top of the backup).

    The following is a short macro which looks down Column K of Sheet1 and, when it finds the word "done" copies that row onto Sheet2 at the bottom of a list there. After that, it deletes the row in question from Sheet1 and closes up the gap.


    Dim i As Long
    Dim wks1 As Worksheet, wks2 As Worksheet
    Dim LastRow1 As Long, LastRow2 As Long

    Set wks1 = Worksheets("sheet1")
    Set wks2 = Worksheets("sheet2")

    LastRow1 = wks1.Cells(Rows.Count, "A").End(xlUp).Row
    LastRow2 = wks1.Cells(Rows.Count, "A").End(xlUp).Row

    For i = LastRow1 to 1 Step -1
    If wks1.Range("K" & i) = "done" Then
    wks1.Cells(i, 1).EntireRow.Copy Destination _
    :=wks2.Cells(LastRow2, 1)
    LastRow2 = LastRow2 + 1
    wks1.Rows(i).Delete
    End If
    Next i

    Doubtless you will need to change a number of descriptive words (e.g. sheet names). No problem: we'll do that later.

    Now to "install" this.

    Easiest way I know is to record an empty macro and then copy and paste the above code into it. (I fancy calling it a macro paste sandwich...)

    To record a macro: Tools > Macro > Record new macro (on the main menu).
    In the box which pops up, give it a name (mine is called Weed...) and give it a keyboard shortcut (say, w). Close the box with OK.

    We now need to stop recording. Tools > Macro > Stop recording.

    Now to put the paste oin the sandwich: Alt + F8 will bring up the macro box again. Make sure Weed is selected and then click Edit.

    This will show you an "empty" macro. Paste into this immediately above the last line (End sub) the code which I gave you above.

    Now change anything which needs changing. Put in your own sheet names wherever it says sheet1 or sheet2. Change the "K" in Range("K" & i) so that it matches the column where you are going to "sign-off" jobs as completed.
    Change the word "done" to whatever code/number/word you are going to use.

    Save the edited macro when you are finished.

    When you want to try it out, put "done" in a couple of appropriate places. Press Ctrl +w. They should have transferred to sheet2. If you then do some more, they will be appended to the list on sheet2.

    If there are no "done" jobs, nothing happens: quite rightly.
    The only anxiety I have is that, to simplify it, I've used a way of identifying the last row on the sheet which will not react kindly if you decide to delete a row from sheet2 (e.g. to put it back on sheet1). You could end up with the odd blank row: untidy.

    Alf

  3. #3
    Registered User
    Join Date
    02-25-2005
    Posts
    4
    I am a newbie, and also facing the problem.
    I am stuck at the start.Sorry

    I am getting error Object required for "Rows", "Range". How can i set those. Can u give me the idea. I am not using a macro but writting a code for the same proc.

    Thanks

  4. #4
    Registered User
    Join Date
    02-25-2005
    Posts
    4
    I would like to select only one specific column and paste it into new sheet.

    I used the above code, but it pastes all the columns.
    Kindly help as i am in a breakthrough

    Thanks

  5. #5
    Forum Contributor
    Join Date
    06-10-2004
    Posts
    121
    Quote Originally Posted by kdalton1964
    Please keep in mind I am an idiot when it comes to anthing above basic formulas
    The simplest solution is to link the cells on different sheets, combined with a conditional formula.

    Basically this will test if, for example, the word "done" is typed in the appropriate cell by your managers, and if it is, will display the information on the second sheet, otherwise the cells on the second sheet will be blank.

    Lets assume you have 20 rows of 8 columns on your "status" worksheet (sheet1), your data starts in cell A6, and column H is where your managers enter "done"

    In the second worksheet enter in the first corresponding datacell ie A6 the following:-

    =IF(Sheet1!$H6="done",Sheet1!A6,"")

    Copy this formula to the rest of the data range (A6:H26) on your second sheet.


    HTH

  6. #6
    Forum Contributor
    Join Date
    06-10-2004
    Posts
    121
    Quote Originally Posted by sachinmk
    I would like to select only one specific column and paste it into new sheet
    In your case use the method I previously described but use the following formula in one column only of sheet 2:-

    eg =IF(Sheet1!$H6="done",Sheet1!$E6,"")

    This will display in sheet 2 the information from column E sheet 1, based on "done" being entered in column H on sheet 1.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1