copy paste macro

[tinamiller1]

I have 3 text based cells and 1 summation based cell. I have the following macro and it works except for copying the summation based cell which is h38. I have 248 worksheets that are within my workbook and need to copy the data from every sheet onto the summary2 sheet. How can I copy the summation as a pastespecial the values only for cell h38 from every sheet?


'seventh macro
'copy cells
Sub copycell()
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
i = 0
Set wb = Workbooks("sheet1.xlsm")
Set wsum = wb.Sheets("summary2")
'Iterate through the sheets
For Each vws In wb.Sheets
If vws.Name <> "summary2" Then
j = CStr(i + 2)
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("H38").Copy wsum.Range("D" & j)
i = i + 1
End If
Next
End Sub

[SamT]

OP Code tabbed and tagged

Option Explicit

'seventh macro
'copy cells
Sub copycell()
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

i = 0
Set wb = Workbooks("sheet1.xlsm")
Set wsum = wb.Sheets("summary2")

'Iterate through the sheets
For Each vws In wb.Sheets
  If vws.Name <> "summary2" Then
    j = CStr(i + 2)
    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("H38").Copy wsum.Range("D" & j)
    i = i + 1
  End If
Next
End Sub

[SamT]

'Iterate through the sheets
j = 2
For Each vws In wb.Sheets
  If vws.Name <> wsum.Name Then
    vws.Range("b8").Value.Copy wsum.Range("A" & j)
    vws.Range("b9").Value.Copy wsum.Range("B" & j)
    vws.Range("b5").Value.Copy wsum.Range("C" & j)
    vws.Range("H38").Value.Copy wsum.Range("D" & j)
    j = j + 1
  End If
Next vws

[tinamiller1]

'Iterate through the sheets
j = 2
For Each vws In wb.Sheets
  If vws.Name <> wsum.Name Then
    vws.Range("b8").Value.Copy wsum.Range("A" & j)
    vws.Range("b9").Value.Copy wsum.Range("B" & j)
    vws.Range("b5").Value.Copy wsum.Range("C" & j)
    vws.Range("H38").Value.Copy wsum.Range("D" & j)
    j = j + 1
  End If
Next vws

I am confused how to apply the value.copy for only cell h38 into my original code. The other cells are not summations. Do I cahnge them all to .value.copy

[SamT]

The Cell .Value is what you see when you look at the cell. Iit can be the result of a formula (a Summation) or it can be what is typed into the cell.

You only have to use .Value when you want the result of a calculation, (Summation,) but you can use it any time you don't need to copy the cell's formula.

SO: You must use "Range("H38").Value.Copy," and you can use it for the rest.