Update Workbook 1 using information from Workbook 2 while preserving information on Workbook 1

JenJarchow

New Member
Joined
Feb 25, 2022
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Hello!

I am trying to design a VBA that will allow me to update Workbook 1 (Site Master Tracker Template) from Workbook 2 (SUSAR Master List_DO NOT EDIT). Easy enough, except I use Workbook 1 to cross check files at a location and when I update Workbook 1 with the new information from Workbook 2 (usually updated documents) I want to preserve the information in Workbook 1 and only add the NEW information from Workbook 2.

In Workbook 1 the information I want to preserve is found in columns A-K. In Workbook 2 the information I want to add are the new rows that have been inserted and colored red. Ideally I'd like to insert the new red rows and leave the other rows untouched. I want to be able to continuously update Workbook 1 whenever Workbook 2 has been updated with new rows. I hope this makes sense! Sorry I can't upload the workbooks themselves I hope the pictures suffice!
 

Attachments

  • Workbook 1.JPG
    Workbook 1.JPG
    131.8 KB · Views: 17
  • Workbook2.JPG
    Workbook2.JPG
    145.9 KB · Views: 20

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hello!

I am trying to design a VBA that will allow me to update Workbook 1 (Site Master Tracker Template) from Workbook 2 (SUSAR Master List_DO NOT EDIT). Easy enough, except I use Workbook 1 to cross check files at a location and when I update Workbook 1 with the new information from Workbook 2 (usually updated documents) I want to preserve the information in Workbook 1 and only add the NEW information from Workbook 2.

In Workbook 1 the information I want to preserve is found in columns A-K. In Workbook 2 the information I want to add are the new rows that have been inserted and colored red. Ideally I'd like to insert the new red rows and leave the other rows untouched. I want to be able to continuously update Workbook 1 whenever Workbook 2 has been updated with new rows. I hope this makes sense! Sorry I can't upload the workbooks themselves I hope the pictures suffice!
how is the data in worksheet 2 updated. Cell by cell, imported from elsewhere or copy/paste.
If cell by cell are all cells updated at the same time or one at time. If one at a time do you want the row to insert/update in worksheet 1 every time a cell is updated or just once. If just once what would be the last cell/column to be updated
 
Upvote 0
Hello!

Workbook 1 starts as a blank template and then I update columns H, I, and J manually as I review the documents present at the location.

I only visit each location monthly so once a month I want to run the macro code and update Workbook 1 with the new information from Workbook 2.

The last item to be updated in Workbook 1 would be either column I or J.

Let me know if you need more info! Thanks!
 
Upvote 0
you could try something like this. You will need to have both workbooks open at the same time. You can attach the code to a button in workbook 2 and run it once you have entered all your info into workbook2. Change the ws1 & ws2 names to that of your worksheet names and add the file extension of your workbooks to the workbookname in the code (I have assumed you did mean you have two separate workbooks rather than meaning you had two worksheets.)

VBA Code:
Sub Transf_Info()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Ans As range, Rng As range, cel As range
Dim lcol As Long

Set wb1 = Workbooks("Site Master Tracker Template")  'add file extension to name
Set wb2 = Workbooks("SUSAR Master List_DO NOT EDIT")  'add file extension to name
Set ws1 = wb1.Sheets(Worksheet1)  'change this to be your worksheet1 name
Set ws2 = wb2.Sheets(Worksheet2)  'change this to be your worksheet2 name

Set Rng = ws1.UsedRange

For Each cel In ws2.range("C1:C" & ws2.Cells(Rows.count, 3).End(xlUp).row)
    If cel <> "" Then
        Set Ans = Rng.Find(what:=cel, LookIn:=xlValues, _
            lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
             
            If Not Ans Is Nothing Then
              lcol = (ws2.Cells(3, Columns.count).End(xlToLeft).Column)
              ws2.range(ws2.Cells(cel.row, "C"), ws2.Cells(cel.row, lcol)).Copy
              ws1.Cells(Ans.row + 1, "C").Insert Shift:=xlDown
            End If
    End If
Next cel
End sub
 
Upvote 0
Hello!

Hmm this isn't quite right. When I copy and paste your code above, and make the necessary changes to workbook and worksheet names (you were right I'm working out of two workbooks!) I get a "Run-Time error '1004': Insert Method of Range Class Failed error. It doesnt like this line of code "ws1.Cells(Ans.Row + 1, "C").Insert Shift:=xlDown" (6 lines from the bottom).

Then when I close the visual basic window the screenshot attached is what I'm left with. Could you possibly modify the code to do the following:
- Ignore the header row - I dont need this copied
- Only insert the rows with new information
- Have it insert entire rows at a time and preserve all information already present in each column? (Clearly you can see where the rows inserted down in column C-G but did not move any of the information in Column A,B, H-K with it. When a new row is inserted I want the entire row length to move, not just column C-G).

I hope this makes sense! Thank you for all of your help with this! :)

J
 

Attachments

  • Shifted Cells.JPG
    Shifted Cells.JPG
    195 KB · Views: 11
Upvote 0
Hello!

Hmm this isn't quite right. When I copy and paste your code above, and make the necessary changes to workbook and worksheet names (you were right I'm working out of two workbooks!) I get a "Run-Time error '1004': Insert Method of Range Class Failed error. It doesnt like this line of code "ws1.Cells(Ans.Row + 1, "C").Insert Shift:=xlDown" (6 lines from the bottom).

Then when I close the visual basic window the screenshot attached is what I'm left with. Could you possibly modify the code to do the following:
- Ignore the header row - I dont need this copied
- Only insert the rows with new information
- Have it insert entire rows at a time and preserve all information already present in each column? (Clearly you can see where the rows inserted down in column C-G but did not move any of the information in Column A,B, H-K with it. When a new row is inserted I want the entire row length to move, not just column C-G).

I hope this makes sense! Thank you for all of your help with this! :)

J
To ignore header row just change c1 to start at c2. and if you want the info from "B" copied change them both to B ie "B2:B".......
The runtime error could be that the workbook is in someway protected towards the end. Its difficult to tell without a workbook to work from.
You would need some way to differentiate the updated information in workbook 2. Will they always be in RED or was that just for the purposes of illustrating what you wanted?
 
Upvote 0
To ignore header row just change c1 to start at c2. and if you want the info from "B" copied change them both to B ie "B2:B".......
The runtime error could be that the workbook is in someway protected towards the end. Its difficult to tell without a workbook to work from.
You would need some way to differentiate the updated information in workbook 2. Will they always be in RED or was that just for the purposes of illustrating what you wanted?
Hello!

Sorry for the delay! My personal computer was being worked on but I have it back now and I was able to attach the mini-sheets of both workbooks for you.

I had an idea that might be easier than what I originally asked (I could be wrong so please tell me if so!). What if we modified the code you've provided to read the workbook "ISF Master List" column K, and when a value is present, copy that entire row and insert it into workbook "ISF Test list"? Is that possible? Is that easier? Is there a way for the code to read column K and discern new values when new entries are added? To answer your question above, yes new entries will all have a unique date added to column K and will be in red. They will be added to "ISF Master List" workbook.

Please note that the mini sheet "ISF Master List" is only partial. I have included all columns but there were too many rows for it to create a mini sheet of the entire document.

ISF Master Reference List_DO NOT EDIT_downloaded.xlsm
ABCDEFGHIJK
1SectionSection Sub CodeDocument NameVersionDatedIn eISFIn Final ISFAt LocationeCopy SavedCommentsDate Master List Updated
20 TOC & Study Team Contact Sheet0.01Table of ContentsV111-Oct-21YesYes
30.02Study Team Contact SheetV1O4Oct21YesYes
41 Site Personnel Documentation1.01Site Signature and Delegation of Authority LogV1O4Oct21YesYes
51.02CV and Medical Licenses
61.02PI CV
71.02SC CV
81.02Sub-I CV
902 Site Procedural Documents and Forms2.01Site Procedural Documents and Foms
102.02Site Recruitment and Activation Plan
112.02SRRP Attachment EV14-Oct-21YesYes
122.03Source Data AgreementV118-Nov-21YesYes
132.04EMR ChecklistV115-Sep-20YesYes
142.04EMR Certified Copy MemoYesYes
152.04EMR Permission to Access FormYesYes
1603 Study Protocol3.01Study ProtocolV126-Apr-21Yes
173.01Final and Amendments
183.01Protocol Singature PageV118-Aug-21Yes
193.01Study Schedule Flowchart
203.01Inclusion/Exclusion ChecklistV118-Oct-21Yes
213.02Study Procedure Manuals
223.026MWT guideline & checklist V19-Sep-21Yes
233.02Blank eCRF V15-Oct-21
243.02EDC Completion Guidelines V15-Oct-21Yes
253.02RHC Guideline N/AN/A
263.02RHC WorksheetV112-Aug-21N/AN/A
2704 ICF and Subject Materials4.01Informed Consent, including translations (if any)
284.01Main ICF V1V115-Sep-21
294.01Newborn ICF V1V115-Sep-21
304.01Greenphire ICF V1V110-Sep-21
314.01Pregnant Partner NTF from AdvarraV19-Nov-20YesYes
324.01Subject Information Sheet and Informed Consent Form
334.01Signed Informed Consent Forms, if not filed with Source Documents or under 19.02
344.02Subject Materials, including translations (if any)
354.02Patient Letter V120-Jul-21YesYes
364.02PCP Participant enrollment letter V120-Jul-21YesYes
374.02Recruitment poster V18-Jul-21YesYes
384.02Study introduction trifold (for subjects) V122-Jul-21YesYes
394.02Thank You Card V120-Jul-21YesYes
404.02Patient CardV12-Jul-21YesYes
414.02Borg CR10 InstructionsV1N/AYesYesNot Printed in Binder - Provided by Jumo Health
424.02Borg CR10 ScaleV1N/AYesYesNot Printed in Binder - Provided by Jumo Health
434.03Greenphire Travel and ReimbursementsAlso available in Spanish if applicable
444.03Greenphire ClinCard Cardholder RideShare FAQV118-Apr-19YesYes
454.03Greenphire ClinCard Carrier EnglishV5Sep-20YesYes
464.03Greenphire ClinCard Travel Ref GuideV311-Oct-16YesYes
474.03Greenphire ConneX USA Travel Contact CardV1Jan-19YesYes
485 Regulatory 5.01Site Specific Regulatory Submission/Approval/AcknowledgementFolders are present but all information within will be provided and filed by siteFolders are present but all information within will be provided and filed by site
495.02FDA 1572V111-Aug-21
505.03Financial Disclosure
515.03PI FDF - V111-Aug-21
525.03Sub-I FDF - V1
535.03Sub-I -V1
545.04Other Site Specific Approvals
555.05Regulatory Correspondence
566 Ethics (IRB)6.01Ethics Initial CTA Submission/Approval/AcknowledgmentFolders are present but all information within will be provided and filed by siteFolders are present but all information within will be provided and filed by site
576.02Ethics Amendment CTA Submission/Approval/Acknowledgment
586.03Ethics Progress/Periodic/Clinical Study Reports Submission/Approval/Acknowledgment
596.03Initial IRB ApprovalSee IRB Letters Tab
606.03Ethics Study Deviation(s) Submission/Approval/Acknowledgment
616.03Ethics SAE Submission/Approval/Acknowledgment
626.04Ethics Expedited Safety Report Submission/Approval/Acknowledgment
636.05End of Study Submission/Approval/Acknowledgment
646.06Ethics GCP Compliance Statement
656.07Ethics Documenatation of Non-Voting Status (if not inlcuded in Submission/Approval/Acknowledgment)
666.08Ethics Correspondence
677 Legal & Financial Documentation7.01CDAFolders are present but all information within will be provided and filed by siteFolders are present but all information within will be provided and filed by site
687.02Indemnification
697.03Clinical Trial Agreement(s) Investigator and site, including translations
707.04Clinical Trial Agreement(s) Site Departments and Service Providers, including translations
717.05Other Legal and Financial documentation (W9, DPA, PAF if not included in CTA)
727.06Invoices and Payment Information
737.07Insurance
747.08Legal and Financial Corespondence
758 Safety8.01Investigator Brochure/Investigational Medicinal Product Dossier/SPC, translations (if any)V1617-Apr-21YesYes
768.02SAE and Pregnancy reporting forms
778.02Exposure During Pregnancy Reporting FormV1N/AYesYes
788.02SAE Reporting FormV1N/AYesYes
798.03Expedited Safety Reports
808.04PsiXchange Reference GuidelineYesYes
819 Laboratory (Central and Local)9.01Laboratory Manual (Central laboratory) V113-Nov-21YesYes
829.01Reference Ranges
839.01CV of Laboratory Head
849.01PPD Laboratories Collection Flow ChartV114-Sep-21YesYes
859.02Reference Rangse/Normal Values (Local Lab)N/A24Sug21
869.03Accreditation Records and/or Head of Laboratory CV (Local laboratory)
879.04Training / Meeting Materials
889.04Preclarus Lab Data Portal User GuideV220-Jul-18YesYes
899.05Bio-analytical Documentation
909.06Labeling
919.07Lists/Logs (e.g. record of retained body fluids/tissue samples (if any),
929.07Lab Samples Storage Temp LogV111-Oct-21YesYes
939.07Lab Samples Storage_Subject-Specific V111-Oct-21YesYes
949.07Shipment records
959.08Laboratory Correspondence
9610 Vendors/Local Department(s)/Third Party Provider(s) 10.01Almac IRT
9710.01Vendor Manual/ IxRS User Guides V15-Nov-21YesYes
9810.01Training/Meeting Materials
9910.01Lists/Logs/Vendor Correspondence
10010.02BAIM Institute
10110.02BAIM ManualV13-Nov-21YesYes
10210.02Training/Meeting Materials
10310.02Lists/Logs/Vendor Correspondence
10410.03BioTel Research
10510.03ECG acquisition guide V13-Aug-21YesYes
10610.03ECG site information form V117-Jun-21YesYes
10710.03ECHO data transmittal form V130-Aug-21YesYes
10810.03ECHO participant screening guideV130-Aug-21YesYes
10910.03ECHO participant screening guideV28-Feb-22YesYes15-Feb-22
11010.03ECHO quick reference guide V127-Jul-21YesYes
11110.03ECHO site questionnaire V123-Aug-21YesYes
11210.03Ambra eTransfer Site User GuideV1N/A
11310.03QMS Site User GuideV110-Oct-20
11410.03Site Validation/Qualification
11510.03ECG Test Transmission Successful EmailNoYes
11610.03ECHO - First Participant Ready NotificationNoYes
11710.03Lists/Logs/Vendor Correspondence
11810.03ECHO Peer Training LogV32-Jun-20YesYesNote: It says to fax a copy of the log to BTR at the bottom of the form, this is not required. The site maintains the log for their records24-Feb-22
11910.03ECG Peer Training LogV32-Jun-20YesYesNote: It says to fax a copy of the log to BTR at the bottom of the form, this is not required. The site maintains the log for their records24-Feb-22
12010.04Greenphire
12110.04Welcome to Clincard V1N/AYesYes
12210.04Welcome to ConneX V1Feb-20YesYes
12310.04ClinCard Reference Guide for CoordinatorsV7.3Oct-19YesYes
12410.04ConneX Travel Reference Guide for CoordinatorsV2Dec-21YesYes
12510.04Grenphire New User Training HandoutN/AN/AYesYes
12610.04Greenphire Generic ClinCardV3Jun-18YesYes
12710.04Greenphire ClinCard Messgae TemplateV5Feb-18YesYes
12810.04Greenphire CliCard Rideshare Message TemplateV1Apr-19YesYes
12910.04Patient Materials (filed in section 4.02)
13010.04Training/Meeting Materials
13110.04Lists/Logs/Vendor Correspondence
13210.05Jumo Health
13310.05Site Material N/AN/AN/AN/A
13410.05Patient materials (filed in section 04.02) N/AN/AN/AN/A
13510.05Training/Meeting Materials N/AN/AN/AN/A
13610.05Lists/Logs/Vendor Correspondence N/AN/AN/AN/A
13710.06Medable/PPD Digital
13810.06Medable Site Guide V122-Sep-21YesYes
13910.06Training/Meeting Materials
14010.06Lists/Logs/Vendor Correspondence
14110.07Medidata Rave
14210.07eCRF guideline V15-Oct-21YesYes
14310.07eCRF guideline V22-Dec-21
14410.07eCRF guidelineV323-Feb-22YesYes2-Mar-22
14510.07Training/Meeting Materials
14610.07Lists/Logs/Vendor Correspondence
14710.08PRA Safety Reporting System: psiXchange
14810.08Reference Guideline V2.0V26-Aug-20YesYes
14910.08Reference Guideline V2.0V34-Nov-20
15010.08Training/Meeting Materials
15110.08Lists/Logs/Vendor Correspondence
15210.09Local Department(s)/Third Party Provider(s)
15310.09Training/Meeting Materials
15410.09Lists/Logs/Vendor Correspondence
Test


Here is the destination workbook "ISF Test List":

ISF Test List.xlsm
ABCDEFGHIJK
1SectionSection Sub CodeDocument NameVersionDatedIn eISFIn Final ISFIn Site ISFIn PhlexEviewCommentsDate Master List Updated
20 TOC & Study Team Contact Sheet0.01Table of ContentsV111-Oct-21YesYesYersYes
30.02Study Team Contact SheetV1O4Oct21YesYesYrsYes
41 Site Personnel Documentation1.01Site Signature and Delegation of Authority LogV1O4Oct21YesYesYesYes
51.02CV and Medical Licenses
61.02PI CV
71.02SC CV
81.02Sub-I CV
902 Site Procedural Documents and Forms2.01Site Procedural Documents and Foms
102.02Site Recruitment and Activation Plan
112.02SRRP Attachment EV14-Oct-21YesYesYesYes
122.03Source Data AgreementV118-Nov-21YesYesYesNo
132.04EMR ChecklistV115-Sep-20YesYesYesYes
142.04EMR Certified Copy MemoYesYesYesYes
152.04EMR Permission to Access FormYesYesYesYes
1603 Study Protocol3.01Study ProtocolV126-Apr-21Yes
173.01Final and Amendments
183.01Protocol Singature PageV118-Aug-21Yes
193.01Study Schedule Flowchart
203.01Inclusion/Exclusion ChecklistV118-Oct-21Yes
213.02Study Procedure Manuals
223.026MWT guideline & checklist V19-Sep-21Yes
233.02Blank eCRF V15-Oct-21
243.02EDC Completion Guidelines V15-Oct-21Yes
253.02RHC Guideline N/AN/A
263.02RHC WorksheetV112-Aug-21N/AN/A
2704 ICF and Subject Materials4.01Informed Consent, including translations (if any)
284.01Main ICF V1V115-Sep-21
294.01Newborn ICF V1V115-Sep-21
304.01Greenphire ICF V1V110-Sep-21
314.01Pregnant Partner NTF from AdvarraV19-Nov-20YesYes
324.01Subject Information Sheet and Informed Consent Form
334.01Signed Informed Consent Forms, if not filed with Source Documents or under 19.02
344.02Subject Materials, including translations (if any)
354.02Patient Letter V120-Jul-21YesYes
364.02PCP Participant enrollment letter V120-Jul-21YesYes
374.02Recruitment poster V18-Jul-21YesYes
384.02Study introduction trifold (for subjects) V122-Jul-21YesYes
394.02Thank You Card V120-Jul-21YesYes
404.02Patient CardV12-Jul-21YesYes
414.02Borg CR10 InstructionsV1N/AYesYesNot Printed in Binder - Provided by Jumo Health
424.02Borg CR10 ScaleV1N/AYesYesNot Printed in Binder - Provided by Jumo Health
434.03Greenphire Travel and ReimbursementsAlso available in Spanish if applicable
444.03Greenphire ClinCard Cardholder RideShare FAQV118-Apr-19YesYes
454.03Greenphire ClinCard Carrier EnglishV5Sep-20YesYes
464.03Greenphire ClinCard Travel Ref GuideV311-Oct-16YesYes
474.03Greenphire ConneX USA Travel Contact CardV1Jan-19YesYes
485 Regulatory 5.01Site Specific Regulatory Submission/Approval/AcknowledgementFolders are present but all information within will be provided and filed by siteFolders are present but all information within will be provided and filed by site
495.02FDA 1572V111-Aug-21
505.03Financial Disclosure
515.03PI FDF - V111-Aug-21
525.03Sub-I FDF - V1
535.03Sub-I -V1
545.04Other Site Specific Approvals
555.05Regulatory Correspondence
566 Ethics (IRB)6.01Ethics Initial CTA Submission/Approval/AcknowledgmentFolders are present but all information within will be provided and filed by siteFolders are present but all information within will be provided and filed by site
576.02Ethics Amendment CTA Submission/Approval/Acknowledgment
586.03Ethics Progress/Periodic/Clinical Study Reports Submission/Approval/Acknowledgment
596.03Initial IRB ApprovalSee IRB Letters Tab
606.03Ethics Study Deviation(s) Submission/Approval/Acknowledgment
616.03Ethics SAE Submission/Approval/Acknowledgment
626.04Ethics Expedited Safety Report Submission/Approval/Acknowledgment
636.05End of Study Submission/Approval/Acknowledgment
646.06Ethics GCP Compliance Statement
656.07Ethics Documenatation of Non-Voting Status (if not inlcuded in Submission/Approval/Acknowledgment)
666.08Ethics Correspondence
677 Legal & Financial Documentation7.01CDAFolders are present but all information within will be provided and filed by siteFolders are present but all information within will be provided and filed by site
687.02Indemnification
697.03Clinical Trial Agreement(s) Investigator and site, including translations
707.04Clinical Trial Agreement(s) Site Departments and Service Providers, including translations
717.05Other Legal and Financial documentation (W9, DPA, PAF if not included in CTA)
727.06Invoices and Payment Information
737.07Insurance
747.08Legal and Financial Corespondence
758 Safety8.01Investigator Brochure/Investigational Medicinal Product Dossier/SPC, translations (if any)V1617-Apr-21YesYes
768.02SAE and Pregnancy reporting forms
778.02Exposure During Pregnancy Reporting FormV1N/AYesYes
788.02SAE Reporting FormV1N/AYesYes
798.03Expedited Safety Reports
808.04PsiXchange Reference GuidelineYesYes
819 Laboratory (Central and Local)9.01Laboratory Manual (Central laboratory) V113-Nov-21YesYes
829.01Reference Ranges
839.01CV of Laboratory Head
849.01PPD Laboratories Collection Flow ChartV114-Sep-21YesYes
859.02Reference Rangse/Normal Values (Local Lab)N/A24Sug21
869.03Accreditation Records and/or Head of Laboratory CV (Local laboratory)
879.04Training / Meeting Materials
889.04Preclarus Lab Data Portal User GuideV220-Jul-18YesYes
899.05Bio-analytical Documentation
909.06Labeling
919.07Lists/Logs (e.g. record of retained body fluids/tissue samples (if any),
929.07Lab Samples Storage Temp LogV111-Oct-21YesYes
939.07Lab Samples Storage_Subject-Specific V111-Oct-21YesYes
949.07Shipment records
959.08Laboratory Correspondence
ISF


Hope this helps! Thanks so much for sticking with me while we figure this out! :)
 
Upvote 0
Yes it would be possible to check column K for a value although the question then is how would the code differentiate what is an old value in column K or a new value. Is the date in column K always going to be the current date ie the date you are running the code or will it be a case of column K might not get update for a period of time and then the date may be a date in the past?

It may be more beneficial to have a helper column that puts a Y in the column if the data has already been moved to the other list
 
Upvote 0
Yes it would be possible to check column K for a value although the question then is how would the code differentiate what is an old value in column K or a new value. Is the date in column K always going to be the current date ie the date you are running the code or will it be a case of column K might not get update for a period of time and then the date may be a date in the past?

It may be more beneficial to have a helper column that puts a Y in the column if the data has already been moved to the other list
The date will be different and not necessarily the same day the macro is run. There will definitely be periods of time where column k will not be updated, and the date in column K may be in the past. I'm open to a helper column! That wasn't something I had considered!
 
Upvote 0
Ok I think this may work.
Create yourself a helper column in L and label it transferred. (in your source workbook)
Make sure to amend your workbook and sheet names in the code below. When the data is transferred the code should place a "Yes" into column L and this will prevent it being transferred again when you next run the code.

Apologies havent been able to test it so let me know how it goes.

VBA Code:
Sub transfer()
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cel As Range, Rng As Range, Ans As Range
Dim lcol As Long

Set wb1 = Workbooks(Workbook1)  'add workbook name & file extension
Set wb2 = Workbooks(Workbook2)  'add workbook name & file extension
Set ws1 = wb1.Sheets(Source)  'change this to be your worksheet1 name
Set ws2 = wb2.Sheets(Dest)  'change this to be your worksheet2 name

Set Rng = ws1.UsedRange

For Each cel In ws2.Range("K1:K" & ws2.Cells(Rows.count, 3).End(xlUp).row)
    If cel.row <> 1 Then
        If cel <> "" And cel.Offset(0, 1) = "" Then
         Debug.Print ws2.Cells(cel.row, 3)
            Set Ans = Rng.Find(what:=ws2.Cells(cel.row, 3), LookIn:=xlValues, _
                lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                 
                If Not Ans Is Nothing Then
                  ws1.Rows(Ans.row + 1).EntireRow.Insert
                  lcol = (ws2.Cells(1, Columns.count).End(xlToLeft).Column)
                  ws2.Range(ws2.Cells(cel.row, 2), ws2.Cells(cel.row, lcol)).Copy ws1.Cells(Ans.row + 1, 2)
                  ws2.Cells(cel.row, lcol + 1) = "Yes"
                End If
        End If
     End If
Next cel


End Sub
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top