How to refer to a cell from another workbook in VBA
 

Hi,
I want to check the date in 5 cells belongings to 5 different worksheets of
5 workbooks to make sure they are all the same.
E.g: Cell A1 in worksheet "StoreA" of workbook "Material"
Cell A1 in worksheet "StoreB" of workbook "Tools"
Cell A1 in worksheet "StoreC" of workbook "WIP"
Cell A1 in worksheet "StoreD" of workbook "Goods"

Pls advise me a VBA code to solve this.

Thanks so much

How to refer to a cell from another workbook in VBA
 

If the workbooks will be open at the time:

workbooks("Material").worksheets("StoreA").range(" A1").value gets the value

"diepvic" wrote:

Hi,
I want to check the date in 5 cells belongings to 5 different worksheets of
5 workbooks to make sure they are all the same.
E.g: Cell A1 in worksheet "StoreA" of workbook "Material"
Cell A1 in worksheet "StoreB" of workbook "Tools"
Cell A1 in worksheet "StoreC" of workbook "WIP"
Cell A1 in worksheet "StoreD" of workbook "Goods"

Pls advise me a VBA code to solve this.

Thanks so much

How to refer to a cell from another workbook in VBA
 

Workbooks("Material").Worksheets("StoreA").Range(" A1")
etc.

Regards,
Stefi

€ždiepvic€� ezt Ã*rta:

Hi,
I want to check the date in 5 cells belongings to 5 different worksheets of
5 workbooks to make sure they are all the same.
E.g: Cell A1 in worksheet "StoreA" of workbook "Material"
Cell A1 in worksheet "StoreB" of workbook "Tools"
Cell A1 in worksheet "StoreC" of workbook "WIP"
Cell A1 in worksheet "StoreD" of workbook "Goods"

Pls advise me a VBA code to solve this.

Thanks so much

How to refer to a cell from another workbook in VBA
 

It is always better to create an object for referencing..

Dim wbBook As Workbook
Dim wsSheet As Worksheet

'To reference a open workbook (unsaved)
Set wbBook = Workbooks("Book1")

'To reference a open workbook (already saved)
Set wbBook = Workbooks("Book1.xls")

'To reference a work sheet
Set wsSheet = wbBook.Worksheets("Sheet1")

'To access cell A1 of book1 Sheet1
wsSheet.Range("A1")

If this post helps click Yes
---------------
Jacob Skaria


"diepvic" wrote:

Hi,
I want to check the date in 5 cells belongings to 5 different worksheets of
5 workbooks to make sure they are all the same.
E.g: Cell A1 in worksheet "StoreA" of workbook "Material"
Cell A1 in worksheet "StoreB" of workbook "Tools"
Cell A1 in worksheet "StoreC" of workbook "WIP"
Cell A1 in worksheet "StoreD" of workbook "Goods"

Pls advise me a VBA code to solve this.

Thanks so much

How to refer to a cell from another workbook in VBA
 

Change the Workbook names and sheetname to suit.
dt1 = Workbooks("Book3").Worksheets("Sheet1").Range("A1" )
dt2 = Workbooks("Book3").Worksheets("Sheet2").Range("A1" )
dt3 = Workbooks("Book3").Worksheets("Sheet3").Range("A1" )
dt4 = Workbooks("Book3").Worksheets("Sheet4").Range("A1" )
dt5 = Workbooks("Book3").Worksheets("Sheet5").Range("A1" )

If dt1 = dt2 And dt1 = dt3 And dt1 = dt4 And dt1 = dt5 Then
MsgBox "The dates are same"
'your code
Else
'your code
End If

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

It is always better to create an object for referencing..

Dim wbBook As Workbook
Dim wsSheet As Worksheet

'To reference a open workbook (unsaved)
Set wbBook = Workbooks("Book1")

'To reference a open workbook (already saved)
Set wbBook = Workbooks("Book1.xls")

'To reference a work sheet
Set wsSheet = wbBook.Worksheets("Sheet1")

'To access cell A1 of book1 Sheet1
wsSheet.Range("A1")

If this post helps click Yes
---------------
Jacob Skaria


"diepvic" wrote:

Hi,
I want to check the date in 5 cells belongings to 5 different worksheets of
5 workbooks to make sure they are all the same.
E.g: Cell A1 in worksheet "StoreA" of workbook "Material"
Cell A1 in worksheet "StoreB" of workbook "Tools"
Cell A1 in worksheet "StoreC" of workbook "WIP"
Cell A1 in worksheet "StoreD" of workbook "Goods"

Pls advise me a VBA code to solve this.

Thanks so much