VBA Code for Match Cell Value to Worksheet Name of different workbook and paste data

[Gaurangg]

Hi,

I have some data and I would like to put an If condition to copy to worksheet if a cell value of workbook1 match to worksheet of another workbook2

For example, If range("C1"). value of Excel1, Sheet("Form") match a Worksheet name of Excel2 then I would like to copy Range ("B4:V52") of Excel1 and paste to that particular worksheet of Excel2 Range("B4:52").

Means, in Excel1(Workbook), worksheet named "Form", cell C1 I have a value of ABC and I also have a worksheet name ABC in different workbook Excel2. If the cell value of Excel1 Sheet("Form") match the worksheet name of Excel2 then I want to copy range from B4:V52 of Excel1, Sheet(Form") and paste to B4:V52 of the worksheet (ABC) of Excel2

Any lead would be appreciated. I can do most of it but I found debugging error. Below is my coding that i have tried.

Thanks,

Sub CopyItems() ' ' CopyItems Macro ' Copy items to sheet. ' ' Dim wbTarget As Workbook Dim wbTarget1 As Workbook Dim wbThis As Workbook Dim shThis As Worksheet Dim shThis1 As Worksheet Dim Wks As Worksheet Set wbThis = ActiveWorkbook Set shThis = wbThis.Sheets("Audit Form") Set shThis1 = wbThis.Sheets("Error Sheet") strName = shThis.Range("C1").Value Set wbTarget = Workbooks.Open("\\C:\Reports\Reports (Do Not Delete)\Daily Dumps\MTD Collation.xlsb") Set wbTarget1 = Workbooks.Open("\\C:\Reports\Reports (Do Not Delete)\Daily Dumps\MTD Error Collation.xlsb") For Each wkSht In Sheets If shThis.Range("C1").Value = wkSht.Name Then wbTarget.wksht.Range(B4:AO1503).ClearContents shThis.Range("C6:AP1505").CurrentRegion.Copy Destination:=wbTarget.wkSht.Range(B4:AO1503) wbTarget.Save wbTarget.Close wbTarget1.wksht.Range(B3:V52).ClearContents shThis1.Range("A3:U52").CurrentRegion.Copy Destination:=wbTarget1.wkSht.Range(B3:V52) wbTarget1.Save wbTarget1.Close End If wbThis.Activate Set wbTarget = Nothing Set wbTarget1 = Nothing Set wbThis = Nothing Next End Sub

<TBODY>
</TBODY>

 

[Gaurangg]

Please help if any correction required in above code.

 

[V_Malkoti]

Untested but try this on a copy:

Sub CopyItems()'
' CopyItems Macro
' Copy items to sheet.
'
'
    Dim wbTarget As Workbook
    Dim wbTarget1 As Workbook
    Dim wbThis As Workbook
    Dim shThis As Worksheet
    Dim shThis1 As Worksheet
    Dim Wks As Worksheet
    
    
    Set wbThis = ActiveWorkbook
    Set shThis = wbThis.Sheets("Audit Form")
    Set shThis1 = wbThis.Sheets("Error Sheet")
    strName = shThis.Range("C1").Value
    Set wbTarget = Workbooks.Open("\\C:\Reports\Reports (Do Not Delete)\Daily Dumps\MTD Collation.xlsb")
    'Set wbTarget1 = Workbooks.Open("\\C:\Reports\Reports (Do Not Delete)\Daily Dumps\MTD Error Collation.xlsb")
    
    For Each wkSht In Target.Sheets
        If shThis.Range("C1").Value = wkSht.Name Then
            wkSht.Range("B4:V52").ClearContents
            shThis.Range("B4:V52").CurrentRegion.Copy Destination:=wkSht.Range("B3:V52")
            wbTarget.Save
        End If
    Next
    
    wbTarget.Close
    wbThis.Activate
    
    Set wbTarget = Nothing
    'Set wbTarget1 = Nothing
    Set wbThis = Nothing
End Sub

 

[Gaurangg]

Hey Malkoti,

Thanks for your quick help. But i m sorry, it is not working. getting yellow highlighted debugging at "For Each wksht..." line and macro doesn't complete the action.

 

[V_Malkoti]

Sorry, I used wrong variable name for workbook. Change that line to:

For Each [COLOR=#000000]wkSht[/COLOR] In [COLOR=#0000ff]wbTarget[/COLOR].Sheets

 

[Gaurangg]

Thanks bro. Now code is working. But now the problem is It copies the entire worksheet with formulas as well. want to paste data as values of selected range only.

 

[V_Malkoti]

Try replacing

shThis.Range("B4:V52").CurrentRegion.Copy Destination:=wkSht.Range("B3:V52")

with

shThis.Range("B4:V52").Copy
wkSht.Range("B4:V52").PasteSpecial Paste:=xlPasteValues

 

[Gaurangg]

Thank You. Its working completely now. Thanks a lot.

 

[V_Malkoti]

Glad I could help.