tinamiller1
08-21-2013, 07:43 AM
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
'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