Hi, there I have a question want to automate through vba. I have a data in sheet1 in range A1:A4. Pl

[Shailender]

Hi, there I have a question want to automate through vba. I have a data in sheet1 in range A1:A4. Please refer to the workbook for question,

 

[shrivallabha]

What you need is unclear. Do you want last non-blank value in Column A of Sheet1 to reflect in Sheet2 cell D4?

 

[Marc L]

Hi !​

Sub Demo1()
    Sheet1.Activate
    Sheet2.[C4:D4].Value = Array("Sheet1!" & ActiveCell.Address(0, 0), ActiveCell.Value)
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Shailender]

Hi Marc, thank you for the quick reply. Actually what exactly want is i want the activecell value of the sheet1 in the sheet2 D4. Example if the activecell of the sheet1 is 3, and that value should reflect in sheet2 with reference =sheet!c3. Hope this is clear.

 

[Marc L]

Just update my sample to your need !

As your attachment does not reflect your need, next time you should
save time by a crystal clear and complete explanation …
​

 

[Shailender]

Hello, Marc, I have attached excel sheet with the criteria I need.

 

[shrivallabha]

Shailender,

The cell doesn't remain active if we switch from Sheet1 to Sheet2. Activecell / Activesheet / Activeworkbook will always refer to current workbook.

So can you describe the exact event when you want the Sheet1 reference needs to be updated in Sheet2?

 

[Shailender]

Hello, Marc, to clarify the question clearly, for instance, today active cell in sheet1 A4, tomorrow the active cell is A5 every day the active cell in the sheet1 is going to increase. I have a task that active cell value to be placed in sheet2 D4 with the formula. Hope this is clear now.

 

[Marc L]

Unclear : just see in VBA inner help what ActiveCell means !

We still are waiting for an answer of post #2 ?!
Last attachment is same as initial one ! …

You have some material within my code, try just to mod it …

« No arm, no chocolate ! »
No true explanation, no solution …
​

 

[Shailender]

Marc, I am clear with my question. Let's say we are in Sheet1 the active cell for today is A4, and tomorrow it is A5 in the sheet1, that value I need it in Sheet2 in D4. Hope this is clear now.

 

[Shailender]

Hello, Shrivallaba, I posted my question clearly in the above post, Please look into that. Thank you.

 

[Marc L]

As it seems you don't ever read VBA inner help,
as « active cell for today » is like a no sense
(no link between a date and the active cell !),
as in both attachments it seems expected result is lacking,
as a child can clearly expose this kind of need,
all I can tell is you must test yourself in a VBA code ActiveCell …

Now I do not feel anymore to be The Doctor,
so I quit before becoming a Dalek ‼

As Shrivallabha is very clever, if you just answer to its first post …

I'm leaving !

​

 

[shrivallabha]

Shailender, see if below formula gives you what you want assuming you have numeric data in column A (as in sample file).
=VLOOKUP(9.99999999999999E+307,Sheet1!A:A,1,TRUE)