List Object - Only partial

[vilas desai]

Hi Moderators,

I want a help on this:
I have a list object with cols A:T in sheet Cab_Sch
the header is on Row 4 and values are in Row 5 and below. In also have a form button which when clicked copies ranges from a sheet MasterList to destination sheet Cab_Sch in the following cols: A:E and F:G and M in as many rows in Cab_Sch as in MasterList.

Code: Select all

Sub CopyData()
     Dim r As Long
     Dim m As Long
     Dim t As Long
     Application.ScreenUpdating = False
     Worksheets("Loop_Data").Range("5:50000").Clear
     t = 4
     m = Range("W" & Rows.Count).End(xlUp).Row
     For r = 5 To m
         If Range("I" & r).Value <> 0 Then
             t = t + 1
             
            worksheet.Masterlist. Range("H" & r).Copy _
                 Destination:=Worksheets("Cab_Sch").Range("A" & t)
           
         End If
     Next r
     Application.ScreenUpdating = True
End Sub

Other cols have either drop down validations or manually entered values or values based on formulas.
Question: Are the formulas / drop downs copied in the list object for all the rows that are copied in the sheet Cab_Sch?

Please advise
best regards
Vilas Desai

[Rudi]

The formulas and validation will be automatically extended to the bottom of the table as data is added to the table, whether the data is manually added or added by formula.

Question: In stead of looping through the range on the Masterlist, copying and pasting the records one by one, why not just filter the rows <> 0 and copy and paste the filtered set in one go for each of the column ranges indicated; A:E, F:G ... etc

[vilas desai]

Thanks, Rudi. Could you please send me the code for that.

[Rudi]

Try these.
Attached is also a copy of the sample file I used to create the macros. The Cab_Sch table contains a validation and calculation column too.
I have not created a macro button, but you can run the code from the VIEW tab, Macros (or from VBA)

This code below will clear the table and add the filtered records from the MasterList

Code: Select all

Sub FillClearedTable()
Dim lC As Long
    Application.ScreenUpdating = False
    lC = Sheets("MasterList").Range("A" & Rows.Count).End(xlUp).Row
    On Error Resume Next
    Sheets("Cab_Sch").ListObjects(1).DataBodyRange.Delete
    On Error GoTo 0
    With Sheets("MasterList")
        .Range("A1").AutoFilter Field:=9, Criteria1:="<>0"
        .Range(.Range("A2"), .Range("E" & lC)).SpecialCells(xlCellTypeVisible).Copy Sheets("Cab_Sch").Range("A5")
        .Range(.Range("F2"), .Range("G" & lC)).SpecialCells(xlCellTypeVisible).Copy Sheets("Cab_Sch").Range("F5")
        .Range(.Range("M2"), .Range("M" & lC)).SpecialCells(xlCellTypeVisible).Copy Sheets("Cab_Sch").Range("M5")
    End With
    Sheets("MasterList").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

This code will append the filtered data from MasterList to the end of the table

Code: Select all

Sub AppendTable()
Dim lSC As Long
Dim lDC As Long
    Application.ScreenUpdating = False
    lSC = Sheets("MasterList").Range("A" & Rows.Count).End(xlUp).Row
    lDC = Sheets("Cab_Sch").Range("A" & Rows.Count).End(xlUp).Row
    With Sheets("MasterList")
        .Range("A1").AutoFilter Field:=9, Criteria1:="<>0"
        .Range(.Range("A2"), .Range("E" & lSC)).SpecialCells(xlCellTypeVisible).Copy Sheets("Cab_Sch").Range("A" & lDC).Offset(1)
        .Range(.Range("F2"), .Range("G" & lSC)).SpecialCells(xlCellTypeVisible).Copy Sheets("Cab_Sch").Range("F" & lDC).Offset(1)
        .Range(.Range("M2"), .Range("M" & lSC)).SpecialCells(xlCellTypeVisible).Copy Sheets("Cab_Sch").Range("M" & lDC).Offset(1)
    End With
    Sheets("MasterList").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub