EXCEL表合并，现在是粘贴公式，能改成粘贴时只粘贴数值吗？

Option Explicit
Sub 合并生成报表()
Dim i As Integer
Dim j As Integer
Dim N As Integer
Dim Filename(100) As String
Dim Stemp As String
Dim sFile As String
Dim FileCount As Integer
sFile = ActiveWorkbook.Name
With Application.FileDialog(msoFileDialogOpen)
Rows("3:65536").Clear '清空汇总表
.Title = "选择文件（可多选）"
.AllowMultiSelect = True
.Filters.Add "Excel Files", "*.xls"
.FilterIndex = 2 '默认的文件筛选条件的索引号
.Show
FileCount = .SelectedItems.Count
If FileCount = 0 Then Exit Sub
Filename(1) = .SelectedItems(1)
For i = 1 To FileCount
Filename(i) = .SelectedItems(i)
Next i
End With

For i = 1 To FileCount
Workbooks.Open (Filename(i))
For j = 1 To ActiveWorkbook.Sheets.Count
Sheets(j).Activate
N = ActiveSheet.UsedRange.Rows.Count
Stemp = "AR5" & ":BG" & N 'D5-S5以下所有数据
Range(Stemp).Select
Selection.Copy
Workbooks(sFile).Activate
N = ActiveSheet.Range("A65536").End(xlUp).Row
If N = 1 Then N = 0
Cells(N + 1, 1).Select
ActiveSheet.Paste
Stemp = Right(Filename(i), Len(Filename(i)) - InStrRev(Filename(i), "\"))
Workbooks(Stemp).Activate
Next j
ActiveWorkbook.Close
Next i
End Sub

activesheet.paste 改成
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

把所有的引用地址改成绝对引用就可以了，如b2改成$b$2

改完后为：

=syd($b$2,$h$2,$d$2,year($a$6)-year($a$5))