How to refer to cell in a different sheet?

[zjianguk]

Hi,

I have two sheets: Sheet1 and Sheet2. There is a macro for Sheet1 which needs to refer to a cell A1 in Sheet2. What is the code for the reference to Sheet2? I tried ...Sheets("Sheet2").Range("A1").Value, but not working.

[kbkumar]

Hi - A sample code to pull value of sheets 2 into any cell in Sheet1:

Sub Test()
Range("A1") = Worksheets("Sheet2").Range("C7")
End Sub

Hope you are able to modify this as per your needs.

[zjianguk]

This worked. But if I add something to make it as: Range("A1") = Worksheets("Sheet2").Range("C7").Value

Then this resulted in an error.

Hi - A sample code to pull value of sheets 2 into any cell in Sheet1:

Sub Test()
Range("A1") = Worksheets("Sheet2").Range("C7")
End Sub

Hope you are able to modify this as per your needs.

[kbkumar]

Hi Zjianguk,

I would not use ".value" in the end at this stage. Would be good if you could upload a sample workbook and share the error you are getting. This will help address your query more efficiently.

This worked. But if I add something to make it as: Range("A1") = Worksheets("Sheet2").Range("C7").Value

Then this resulted in an error.

Thanks.

[Winon]

Hello zjianguk,

I think it may depend on various factors. If Sheets("Sheet2") is in fact Sheet 2, it should work, otherwise it would be better if you could upload a sample WorkBook, with your Code, for us to have a look at.

[kbkumar]

Complete agree and second Winon's thoughts. Worksheet names while using macros could be tricky at times.

[zjianguk]

Thanks for your offer to help, Winon

[Winon]

Hello zjianguk,

You are welcome.

Mind sharing with us how you got your issue resolved?