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,
<TBODY>
</TBODY>
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>