Multi sheet data import

[balthur]

I've got a 2003 xls file with 300 sheets,
in sheet number 301 (named summary) I've got the lit of the 300 sheets.

I need to get inside every sheet and import data from C3 and put it into a list right next to the sheet name in sheet 301 in an another column.

big thanks for help guys

 

[Rasm]

Try this

Public Sub CopyFromSheets()
    Sheets("Sumary").Activate
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Sumary" Then 
            ActiveSheet.Cells(i, 3).Value = Sheets(i).Range("C3").Value
        End If
    Next i
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

You could just use a formula. This would have the advantage of automatically updating if any of the C3 cell values change.

If your first sheet name is in cell A2 of the summary sheet then in cell B2 try this formula and copy it down.

=INDIRECT("'"&A2&"'!C3")

 

[balthur]

Try this

Public Sub CopyFromSheets()
    Sheets("Sumary").Activate
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Sumary" Then 
            ActiveSheet.Cells(i, 3).Value = Sheets(i).Range("C3").Value
        End If
    Next i
End Sub

looks cool, but how would I use it in an another spreadsheet ?
so how to copy cells from all sheets from an another spreadsheet ? :P

 

[Rasm]

What you mean by spreadsheet - are all the sheets in one workbook or multiple workbook?