Cell values from mulitiple worksheets to two columns on another ws

[Guest]

Oh Wise Ones,
I have browsed some previous posts for a similar request
but I could not find an example that I was capable of editing for my purpose.
Heres what I would like to accomplish. I have multiple worksheets in a
workbook and more may be added at any time. I need to retrieved the values in
cells F2(Date reported) and F4(Date of Incident) from ALL other existing
worksheets and place them in column H and J respectively of a worksheet
called "Control" in the same workbook. Obviously the range in column H and J
would automatically grow as more sheets are added. Any help would be
appreciated.

Thanks,
Mike

 

[Guest]

Try this:

Sub test()
Dim ws As Worksheet
Dim i As Integer
For Each ws In Worksheets
If ws.Name <> "Control" Then
i = i + 1
Sheets("Control").Cells(i, 8) = ws.Cells(2, 6)
Sheets("Control").Cells(i, 10) = ws.Cells(4, 6)
End If
Next
End Sub

This code would be placed in a new module.

 

[Guest]

Thanks Art! You guys are awesome!

Mike

Try this:

Sub test()
Dim ws As Worksheet
Dim i As Integer
For Each ws In Worksheets
If ws.Name <> "Control" Then
i = i + 1
Sheets("Control").Cells(i, 8) = ws.Cells(2, 6)
Sheets("Control").Cells(i, 10) = ws.Cells(4, 6)
End If
Next
End Sub

This code would be placed in a new module.