Results 1 to 2 of 2

Duplicating rows to another sheet with update button problem

Threaded View

  1. #1
    Registered User
    Join Date
    06-04-2014
    Posts
    6

    Duplicating rows to another sheet with update button problem

    I know this similar request has been made before on other posts but I can't find a solution to this particular issue.

    I am using the below code to copy row data from one sheet to another based on criteria in a column. Easy enough. I have an "update" button to call the macro and it works well, save two features.

    If there is missing info in a row I wanted a box to pop up "Please fill out row" or something like that. Right now an error box with "400" displays.

    Second, if I delete the first row on the "Master" sheet and run the macro, it won't reflect that deletion on the sub sheet. This only happens for the first row because I don't know how to program a "clear contents" that deals with the change/update. I have also attached a sheet.

    Bonus -- how could I make this code refer to table data?

    Sub ToSet()
    
    Dim rT As Range 'source data
    Dim rD As Range 'data minus headers
    Dim wS As Worksheet 'source sheet
    Dim wT As Worksheet 'target sheet
    Dim wsRow As Long
    Dim b As Boolean
    
    Set wS = Worksheets("MASTER")
    With wS
        .AutoFilterMode = False
        Set rT = .Range("A1", .Cells(1, Columns.Count).End(xlToLeft)) 'data width
        Set rT = rT.Resize(.Cells(.Rows.Count, 3).End(xlUp).Row) 'data height including header
        Set rD = rT.Offset(1).Resize(rT.Rows.Count - 1) 'data height wo header
        
    End With
    
    
    For Each wT In Worksheets
        rT.AutoFilter Field:=7, Criteria1:=wT.Name
        On Error Resume Next
        b = rD.SpecialCells(xlCellTypeVisible).Count > 1 'check if data for this sheet
        
        On Error GoTo 0
        If b Then 'data exists, continue
            wT.Range("A2", wT.Cells.SpecialCells(xlLastCell)).Clear 'clear everything below header row
    ' This next line may not be necessary if new data always placed at row 2
            wsRow = wT.Cells(Rows.Count, 2).End(xlUp).Row + 1 'find 1st empty row
            rD.SpecialCells(xlCellTypeVisible).Copy wT.Range("A" & wsRow) 'copy over data
        End If
    Next wT
    wS.AutoFilterMode = False
    Application.CutCopyMode = False
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 06-29-2012, 10:57 AM
  2. Creating a control button on a front sheet to update another sheet
    By 2steve40 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2010, 06:47 AM
  3. update an overview sheet using a command button
    By johnpaulhenning in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-26-2008, 04:25 PM
  4. macro button to update excel sheet
    By mikeydaman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-22-2008, 03:22 AM
  5. Replies: 0
    Last Post: 07-13-2006, 07:45 PM

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