Results 1 to 7 of 7

Removing Rows from one spreadsheet to another

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-20-2013
    Location
    Ann Arbor, MI
    MS-Off Ver
    Excel 2010
    Posts
    255

    Removing Rows from one spreadsheet to another

    Hello,

    I posted a forum about this before (http://www.excelforum.com/excel-prog...-new-post.html), and the code I was using worked for a while, but now i'm running into problems.

    Private Sub CommandButton1_Click()
    
    Dim LR As Long, ws As Worksheet, ms, Qs
    On Error Resume Next
    Set ws = Sheets("Current Contractors")
    Set ms = Sheets("History Contractors")
    'Set Qm = ms.ListObjects("Table3")
    Set Qs = ws.ListObjects("Table2")
    Application.ScreenUpdating = False
        With Qs
        .AutoFilterMode = False
            LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        With ws.Range("A1:H" & Cells.Find("*", , , , xlByRows, xlPrevious).Row)
            
            ms.Cells(1, 1) = "Today's Date"
            ms.Cells(1, 2) = "Purchase Order Date"
            ms.Cells(1, 3) = "Contractor"
            ms.Cells(1, 4) = "Equipment"
            ms.Cells(1, 5) = "Description"
            ms.Cells(1, 6) = "Expected Time of Completion"
            ms.Cells(1, 7) = "Completed? y or n"
            ms.Cells(1, 8) = "Time of Completion"
            
           .AutoFilter 7, "y"
           .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy
            ms.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
           .AutoFilter
           .Application.CutCopyMode = 0
           
            
        End With
        End With
        
        Application.ScreenUpdating = True
        
    End Sub
    This code is working partially: It pastes the rows into the History sheet, but it doesn't always remove them from the Current sheet. I'm not sure if it has to do with being in tables or not. I also have another forum (http://www.excelforum.com/excel-prog...76#post3261576) about how my tables aren't expanding with new data.

    The workbook is posted.
    Any ideas?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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