Copy paste Row Range(Dynamic) and paste based on C

2019-08-19 14:23发布

问题:

Copy paste Row Range(Dynamic) and paste based on Column value equal to X else SKIP.

Have Condition in Row11 ,i.e. I11:T11, if I11 contains TEST, skip to next column until it is not equal to TEST, then select range in next row from that cell for example M12:T12 and while pasting check the values in column H, if it contains X then only paste else skip. Data in Column H will start from H12 till last row of H column.

Please Refer attached screenshot and follow below guidelines :

  • Have a Flag value in Column H, if it contains X then it should go and check I11:T11, in which if it founds member <> TEST, then copyrange from that cell , in this case it is L12:T12, and paste only in those rows where value in column H is X, it should not paste in rows where column H contains Y.

  • Further values in column H will be dynamic, i.e. above logic should consider H13 till last row in column H. Alse there are extra columns starting from V, anyhow the values in V12:X12 and Z12:AB12 should get copied based on column H logic flag value.

  • FYI, there are already values in rows were H column value is Y hence cannot clear those values.

Want to achieve this on a button click using a VBA code.

Adding Code, but it limits to the fixed column H values and Fixed row values.

Sub CopyOnCondition1()    
     Dim sh1 As Worksheet, c As Range    
     Set sh1 = Worksheets("SheetNameHere") 'change the sheetname

     For Each cel In sh1.Range("I11:T11")    
        If Not cel.Value = "TEST" Then    
            sh1.Range(Cells(12, cel.Column), Cells(12, 20)).Copy
            sh1.Range(Cells(13, cel.Column), Cells(24, 20)).PasteSpecial xlPasteFormulas    
        End If    
     Next

     For Each cel In sh1.Range("H13:H24")    
        If cel.Value = "Y" Then sh1.Range("I" & cel.Row & ":T" & cel.Row).ClearContents    
     Next        
End Sub

enter image description here