How to use paste special values

Hi there,

I am trying to create a summary column A on Sheet1 that takes column E from sheets 2-8 and forms one master list. Column E is a concatenation and the number of rows in column E differs from sheet to sheet, from month to month.

I thought I was doing great until I ran into an issue with pasting as a special value. My understanding is I have to use pasting as a special value with a range, but I don't understand how I can make the range dynamic.

This is where I got before I discovered the paste as special value issue (I practiced on a values only workbook).

Thanks for your help!

Private Sub CommandButton1_Click()
Dim lastrow As Long, endrow As Long
lastrow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Worksheets("Sheet2").Cells(i, 5).Copy
endrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("Sheet2").Paste Destination:=Worksheets("Sheet1").Cells(endrow + 1, 1)
Next i

End Sub

Hey,

There is actually a separate pastespecial function in Excel, here is how that would look

Sub PasteSpecial_Test()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ws.Range("A1").Copy
    ws.Range("A1").PasteSpecial (xlPasteValues)

End Sub

Alternatively, you can actually set the value of the cell/range to it's own value to accomplish the same thing

Sub Alternate_Set_as_Value()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ws.Range("A1").Value = ws.Range("A1").Value

End Sub