CODEWITHSUNDEEP
excelvbarowapplyselected
nicmdm
nicmdm

Reputation: 1

How to apply a macro to selected row only

I'm really new at maccros. I've made one using the auto recording, but I can't seem to use it to the selected row only, it keeps doing it on the same row as the record. I really need your help to solve it, and help me having a better understanding on how maccros actually works

My macro is as follow:

Sub COPIERVALEURS()
'
' COPIERVALEURS Macro
'
' Touche de raccourci du clavier: Ctrl+Shift+V
'

    Range("A34:H34").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
    Range("M34:N34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("K34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
    Range("S34:T34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
    Range("Y34:Z34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("W34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
    Range("AE34:AF34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AC34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=5
    Range("AI34:AJ34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AG34").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
    Range("AK34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks     _
        :=False, Transpose:=False
End Sub

Upvotes: 0

Views: 469

Answers (2)

nicmdm
nicmdm

Reputation: 1

Thanks Error 1004, It helped me a lot, the final code is now as below, and work perfectly, thanks to you:

Option Explicit

Sub COPIERVALEURS()

' COPIERVALEURS Macro
' Touche de raccourci du clavier: Ctrl+Shift+V

    Dim RowNo As Long

    With ThisWorkbook.Worksheets("PAQ")

        RowNo = Selection.Row '<- Here you get the row number you have select

        .Range("A" & RowNo & ":H" & RowNo).Copy
        .Range("A" & RowNo & ":H" & RowNo).PasteSpecial Paste:=xlPasteValues
        .Range("M" & RowNo & ":N" & RowNo).Copy
        .Range("K" & RowNo).PasteSpecial Paste:=xlPasteValues
        .Range("S" & RowNo & ":T" & RowNo).Copy
        .Range("Q" & RowNo).PasteSpecial Paste:=xlPasteValues
        .Range("Y" & RowNo & ":Z" & RowNo).Copy
        .Range("W" & RowNo).PasteSpecial Paste:=xlPasteValues
        .Range("AE" & RowNo & ":AF" & RowNo).Copy
        .Range("AC" & RowNo).PasteSpecial Paste:=xlPasteValues
        ActiveWindow.SmallScroll ToRight:=5
        .Range("AI" & RowNo & ":AJ" & RowNo).Copy
        .Range("AG" & RowNo).PasteSpecial Paste:=xlPasteValues
        .Range("AK" & RowNo).Copy
        .Range("AK" & RowNo).PasteSpecial Paste:=xlPasteValues

    End With

End Sub

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

I try to understand your logic to help you as much as i can. Select the row you want this code to take action, import a break point in the With line, execute and debug the code to see if its fits your requirements. In order to select a line press on the number of each line on your left.

Code:

Option Explicit

Sub test()

    Dim RowNo As Long

    With ThisWorkbook.Worksheets("Sheet1")

        RowNo = Selection.Row '<- Here you get the row number you have select

        .Range("M" & RowNo & ":N" & RowNo).Copy '<- Copy range M:N of the RowNo you have selct
        .Range("K" & RowNo).PasteSpecial Paste:=xlPasteValues '<- Paste in Column K row the one tou have select

        .Range("S" & RowNo & ":T" & RowNo).Copy
        .Range("Q" & RowNo).PasteSpecial Paste:=xlPasteValues

        .Range("Y" & RowNo & ":Z" & RowNo).Copy
        .Range("W" & RowNo).PasteSpecial Paste:=xlPasteValues

        .Range("AE" & RowNo & ":AF" & RowNo).Copy
        .Range("AC" & RowNo).PasteSpecial Paste:=xlPasteValues

        .Range("AI" & RowNo & ":AJ" & RowNo).Copy
        .Range("AG" & RowNo).PasteSpecial Paste:=xlPasteValues

        Application.CutCopyMode = False

    End With

End Sub

Upvotes: 1

Related Questions