Referring to column in a macro

[ferocious12]

Hi All,values

I have following macro that paste range A7:AC76 from Sheet1 to "Summary" Sheet. At the end it copy the values from Sheet1 column M7 onwards and I have to paste special as values the copied data in column M of the Summary sheet. The problem is how to select the column M of first row of the freshly copied data in Summary sheet.

Currently I am doing it manually. Just pasting it in column M of the first row of currently selected range in Summary Sheet.

Please help.

Thanks

Sub SummaryPaste()

    
    Sheets("Sheet1").Select
    
    Range("E7").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Range("A7:AC76").Select
    
    Selection.Copy
    
    Sheets("summary").Select
    Application.Goto Reference:="R100001C1"
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    
     Sheets("Sheet1").Select
    Range("M7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Sheets("summary").Select

End Sub

 

[rahulshewale1]

hi @ferocious12 ,

Please upload sample data

 

[mohadin]

Hi see this if it work for you

Sub SummaryPaste()
    With Sheets("Sheet1").Select
        Set Rng = Union(Range("E7"), Range("G7"), Range("H7"), Range("L7"))
        With Rng
            .Value = .Value
        End With
    End With
    Sheets("Sheet1").Range("A7:AC76").Copy Sheets("summary").Range("A1").Offset(Sheets("summary").Cells(Rows.Count, 1).End(xlUp).Row + 1)
    
    Sheets("Sheet1").Select
    Range("M7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("summary").Select
    [M:M].Select
    ActiveSheet.Paste
End Sub