VBA Macro Consolidate Data From Discontiguous Cells In Multiple Sheets To One Master

[tinamiller1]

I have a macro that copies multiple cells from my 301 worksheets in a workbook to 1 sheet. All of the cells are consistent as far as placement and contain text value except for 1 cell that is a summation of other cells. I am not quite sure how to code that particular portion so it copies the value only and pastes it in the worksheet. This would be similiar to doing a copy paste special but since I have 301 worksheets, I don't want to manually do this. Here is my code: cell j43 is the summation cell.

'seventh macro
'copy cells
Sub copycells()
Dim WS As Worksheet, wsum As Worksheet
Dim wb As Workbook
Dim vws As Variant 'Need to use a Variant for iterator
Dim i As Integer, j As String, k As String 

i = 0
Set wb = Workbooks("sheet4.xlsm")
Set wsum = wb.Sheets("summary") 

'Iterate through the sheets
For Each vws In wb.Sheets
If vws.Name <> "summary" Then
j = CStr(i + 2)
k = CStr(i + 18)
vws.Range("b8").Copy wsum.Range("a" & j)
vws.Range("b9").Copy wsum.Range("b" & j)
vws.Range("b5").Copy wsum.Range("c" & j)
vws.Range("H48").Copy wsum.Range("D" & j)
vws.Range("g13:g31").Copy wsum.Range("e" & j & ":e" & k)
vws.Range("i13:i31").Copy wsum.Range("f" & j & ":f" & k)
vws.Range("j13:j31").Copy wsum.Range("g" & j & ":g" & k)
vws.Range("k13:k31").Copy wsum.Range("h" & j & ":h" & k)
vws.Range("l13:l31").Copy wsum.Range("i" & j & ":i" & k)
vws.Range("k38").Copy wsum.Range("j" & j)
vws.Range("l38").Copy wsum.Range("k" & j)
vws.Range("e2").Copy wsum.Range("l" & j)
Sheets("Sheet4").Range("j43").Copy Destination:=Sheets("summary").Range("m" & j) 

i = i + 18
End If
Next
End Sub