Extracting from multiple worksheets

[sharper31]

I want to extract the value of a cell from over 50 worksheets within one book
and paste to a new worksheet in the same workbook in a column with the name
of the worksheet in a column next to it. The name of the worksheet is also
in a cell within the respective worksheet.
The name cell is always the same in every worksheet, A6 the value I want to
extract always varies, but is the lower most cell in the column K, and is
also the highest value on the worksheet.

Can anyone write a macro to do this? or the necessary function to paste
into the new worksheet.

 

[Bernie Deitrick]

Sharper,

Try this macro, below. It will extract those values from every worksheet,
and put them on a sheet named "Extracted Values"

HTH,
Bernie
MS Excel MVP

Sub SharpersExtractor()
Dim mySht As Worksheet

Worksheets.Add.Name = "Extracted Values"

For Each mySht In ActiveWorkbook.Worksheets
Range("A65536").End(xlUp)(2).Value = _
mySht.Range("A6").Value
Range("B65536").End(xlUp)(2).Value = _
mySht.Range("K65536").End(xlUp).Value
Next mySht
End Sub