Replace values in a specific column and row VBA

[Tresor]

Hello everyone!

Hope you are great.

I am trying (with no success) to filter for a specific value in a table and replace the column number 4 with the data in a differen sheet. I copy all the data in the first row and I don´t know how to paste it in the specific row. The code that I am using is the following:

Sub copia()
    
    Application.ScreenUpdating = False
    
    Dim hotel As String
    
    hotel = ActiveSheet.Name
         
    Sheets("discounts").Select
    ActiveSheet.ListObjects("discount").Range.AutoFilter Field:=1, Criteria1:=hotel
    
    Sheets(hotel).Select
    Range("d43:d50").Select
    Selection.Copy
    
    Sheets("discounts").Select
    ActiveSheet.ListObjects("discount").ListColumns(4).Range.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Sheets(hotel).Select
    Range("L33").Select
    
    Application.CutCopyMode = False
    
End Sub

All the help will be apreciate.

Thanks!

 

[JLGWhiz]

If this does not work like you want, try redording it.

Sub copia()    
    Application.ScreenUpdating = False    
    Dim hotel As String    
    hotel = ActiveSheet.Name         
    Sheets("discounts").ListObjects("discount").Range.AutoFilter Field:=1, Criteria1:=hotel    
    Sheets(hotel).Range("d43:d50").Copy    
    Sheets("discounts").ListObjects("discount").Cells(1. 4).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets(hotel).Select
    Range("L33").Select    
    Application.CutCopyMode = False    
End Sub

 

[Tresor]

If this does not work like you want, try redording it.

Sub copia()   
    Application.ScreenUpdating = False   
    Dim hotel As String   
    hotel = ActiveSheet.Name        
    Sheets("discounts").ListObjects("discount").Range.AutoFilter Field:=1, Criteria1:=hotel   
    Sheets(hotel).Range("d43:d50").Copy   
    Sheets("discounts").ListObjects("discount").Cells(1. 4).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets(hotel).Select
    Range("L33").Select   
    Application.CutCopyMode = False   
End Sub

Good morning,

thanks for the answer.

Unfortunately it doesn´t work