Need a Macro to select specific cell from different works book in msexcel 2003

I need help in creating a macro; in selecting specific cell from different 开发者_StackOverflow中文版works books

For Instance I have a workbook1 and specific cell in workbook2 say "B35" and have figure 12 likewise in workbook3 say "B35" and figure 42 and Workbook4 say "B35" and figure 53

I need all of them in Workbook1

as WB2 - B35 = 12, WB 3 = 42 so on...

Can anyone help me?

I am not sure I really understood your question.

Here is the way in VBA to refer to a Range on different workbooks / worksheets:

Workbooks("myWB.xls").Worksheets("Sheet1").Range("C10")

You can also use objects:

Dim wb as Workbook
Dim ws as Worksheet
Dim cell as Range
Set wb = Activeworkbook 'or Workboks("myWB.xls")
Set ws = wb.Worksheets("Sheet1")
Set cell = ws.Range("C10") 'or ws.["C10"]

See MSDN for more information

I guess figure you mean the cell value right ? I have did addition, as you said you need all of them in one . Im not sure which operation you want to perform.

dim wb1 as excel.workbook
dim wb2 as excel.workbook
dim wb3 as excel.worbook
set wb1 = "work_book_name" 'probably where you want all your values
set wb2 = "secon_work_book" 'probable the second workbook 
set wb3 = "third_work_book"  ' probably another workbook  name
wb1.sheets("sheet_name").cells(35,"B").value= wb2.sheets("sheet_name").cells(35,"b").value + wb3.sheets("sheet_name").cells(35,"B").value

you can even set sheets as

dim sh1 as excel.worksheet \\ set as the worksheet
set sh1 = "sheet_name" 

You can set

  set wb1=thisworkbook ' it refers to that workbook where macro is resided.

I will give you small example

Suppose your

  workbook name is tester.xls \I mean excel file name and your sheet name is test

  dim wb1 as excel.workbook
  dim sh1 as excel.worksheet
  set wb1 = "tester"
  set sh1 = "test"
  wb1.sh1.range("A1").value = 42 ' now it has 42 in its cell

There is nothing special about copying values between workbooks. You just need to assign the value of one cell to another cell:

workbooks(2).Sheets(3).Range("b4") = workbooks(1).Sheets(1).Range("a1")

This will loop through all active workbooks to get the value at "B35". Each value is copied to a new row on the sheet you have active at the time you run the macro.

Sub getVals()

    Dim s As Worksheet
    Set s = ActiveSheet ' Change this to where ever you want to copy the values to
    i = 1

    ' loop through all open workbooks
    For Each wkbk In Workbooks
        ' copy the name of the workbook
            s.Cells(i, 1) = wkbk.Name
        ' copy value from "B35" to the first workbook
            s.Cells(i, 2) = wkbk.Sheets(1).Range("B35")
        ' increment i so that it points to the next row
            i = i + 1
    Next

End Sub