How to refer to a cell from another workbook in VBA

[diepvic]

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

 

[Sam Wilson]

If the workbooks will be open at the time:

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

 

[Stefi]

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

Regards,
Stefi

„diepvic†ezt írta:

 

[Jacob Skaria]

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]

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

 

[AhmadReza]

Hi friends,
I have a problem and would be glad if there is someone to help me
I have a file at drive "F" and folder "ABS" - my filename is "Listdata"
In an Excel file on my desktop I want cells(4,4) to retieve data from "ABS" sheet(1) and cell (2,2)
The below code
Sub Macro1()
activesheet.Cells(4, 4).Value = " F:\ABS\" & listdata & ".xlsx" & Sheets(1).Cells(2, 2).Value
End Sub
but it does not work correctly
Thanks Friends