I am trying to grab the value from all sheets in a workbook and paste them as values down the M column starting with M1 on a sheet called "Summary". I would the like to total the values pasted.
The value is found with this code:
Range("F2").End(xlDown).Offset(2).Copy
Here is the whole code:
Sub FindCell()
Sheets(1).Activate
Range("A1").Select
For Each work_sheet In ActiveWorkbook.Sheets
Range("F2").End(xlDown).Offset(2).Copy
With Sheets("Summary")
lst = .Range("M" & Rows.Count).End(xlUp).Row + 1
.Range("M" & lst).PasteSpecial xlPasteColumnWidths
.Range("M" & lst).PasteSpecial xlPasteValues
End With
Next work_sheet
Sheets("Summary").Activate
End Sub
My issue is it only pastes the value of the first worksheet multiple times. Any help?
The value is found with this code:
Range("F2").End(xlDown).Offset(2).Copy
Here is the whole code:
Sub FindCell()
Sheets(1).Activate
Range("A1").Select
For Each work_sheet In ActiveWorkbook.Sheets
Range("F2").End(xlDown).Offset(2).Copy
With Sheets("Summary")
lst = .Range("M" & Rows.Count).End(xlUp).Row + 1
.Range("M" & lst).PasteSpecial xlPasteColumnWidths
.Range("M" & lst).PasteSpecial xlPasteValues
End With
Next work_sheet
Sheets("Summary").Activate
End Sub
My issue is it only pastes the value of the first worksheet multiple times. Any help?