VBA Match Cell Value to Worksheet Name and copy

[Bablu]

Hi,

I have some data and I would like to put an If condition to copy to worksheet if a cell value match to worksheet.

For example, If range("C2"). value match a Worksheet name then I would like to copy Range("A1.currentregion and paste to that particular worksheet.

So on Sheet1 cell C2 I have a value of Credit Swaps and I also have a worksheet name Credit Swaps. If the cell value match the worksheet name then I want to copy sheet1 (currentregion) and paste to Finalrow + 6 on Column N of the worksheet (credit Swaps)

Any lead would be appreciated. I can do most of it but I don't know how to match the cell value to worksheet unless I create each individually which defeats the purpose.

Thanks,

Bablu

 

[joe_pilgrim]

alvinwlh - thanks for the quick reply.

To answer your question(s):
Search from:
Tab: Employee_Roster tab
Index: Agent's name in column A

Copy from:
Tabs: Sales tab
Copy Data: entire row upon match found
Index: Agent's name in column D and E

Paste To:
Tabs: Respective agent tabs from search/match
Paste data: Entire row for matched agent

Hope this helps and should you need any additional information, please let me know. Thanks in advance!

JP

 

[joe_pilgrim]

alvinwlh - thanks for the quick reply.

To answer your question(s):
Search from:
Tab: Employee_Roster tab
Index: Agent's name in column A where Column A is list of employees

Copy from:
Tabs: Sales tab
Copy Data: entire row upon match found
Index: Agent's name in column D and E

Paste To:
Tabs: Respective agent tabs from search/match
Paste data: Entire row for matched agent

Hope this helps and should you need any additional information, please let me know. Thanks in advance!

JP

 

[alvinwlh]

Hi,


Paste To:
Tabs: Respective agent tabs from search/match
Paste data: Entire row for matched agent

One more question:
1) The "paste to" index will be based on the Tab name to the Agent's name found?
2) Paste the entire row to row 1?

 

[joe_pilgrim]

alvinwlh -

Paste To:
Tabs: Respective agent tabs from search/match
Paste data: Entire row for matched agent

One more question:
1) The "paste to" index will be based on the Tab name to the Agent's name found? >> Yes
2) Paste the entire row to row 1? >> No, row 3

Hope this helps.

JP

 

[alvinwlh]

Hi,

You can try:

Sub Employee_search()
Dim sh As Worksheet
 
x = 2
Do
 
With Sheets("Employee_Roster")
    Agent_name = .Range("A" & x).Value
End With
 
On Error Resume Next
Set sh = Sheets(Agent_name)
On Error GoTo 0
 
If Not sh Is Nothing Then
    With Sheets("Sales")
        Set c = .Range("D:E").Find(Agent_name, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                .Rows(c.Row).copy
 
                With Sheets(Agent_name)
                    .Rows(3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
                End With
 
                Set c = .Range("D:E").FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
Else
    R = MsgBox("Sheets " & Agent_name & " does not Exists", vbOKOnly)
End If
 
Set sh = Nothing
x = x + 1
 
Loop While Sheets("Employee_Roster").Range("A" & x).Value <> ""
 
End Sub

 

[joe_pilgrim]

alvinwlh - thanks for the assistance but my curiosity is getting the best of me before running the MACRO. Can you kindly explain what it is doing, if you do not mind?

 

[alvinwlh]

Sub Employee_search()
Dim sh As Worksheet
x = 2
 
Do
'Get the agent name from the Employee Roster
With Sheets("Employee_Roster")
    Agent_name = .Range("A" & x).Value
End With
 
'Set the variable sh to the Agenst's tab
On Error Resume Next
Set sh = Sheets(Agent_name)
On Error GoTo 0
 
'Check the existance of the Agent's Tab
'Do the following only when the Agent's Tab exist
If Not sh Is Nothing Then
    With Sheets("Sales")
        'Find the Agent name in the Sales Tab --> Range D:E
        Set c = .Range("D:E").Find(Agent_name, LookIn:=xlValues, lookat:=xlWhole)
        If Not c Is Nothing Then
            'If Agent's name found then record the first address
            firstAddress = c.Address
            Do
                'Copy the whole row after row found
                .Rows(c.Row).copy
 
                With Sheets(Agent_name)
                    'Paste the Copied row to the Agent's Tab --> row 3
                    .Rows(3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
                End With
 
                'Check again the Agent's name in the Sales Tab
                Set c = .Range("D:E").FindNext(c)
 
            'Loop the check until all apperance of Agent's name found in the sales Tab
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
Else
    'Msg Pop out if the Agent's tab does not exist
    R = MsgBox("Sheets " & Agent_name & " does not Exists", vbOKOnly)
End If
 
'Reset the variable sh
Set sh = Nothing
x = x + 1
 
'Loop back to get a new agent's name in Employee Roster
Loop While Sheets("Employee_Roster").Range("A" & x).Value <> ""
End Sub

 

[joe_pilgrim]

alvinwlh - thanks for your patience. the MACRO appears to work, however, it is only pasting one row for each agent matched.

 

[alvinwlh]

Hi,

Just to clarify again:

Copy from:
Tabs: Sales tab
Copy Data: entire row upon match found (one row only right?)
Index: Agent's name in column D and E

Paste To:
Tabs: Respective agent tabs from search/match
Paste data: Entire row for matched agent to row 3 (replace the existing one?)

Anything that missed out?

 

[joe_pilgrim]

Everything looks good just that it is only pasting one row, where it should copy and paste all rows when match is found. Does this clarify the confusion? Thanks in advance!