Simplifying a code

[Flavien]

Hello everyone!

I would like to replace the many lines of code

WS1.Cells(I, "A").Copy: WS2.Cells(Lig, "A").PasteSpecial Paste:=xlPasteValues

with a range like

WS1.range(I, "A:Q").Copy: WS2.range(Lig, "A:Q").PasteSpecial Paste:=xlPasteValues

would someone know how to do this please?

Sub copy_paste()
    Dim Lig, I As Long
    Dim WS1, WS2 As Worksheet
    
    Set WS1 = Worksheets("Feuil1")
    Set WS2 = Worksheets("Feuil2")
    
'   appeler une FNC
    Ext = ThisWorkbook.Worksheets("Accueil").Range("Concat_Num_FNC").Value
  
    Lig = 4
    
    For I = 2 To WS1.Range("A" & Rows.Count).End(xlUp).Row
        If WS1.Cells(I, 1) = Ext Then
            WS1.Cells(I, "A").Copy: WS2.Cells(Lig, "A").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "B").Copy: WS2.Cells(Lig, "B").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "C").Copy: WS2.Cells(Lig, "C").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "D").Copy: WS2.Cells(Lig, "D").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "E").Copy: WS2.Cells(Lig, "E").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "F").Copy: WS2.Cells(Lig, "F").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "G").Copy: WS2.Cells(Lig, "G").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "H").Copy: WS2.Cells(Lig, "H").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "I").Copy: WS2.Cells(Lig, "I").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "J").Copy: WS2.Cells(Lig, "J").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "K").Copy: WS2.Cells(Lig, "K").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "L").Copy: WS2.Cells(Lig, "L").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "M").Copy: WS2.Cells(Lig, "M").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "N").Copy: WS2.Cells(Lig, "N").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "O").Copy: WS2.Cells(Lig, "O").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "P").Copy: WS2.Cells(Lig, "P").PasteSpecial Paste:=xlPasteValues
            WS1.Cells(I, "Q").Copy: WS2.Cells(Lig, "Q").PasteSpecial Paste:=xlPasteValues



        End If
    Next I
End Sub

 

[Joe4]

WS1.range(I, "A:Q")

is not a valid range reference.

Write it like this:

WS1.Range(Cells(I, "A"), Cells(I, "Q"))

 

[Flavien]

Thank you very much Joe4.
I'm not currently at the office, so i have to wait till monday.
Have a nice weekend. See you!

 

[Flavien]

Hello Joe4,

Unfortunately the code does not execute completely, error 1004 is displayed and indicates a problem in the second part of the line

WS2.Range(Cells(I, "A"), Cells(I, "Q")).PasteSpecial Paste:=xlPasteValues

I've tried different writings but I can't find the solution, any idea, please?

 

[MARK858]

@Flavien try...

Range(WS2.Cells(Lig, "A"), WS2.Cells(Lig, "Q")).PasteSpecial Paste:=xlPasteValues

or you should be able to just do

Range(WS1.Cells(I, "A"), WS1.Cells(I, "Q")).Copy
WS2.Cells(Lig, "A").PasteSpecial Paste:=xlPasteValues

Although it looks like your loop will overwrite the destination range as you aren't incrementing Lig on each iteration, you could replace Lig with I + 3

Also you have to explicitly declare variables in VBA
so

Sub copy_paste()
    Dim Lig, I As Long
    Dim WS1, WS2 As Worksheet

should be

Sub copy_paste()
    Dim Lig As Long, I As Long
    Dim WS1 As Worksheet, WS2 As Worksheet

 

[Flavien]

Although it looks like your loop will overwrite the destination range as you aren't incrementing Lig on each iteration, you could replace Lig with I + 3

Thank you, it works!
Yes you're right, FYI, my idea is to copy 1 row saved in a database, make changes to it and replace the old line with the new one (so, no duplicates)

 

[MARK858]

You're welcome