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

 

[taigovinda]

Not sure I totally follow what you are trying to do... but maybe this snippet helps:

dim wkSht as Worksheet
For Each wkSht in Sheets
  If Sheets("Sheet1").Range("C2").Value = wkSht.Name Then
    'do your thing, maybe Sheets("Sheet1").Range("A1").Copy Destination:= wkSht.Range("N"&FinalRow+6)
  End If
Next

 

[Bablu]

taigovinda,

Thanks for your response.

I think you understood what I was trying to do. Unfortunately, it didn't work.
I tried modifying it a bit but that didn't work either. I see an error message of block variable not set.

Actually, I would need the Sheet1 to be changed to Activesheet because not everytime the sheet would be named as Sheet1 as these sheets would be output of a pivottable.

I tried changing to the below

Dim activesheet As Worksheet
Dim wkSht As Worksheet
For Each wkSht In Sheets
If activesheet.Range("C2").Value = wkSht.Name Then

activesheet.Range("A1").CurrentRegion.Copy Destination:=wkSht.Range("A1")

End If
Next
End Sub

I just want to make sure it can copy and paste to the destination and I can account for finalrow later on.

Basically, if the value of C2 match a worksheet name then I want to copy activesheets data into that worksheet.

Thanks,

Bablu

 

[taigovinda]

Hi Bablu,

Try changing your code to this:

'ActiveSheet need not be dimmed - it is a VBA term that refers to whatever sheet is active at the moment
'Dimming ActiveSheet caused your error
Dim wkSht As Worksheet
For Each wkSht In Sheets
If ActiveSheet.Range("C2").Value = wkSht.Name Then 
 ActiveSheet.Range("A1").CurrentRegion.Copy Destination:=wkSht.Range("A1") 
'change the red part to wkSht.Range("N"&Rows.Count).End(xlUp).Offset(6) to go down 6 rows from the bottom-most filled cell in column N
 
End If
Next
End Sub

Read the comments in my code. Hope that helps - post back if not quite (or not even close ).

Tai

 

[Bablu]

Tai,

This works PERFECTLY . Thanks so much.

Bablu.

 

[rlsublime]

Hello,

I also had a similar question that I am having issues with. I have a tab named "Data" and 2 other tabs named Bill and Jim. Column A in the Data tab contains Names of employees (Jim and Bill) and all employees have a corresponding tab. What I am trying to do is match the names in column A of the "data" tab with the corresponding sheets that have the same names and then paste the names into their respective sheets in column A. Everytime I populate I am trying to get it to paste in the next empty row. In the "data" tab there might be 5 entries for Jim and 3 for Bill and I am trying to get the 5 Jim entries to paste in the Jim tab and the 3 Bill entries to paste in the Bill tab. Any help on this would be greatly appreciated as I have been struggling with it for the past few hours.

Thanks so much for your help in advance.

 

[joe_pilgrim]

Hey guys!

I have a some-what similar question to the original post. I am currently manually copying and pasting my data but would like to know if anyone can assist me with the following:

I have a workbook that consists of sales data. The workbook is then split into worksheets with respective agents. I am currently searching the "Sales" data tab for each agent under two columns and then copy/pasting their respective data unto their tab. This process is taking me too much time and is inefficient.

Can anyone provide me with a MACRO in VBA to accomplish this task:
- search agent name form list under column A
in "AGENT" tab as it has a ALL (100) agents contact info
- match the searched agent to the "Sales" tab and copy/paste entire row unto respective tab in workbook, however the agent's name may appear under two different columns and at times appears under both columns

Apologies for any confusion but below is an example to illustrate my dilemma:

"Sales" tab

Client's Name - Column A
Client's Address - Column B
Client's Phone - Column C
Agent Territory - Column D
Agent Assigned - Column E
more info assigned up to column AD

The search will occur under both Column D and Column E for the agent but again, the agent may appear on one of the columns or in both.

I will this helps and apologies for the novice question as I am no expert in VBA. Thanks in advance and God bless.

JP


-

 

[John Davis]

Hello,

I also had a similar question that I am having issues with. I have a tab named "Data" and 2 other tabs named Bill and Jim. Column A in the Data tab contains Names of employees (Jim and Bill) and all employees have a corresponding tab. What I am trying to do is match the names in column A of the "data" tab with the corresponding sheets that have the same names and then paste the names into their respective sheets in column A. Everytime I populate I am trying to get it to paste in the next empty row. In the "data" tab there might be 5 entries for Jim and 3 for Bill and I am trying to get the 5 Jim entries to paste in the Jim tab and the 3 Bill entries to paste in the Bill tab. Any help on this would be greatly appreciated as I have been struggling with it for the past few hours.

Thanks so much for your help in advance.

There probably are better ways, but maybe:

Sub rlsublime()
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Data").Activate

    With Range("A2:A" & lr)
    
        .AutoFilter Field:=1, Criteria1:="Jim"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Jim").Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
        
    End With
    
     With Range("A2:A" & lr)
    
        .AutoFilter Field:=1, Criteria1:="Bill"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("Bill").Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
        
    End With
   

End Sub

 

[joe_pilgrim]

Bump, any suggestions to my question from the community of experts?

 

[alvinwlh]

Hi,

Just to clarify:

Copy from:
Tabs: Different agent tabs
Copy Data: ??
Index: Agent's name in column D and E



Paste To:
Tabs: Sales tabs
Paste data: ??
Index: Agent's name in column A

Hey guys!

I have a some-what similar question to the original post. I am currently manually copying and pasting my data but would like to know if anyone can assist me with the following:

I have a workbook that consists of sales data. The workbook is then split into worksheets with respective agents. I am currently searching the "Sales" data tab for each agent under two columns and then copy/pasting their respective data unto their tab. This process is taking me too much time and is inefficient.

Can anyone provide me with a MACRO in VBA to accomplish this task:
- search agent name form list under column A
in "AGENT" tab as it has a ALL (100) agents contact info
- match the searched agent to the "Sales" tab and copy/paste entire row unto respective tab in workbook, however the agent's name may appear under two different columns and at times appears under both columns

Apologies for any confusion but below is an example to illustrate my dilemma:

"Sales" tab

Client's Name - Column A
Client's Address - Column B
Client's Phone - Column C
Agent Territory - Column D
Agent Assigned - Column E
more info assigned up to column AD

The search will occur under both Column D and Column E for the agent but again, the agent may appear on one of the columns or in both.

I will this helps and apologies for the novice question as I am no expert in VBA. Thanks in advance and God bless.

JP


-