+ Reply to Thread
Results 1 to 9 of 9

Cut & paste selected cells, by pressing button, to first blank row in table

Hybrid View

Ejb123erok Cut & paste selected cells,... 04-29-2013, 03:26 AM
:) Sixthsense :) Re: Cut & paste selected... 04-29-2013, 03:38 AM
AndyLitch Re: Cut & paste selected... 04-29-2013, 03:47 AM
Ejb123erok Re: Cut & paste selected... 04-29-2013, 03:56 AM
:) Sixthsense :) Re: Cut & paste selected... 04-29-2013, 04:16 AM
AndyLitch Re: Cut & paste selected... 04-29-2013, 04:05 AM
AB33 Re: Cut & paste selected... 04-29-2013, 04:43 AM
Ejb123erok Re: Cut & paste selected... 04-29-2013, 06:54 AM
Ejb123erok Re: Cut & paste selected... 04-30-2013, 08:11 AM
  1. #1
    Registered User
    Join Date
    04-29-2013
    Location
    Uppsala
    MS-Off Ver
    Excel 2010
    Posts
    24

    Cut & paste selected cells, by pressing button, to first blank row in table

    Hi,

    My excel skills sort of ends at (simple) functions, pivot tables and recording macros and since I can't seem fix this issue with this limited excel skillset I'm in need of help.

    I've attached a sample workbook that doesn't look exactly as the original one but it resembles it and I hope that's enough to help me out.

    Anyways, what I want to do is this:
    1. Select one or several rows (always a full row, that is, from column A to D) in sheet1. For example selecting A2:D2.
    2. Press "Archive"
    3. By pressing Archive the selected data is cut and pasted to same columns in sheet 2 as well as placed in the blank rows in that sheet.

    I've tried solving this by creating a macro but I either get an error or it (the macro) keep replacing my previous addition to the table when I press the macro.

    I want to solve this as simple as possible because neither me or the users it's designed for have the skills to work out any furher issues nor do we need for more sophisticated
    solution than this.

    Hope I've provided all necessary information.

    //Ejb123Erok
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Try this...

    Sub MoveDataOnClick()
    Dim nEndRw As Long, wBase As Worksheet, wRes As Worksheet
    
    Set wBase = Sheets("Sheet1")
    Set wRes = Sheets("Sheet2")
    
    With wBase
        nEndRw = .Cells(Rows.Count, "B").End(xlUp).Row
        .Range("A2:D" & nEndRw).Copy wRes.Cells(Rows.Count, "A").End(xlUp)(2)
        .Range("B2:D" & nEndRw).ClearContents
    End With
    
    End Sub


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Put an activeX commandbutton on sheet1 and the following code in sheet 1 module

    Private Sub CommandButton1_Click()
        Archive Selection
        Selection.ClearContents
    End Sub
    
    Public Sub Archive(ByVal Target As Range)
    Dim LastRow As Long, iRows As Integer
    
        iRows = Target.Rows.Count
        
        LastRow = Sheet2.Range("A65536").End(xlUp).Row + 1
        Sheet2.Range("A" & LastRow & ":D" & LastRow + iRows - 1).Value = Target.Value
        
    End Sub
    This will archive the selected range to sheet2.
    Last edited by AndyLitch; 04-29-2013 at 03:50 AM.
    Elegant Simplicity............. Not Always

  4. #4
    Registered User
    Join Date
    04-29-2013
    Location
    Uppsala
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Thank you Sixthsense and Andy for the quick responses.

    Just one question for you each:
    Sixthsense: The code cut and paste the rows but it moves everything, not just he selected row.

    Andy: it just copies, what changes to the code to I need to do so that it removes it from sheet one (i.e. cuts and pastes the selected row).

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Then try this one...

    Sub MoveDataOnClick()
    
    With Selection
        .Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2)
        .ClearContents
    End With
    
    End Sub

  6. #6
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Hi EJ ......... i modified my code above to delete the selected range

    Private Sub CommandButton1_Click()
    Archive Selection
    Selection.ClearContents <--------------
    End Sub

  7. #7
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Sub MoveDataOnClick()
    Dim LR&,  ms As Worksheet
    Application.ScreenUpdating = False
    Set ms = Sheets("Sheet2")
    With Sheets("Sheet1")
        LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        .Range("A2:D" & LR).Cut ms.Cells(Rows.Count, "A").End(xlUp).Offset(1)
    End With
    Set ms = Nothing
    Application.ScreenUpdating = True
    End Sub

  8. #8
    Registered User
    Join Date
    04-29-2013
    Location
    Uppsala
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Many thanks for all the help this far. Before I put this a a "solved" issue I have a few additional things on the same workbook I would like to have some help with.
    Note: I'm using AndyLich's coding for the archive-button since I tried it first and it worked fine.

    Anyhow, when I archive one or several selected rows I want the archived row(s) to be deleted from sheet1, not just the content, because a user might want to archive any row and not necessary the first or last one, thus I don't want empty rows in the middle of the table.

    Furthermore, if possible, it would be great if the archive (sheet2) was protected from any editing. However, If I protect the sheet the archive-button doesn't work (obviously). Is it possible to incorporate that into the buttons coding so when pressing "archive" the archive sheet2 gets temporarily unprotected during the "cut and paste" process.

    I feels like I'm making someone else do my job but I really can't do this on my own!

  9. #9
    Registered User
    Join Date
    04-29-2013
    Location
    Uppsala
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Cut & paste selected cells, by pressing button, to first blank row in table

    Sorry for bumping but I think this thread has been forgotten and I still have issues with what was brought up in my last post. If anything was unclear with it I'm happy to explain in more detail regarding what I was requesting help.

+ 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