If cell C2 in Sheet 1 matches a tab name then copy data from that tab cell A6 and paste into Sheet 1 cell A6

[StillUnderstanding]

Hello,

Please could I ask for some expert help! I am trying to copy data based on the following but cant seem to get it to work, it would be great if anyone could help me work this out.

If cell "C2" in "Sheet 1" matches a tab name then copy data from that tab cell "A6" and paste into "Sheet 1" cell "A6"

Here is the code I am using but its not pasting in sheet 1

Sub Copy_data()
Dim wkSht As Worksheet
For Each wkSht In Sheets
 If Sheets("Sheet1").Range("C2").Value = wkSht.Name Then
 Sheets("Sheet1").Range("a6").Copy Destination:=wkSht.Range("N" & FinalRow + 6)
End If
Next
End Sub

Would be grateful if anyone could help!

Thanks in advance!

 

[footoo]

Your code does not assign a value to FinalRow

 

[footoo]

Here's some alternative code :

Sub Copy_data()
Dim wkSht As Worksheet
On Error Resume Next
Set wkSht = Sheets(Sheets("Sheet1").[C2].Value)
If Err = 0 Then Sheets("Sheet1").[A6].Copy  wkSht.Range("N" & FinalRow + 6)
End Sub

 

[Marc L]

Hi, according to this explanation​

If cell "C2" in "Sheet 1" matches a tab name then copy data from that tab cell "A6" and paste into "Sheet 1" cell "A6"

the Excel basics way :​

Sub Demo1()
    If Evaluate("ISREF('" & [Sheet1!C2] & "'!A1)") Then [Sheet1!A6] = [INDIRECT("'"&Sheet1!C2&"'!A6")]
End Sub

 

[footoo]

Hi, according to this explanation​

the Excel basics way :​

Sub Demo1()
    If Evaluate("ISREF('" & [Sheet1!C2] & "'!A1)") Then [Sheet1!A6] = [INDIRECT("'"&Sheet1!C2&"'!A6")]
End Sub

I think that should probably be :

Sub Demo1()
If Evaluate("ISREF('" & [Sheet1!C2].Text & "'!A1)") Then Sheets([Sheet1!C2].Value).Range("N" & FinalRow + 6) = [Sheet1!A6].Value
End Sub

Of course, a value needs to be assigned to FinalRow

 

[StillUnderstanding]

Thanks @footoo and @Marc L I tried both of these and they do work but not as expected.

So here is what I am expecting to happen:-

Based on Cell C2 in Sheet 1, I am wanting the macro to go to the tab referenced and copy data from that tab and then place it into Sheet1 from cell A7

So if the tab name is called "Lookup" this the process would be as follows:-
1, On Sheet1 in cell "C2" I will have the name of a tab "Lookup"
2, When I run the macro it will go to tab "Lookup" and copy the contents of cells "N7" or could be a range such as "N7:P12"
3, It will paste the cells into Sheet1 from cell "A6"

I am just not sure how to get it to extract the data from the tab in question.

Any help would be appreciated.
Thanks

 

[StillUnderstanding]

Here's some alternative code :

Sub Copy_data()
Dim wkSht As Worksheet
On Error Resume Next
Set wkSht = Sheets(Sheets("Sheet1").[C2].Value)
If Err = 0 Then Sheets("Sheet1").[A6].Copy  wkSht.Range("N" & FinalRow + 6)
End Sub

@footoo I managed to wrok it out based on your code! Thank you.

Here is what I did and it brings back the results that I was expecting.

Sub Copy_data()
Dim wkSht As Worksheet
On Error Resume Next
Set wkSht = Sheets(Sheets("Sheet1").[C2].Value)
If Err = 0 Then wkSht.Range("ap9:au" & FinalRow + 30).Copy Sheets("Sheet1").[A6]
End Sub

 

[Marc L]

I think that should probably be :

Obviously not according to the initial post explanation, the reason why I have quoted it in my previous post …​

 

[footoo]

Obviously not according to the initial post explanation, the reason why I have quoted it in my previous post …​

Yes, I got the copy/paste the wrong way round (but the other suggested changes are valid).