form Button to clear-> copy and put formula back

[vilas desai]

Dear Sirs,

I have a form button in a sheet MasterList! when clicked it copies certain ranges from MasterList! to wsh Cab_Sch.
The code as below first clears the contents in Cab_Sch and then copies the values from MasterList!.
I have also a formula in col H ( and may be in I and J in future) and Drop down list in col M
I dont want the formula and drop down lists to be cleared . Only the values are to be cleared.
If for example, 4 rows are copied from MasterList! to Cab_Sch, the the formulas and dropdown lists are also to be copied in these four rows.
For the next click, if only two rows are copied from Masterlist! to cab_Sch then the formulas are copied in only two rows.

Please advise, Best regards
Vilas desai

Code: Select all

Sub UpdateCabSch()
     Dim r As Long
     Dim m As Long
     Dim t As Long
     Application.ScreenUpdating = False
     Worksheets("Cab_Sch").Range("A5:G50000,I5:V50000").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
                            
            'Device
             Range("D" & r).Copy
             Worksheets("Cab_Sch").Range("A" & t).PasteSpecial Paste:=xlPasteValues
                            
            'Device Tag
             Range("J" & r).Copy
             Worksheets("Cab_Sch").Range("B" & t).PasteSpecial Paste:=xlPasteValues
                            
            'Service Area
             Range("B" & r).Copy
             Worksheets("Cab_Sch").Range("C" & t).PasteSpecial Paste:=xlPasteValues
                            
             'Loop Number
             Range("H" & r).Copy
             Worksheets("Cab_Sch").Range("D" & t).PasteSpecial Paste:=xlPasteValues
                            
            'No of Wires
             Range("I" & r).Copy
             Worksheets("Cab_Sch").Range("E" & t).PasteSpecial Paste:=xlPasteValues
                            
            'IO Type
             Range("W" & r).Copy
             Worksheets("Cab_Sch").Range("G" & t).PasteSpecial Paste:=xlPasteValues
                            
            'Jn Box ID
             Range("K" & r).Copy
             Worksheets("Cab_Sch").Range("K" & t).PasteSpecial Paste:=xlPasteValues
                            
            'Cable Tag
             Range("M" & r).Copy
             Worksheets("Cab_Sch").Range("N" & t).PasteSpecial Paste:=xlPasteValues
                            
            'Marshalling Rack ID
             Range("N" & r).Copy
             Worksheets("Cab_Sch").Range("R" & t).PasteSpecial Paste:=xlPasteValues
                            
            'PLC Rack No
             Range("T" & r).Copy
             Worksheets("Cab_Sch").Range("V" & t).PasteSpecial Paste:=xlPasteValues
                            
         End If
     Next r
     Application.ScreenUpdating = True
End Sub

[HansV]

You could change the line

Code: Select all

     Worksheets("Cab_Sch").Range("A5:G50000,I5:V50000").Clear

to

Code: Select all

     Worksheets("Cab_Sch").Range("A5:G50000,I5:V50000").ClearContents

This will clear the contents of columns A:G and I:V but preserve formatting, including data validation.

[vilas desai]

Dear Hans,

Thanks you. The code does clear the contents but does not copy the formula. Also, in my request above

Quote "if for example, 4 rows are copied from MasterList! to Cab_Sch, the the formulas and dropdown lists are also to be copied in these four rows.
For the next click, if only two rows are copied from Masterlist! to cab_Sch then the formulas are copied in only two rows." Unquote

I mean to say that on the first click cell values from 4 rows are copied, but formulas are not copied, So, I do this manually in Cab_Sch for cols with formulas,
Now if MasterList has only 2 rows, then the 2 rows in Cab_Sch are cleared but the copied col is not cleared.

In order to make a copy of the formulas, should I configure the rows in Cab_Sch as a "Table"
Please advise
Best regards
Vilas Desai

[vilas desai]

On thinking more about this I feel that it is getting complicated. I feel that this is possible only if the formulas are written as a code. If this is complicated, please do not go further.
Regards
VD

[HansV]

I thought you already had a formula in column H of the Cab_Sch sheet.
It may well be possible to populate column H by code, but I'd need to know what to enter in this column.

[vilas desai]

The formula that I have in Col H is H5 =IF(OR($G5="AI",$G5="RTD",$G5="TC",$G5="SG",$G5="HSC",$G5="AO"),"Yes","No")

[HansV]

If you'd like to enter a formula in column H, insert the following just above End If:

Code: Select all

            ' Fill column H
            Worksheets("Cab_Sch").Range("H" & t).FormulaR1C1 = _
                "=IF(OR(RC7={""AI"",""RTD"",""TC"",""SG"",""HSC"",""AO""}),""Yes"",""No"")"

If you'd prefer to enter a calculated value in column H:

Code: Select all

            ' Fill column H
            Select Case Worksheets("Cab_Sch").Range("G" & t).Value
                Case "AI", "RTD", "TC", "SG", "HSC", "AO"
                    Worksheets("Cab_Sch").Range("H" & t).Value = "Yes"
                Case Else
                    Worksheets("Cab_Sch").Range("H" & t).Value = "No"
            End Select

[vilas desai]

That sounds perfect. However the one thing that still remains is when I go back from 4 rows of copy from MasterList to Cab_Sch to three rows on the next click, I see that 3 rows of data is copied but the 4th row still shows the result of the formula. It does not get cleared.

[HansV]

You can now change the line

Code: Select all

     Worksheets("Cab_Sch").Range("A5:G50000,I5:V50000").ClearContents

to

Code: Select all

     Worksheets("Cab_Sch").Range("A5:V50000").ClearContents

There is no need to exclude column H any more, since it will be filled using the new code.

[vilas desai]

Thank you, that is correct.