Solved: How to obtain two cell data from each worksheet in an Excel file?

[mm1234mail]

I have an Excel file and in which there are many worksheets. The number of worksheets in the file is not fixed as the number of worksheet depends on the number of business days in the study period. It may be changed for different Excel file.

In each worksheet, I want to obtain two cell's data. The first one is cell "B2" which is the date of record, e.g. 31/07/2009, and the other cell is "E9" which is a number (actually this cell is a formula to calculate a figure from the worksheet)

Can anyone help to provide the VBA to obtain these 2 cells and to have output to show these 2 record in Excel file in 2 columns as below:

Output:
Column A Column B
31/07/2009 123,456.02
01/08/2009 456,789.11
02/08/2009 101,121.30
. .
. .

Therefore, I need the VBA that can obtain the 2 cells' data from all worksheets in the Excel file and it can be used for different Excel file, ie. I can input the file name or select the Excel file.

Thanks for your help.

[Bob Phillips]

Untested



Public Sub Test()
Dim filename As String
Dim wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim nextrow As Long

With Application.FileDialog(msoFileDialogOpen)

.AllowMultiSelect = False
If .Show = -1 Then

Set wb = Workbooks.Open(filename)
wb.Worksheets.Add after:=wb.Worksheets(wb.Worksheets.Count)
Set wsNew = ActiveSheet
wsNew = "Results"
For Each ws In wb.Worksheets

If ws.Name <> wsNew.Name Then

nextrow = nextrow + 1
wsNew.Cells(nextrow, "A").Value = ws.Range("B2").Value
wsNew.Cells(nextrow, "B").Value = ws.Range("E9").Value
End If
Next ws

wsNew.Columns("A").NumberFormat = "dd/mm/yyyy"
wsNew.Columns("B").NumberFormat = "#,##0.00"
End If
End With
End Sub

[mm1234mail]

xld,

The codes can works and I make some adjustment to fit my requirement as below. Really thanks for your help.

Private Sub CommandButton1_Click()
Dim filename, NewFile As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim nextrow As Long

NewFile = Application.GetOpenFilename
If NewFile = False Then
Exit Sub
End If

Workbooks.Open filename:=NewFile

Set wb = Workbooks.Open(NewFile)
wb.Worksheets.Add after:=wb.Worksheets(wb.Worksheets.Count)
Set wsNew = ActiveSheet
For Each ws In wb.Worksheets

If ws.Name <> wsNew.Name Then

nextrow = nextrow + 1
wsNew.Cells(nextrow, "A").Value = ws.Range("B2").Value
wsNew.Cells(nextrow, "B").Value = ws.Range("E9").Value
End If
Next ws

wsNew.Columns("A").NumberFormat = "dd/mm/yyyy"
wsNew.Columns("B").NumberFormat = "#,##0.00"

End Sub