If Statement which I use to copy paste rows based on specific criteria in a different sheet

Question

So I want to copy/paste fixed numbers in a different tab. I have one parameter (which is the period name) and based on the number the data should go to different rows. I prepared a code so far but it is so big! If you could advice of how I can reduce the lines that would be helpful. I have 12 periods but I just show you the first two as the rest are identical and I change only the row number.

Thanks so much in advance

If Range("D2") = 1 Then

    Range("D10:E10").Select
    Selection.Copy
    Sheets(7).Select
    Range("D10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("G10:H10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("G10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("K10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("K10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("M10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("M10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("O10:S10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("O10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("V10:Z10").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("V10").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
ElseIf Range("D2") = 2 Then

    Range("D11:E11").Select
    Selection.Copy
    Sheets(7).Select
    Range("D11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("G11:H11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("G11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("K11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("K11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("M11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("M11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("O11:S11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("O11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(6).Select
    Range("V11:Z11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(7).Select
    Range("V11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
End if

score 1 · Accepted Answer · answered Oct 20 '20 at 11:23

You don't need to select anything.

Dim row_offset As Long
row_offset = Sheets(6).Range("D2").Value - 1

Sheets(7).Range("D10:E10").Offset(row_offset, 0).Value = Sheets(6).Range("D10:E10").Offset(row_offset, 0).Value
Sheets(7).Range("G10:H10").Offset(row_offset, 0).Value = Sheets(6).Range("G10:H10").Offset(row_offset, 0).Value
Sheets(7).Range("K10").Offset(row_offset, 0).Value = Sheets(6).Range("K10").Offset(row_offset, 0).Value
Sheets(7).Range("M10").Offset(row_offset, 0).Value = Sheets(6).Range("M10").Offset(row_offset, 0).Value
Sheets(7).Range("O10:S10").Offset(row_offset, 0).Value = Sheets(6).Range("O10:S10").Offset(row_offset, 0).Value
Sheets(7).Range("V10:Z10").Offset(row_offset, 0).Value = Sheets(6).Range("V10:Z10").Offset(row_offset, 0).Value

Omg..amazing, super efficient compare to what I have. Thanks so much, really appreciate it! — Evan Papanastasis, Oct 21 '20 at 12:32

If Statement which I use to copy paste rows based on specific criteria in a different sheet

1 Answers1