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

Hi,
 I need help for this ... it has to copy the details of pass and fail in sheet "newCorp"
It has to take details( from sheets like ww31 , ww33 and more) of last column and paste in newCorp sheet.

I tried this but its not working...


Sub Copy()
Dim str As String
str = Range("C4").Value
With Sheets("newCorp")
    With .Range("C5")
    .Formula = "=Iferror(VLOOKUP(B:B," & str & "!A:O,15,FALSE),"""")"
    End With
   End With
    Range("C5").Select
    Selection.AutoFill Destination:=Range("C5:C10")
    Range("C5:C10").Select
End Sub
new2.xlsm
Saha HAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
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)".
1
Saha HAuthor Commented:
Thanks....
I have changed those parameters, now also its not working...

 .Formula = "=Iferror(VLOOKUP(B5," & str & "!C:O,13,TRUE),"" "")"
0
Saha HAuthor Commented:
please can you help me .......
i am in need of that...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rob HensonFinance AnalystCommented:
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)"
0
Saha HAuthor Commented:
yes i changed  that also
0
Rob HensonFinance AnalystCommented:
How are you triggering the macro?

What result are you getting? Just saying its not working doesn't really help.
0
Saha HAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
The command button that was in the uploaded file had no code against it. Is the code actually running?
0
Saha HAuthor Commented:
this is how i tried...
snsnew.xlsm
0
Saha HAuthor Commented:
Please check the attached sheet..
0
Rob HensonFinance AnalystCommented:
Sorry can't download the .xlsm file while at work. Happy to look at it this evening when back at home.
0
Saha HAuthor Commented:
OK sir
0
Rob HensonFinance AnalystCommented:
Amended code:
Sub Copy1()
Dim str As String
str = Range("B4").Value
With Sheets("newCorp")
    With .Range("B5:B10")
    .Formula = "=IFERROR(VLOOKUP(A5," & str & "!C:O,13,FALSE),"""")"
    End With
End With

End Sub

Open in new window

Another reason why it wasn't working was because the sheet names in row 4 have a space after the sheet name so they don't match the actual sheet names. So in theory, it probably was working before but was giving blank as a result of the IFERROR element of the formula.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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

Open in new window

1
Saha HAuthor Commented:
Thank you sir ....  now its working.

Sir i have one more doubt, shall i ask?
0
Saha HAuthor Commented:
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?
0
Rob HensonFinance AnalystCommented:
I suggest you raise a new question
0
Saha HAuthor Commented:
ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.