How to get the specified cell details or values from one sheet to another?

The syntax for your vlookup formula is wrong.
1) The first parameter (lookup value) should be a single cell and not a range, You have B:B where it should be B5.
2) The second parameter (lookup range) should have the lookup value in the left most column. Your lookup values are in column C but you are looking at range A to O, should be C to O
3) The third parameter (offset) is incorrect. Although column O is indeed column 15, it is only column 13 of the lookup range
4) The fourth parameter (lookup type) should be TRUE or FALSE. You have chosen FALSE will look for an exact match but the there is no exact match, for example newCorp sheet has "Connected standby" but WW31 sheet has "Connected standby (CS)".

Thanks....
I have changed those parameters, now also its not working...

 .Formula = "=Iferror(VLOOKUP(B5," & str & "!C:O,13,TRUE),"" "")"

please can you help me .......
i am in need of that...

Have you also changed the headings so that they match? As per my comment

newCorp sheet has "Connected standby" but WW31 sheet has "Connected standby (CS)"

yes i changed  that also

How are you triggering the macro?

What result are you getting? Just saying its not working doesn't really help.

Through command button am running macro.
After clicking that button , no values are displaying.
If we do the same by manually(i.e., by putting the same formula) the values are coming.

The command button that was in the uploaded file had no code against it. Is the code actually running?

this is how i tried...
snsnew.xlsm

Please check the attached sheet..

Sorry can't download the .xlsm file while at work. Happy to look at it this evening when back at home.

OK sir

As an alternative, are you aware of the INDIRECT function?

Syntax:  =INDIRECT(Text)   where Text is a string of text as a reference to a cell, eg $A$1.

In use you could have the value $A$1 in cell B2 and formula =INDIRECT(B2) in another cell. This would return the value of A1.

So in your case, you could replace your lookup formula with:

=IFERROR(VLOOKUP($A5,INDIRECT(B$4&"!$C:$O"),13,FALSE),"")

The INDIRECT creates a string with the sheet name and column range. This is then used in the VLOOKUP as an equivalent to "WW31!C:O"

The formula above can be put into B5 and copied across to I5 and down to I10 or if you want it applied with the macro:

Sub Copy1()

With Sheets("newCorp")
    With .Range("B5:I10")
    .Formula = "=IFERROR(VLOOKUP($A5,INDIRECT(B$4&""!C:O""),13,FALSE),"""")"
    End With
End With

End Sub

Select allOpen in new window

Thank you sir ....  now its working.

Sir i have one more doubt, shall i ask?

Sir can we insert one more row in between B5 and B6, B6 and B7 and so on..

For adding Dpmo values which are present in 3rd row .

And can make dropdown for milestone(Alpha,beta,PV) , if it selects alpha with ww31 and we press the click button it has to display the details ,if beta with ww31 and we press the click button it has to display their detail and so on ...

Can we modify the code according to this?

Please let me know sir , Is it possible to do?

I suggest you raise a new question

ok