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

[JenJarchow]

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!

 

[gordsky]

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

 

[JenJarchow]

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!

 

[gordsky]

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.)

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

 

[JenJarchow]

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

 

[gordsky]

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?

 

[JenJarchow]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Section	Section Sub Code	Document Name	Version	Dated	In eISF	In Final ISF	At Location	eCopy Saved	Comments	Date Master List Updated
2	0 TOC & Study Team Contact Sheet	0.01	Table of Contents	V1	11-Oct-21	Yes	Yes
3		0.02	Study Team Contact Sheet	V1	O4Oct21	Yes	Yes
4	1 Site Personnel Documentation	1.01	Site Signature and Delegation of Authority Log	V1	O4Oct21	Yes	Yes
5		1.02	CV and Medical Licenses
6		1.02	PI CV
7		1.02	SC CV
8		1.02	Sub-I CV
9	02 Site Procedural Documents and Forms	2.01	Site Procedural Documents and Foms
10		2.02	Site Recruitment and Activation Plan
11		2.02	SRRP Attachment E	V1	4-Oct-21	Yes	Yes
12		2.03	Source Data Agreement	V1	18-Nov-21	Yes	Yes
13		2.04	EMR Checklist	V1	15-Sep-20	Yes	Yes
14		2.04	EMR Certified Copy Memo			Yes	Yes
15		2.04	EMR Permission to Access Form			Yes	Yes
16	03 Study Protocol	3.01	Study Protocol	V1	26-Apr-21	Yes
17		3.01	Final and Amendments
18		3.01	Protocol Singature Page	V1	18-Aug-21	Yes
19		3.01	Study Schedule Flowchart
20		3.01	Inclusion/Exclusion Checklist	V1	18-Oct-21	Yes
21		3.02	Study Procedure Manuals
22		3.02	6MWT guideline & checklist	V1	9-Sep-21	Yes
23		3.02	Blank eCRF	V1	5-Oct-21
24		3.02	EDC Completion Guidelines	V1	5-Oct-21	Yes
25		3.02	RHC Guideline			N/A	N/A
26		3.02	RHC Worksheet	V1	12-Aug-21	N/A	N/A
27	04 ICF and Subject Materials	4.01	Informed Consent, including translations (if any)
28		4.01	Main ICF V1	V1	15-Sep-21
29		4.01	Newborn ICF V1	V1	15-Sep-21
30		4.01	Greenphire ICF V1	V1	10-Sep-21
31		4.01	Pregnant Partner NTF from Advarra	V1	9-Nov-20	Yes	Yes
32		4.01	Subject Information Sheet and Informed Consent Form
33		4.01	Signed Informed Consent Forms, if not filed with Source Documents or under 19.02
34		4.02	Subject Materials, including translations (if any)
35		4.02	Patient Letter	V1	20-Jul-21	Yes	Yes
36		4.02	PCP Participant enrollment letter	V1	20-Jul-21	Yes	Yes
37		4.02	Recruitment poster	V1	8-Jul-21	Yes	Yes
38		4.02	Study introduction trifold (for subjects)	V1	22-Jul-21	Yes	Yes
39		4.02	Thank You Card	V1	20-Jul-21	Yes	Yes
40		4.02	Patient Card	V1	2-Jul-21	Yes	Yes
41		4.02	Borg CR10 Instructions	V1	N/A	Yes	Yes			Not Printed in Binder - Provided by Jumo Health
42		4.02	Borg CR10 Scale	V1	N/A	Yes	Yes			Not Printed in Binder - Provided by Jumo Health
43		4.03	Greenphire Travel and Reimbursements							Also available in Spanish if applicable
44		4.03	Greenphire ClinCard Cardholder RideShare FAQ	V1	18-Apr-19	Yes	Yes
45		4.03	Greenphire ClinCard Carrier English	V5	Sep-20	Yes	Yes
46		4.03	Greenphire ClinCard Travel Ref Guide	V3	11-Oct-16	Yes	Yes
47		4.03	Greenphire ConneX USA Travel Contact Card	V1	Jan-19	Yes	Yes
48	5 Regulatory	5.01	Site Specific Regulatory Submission/Approval/Acknowledgement			Folders are present but all information within will be provided and filed by site	Folders are present but all information within will be provided and filed by site
49		5.02	FDA 1572	V1	11-Aug-21
50		5.03	Financial Disclosure
51		5.03	PI FDF -	V1	11-Aug-21
52		5.03	Sub-I FDF -	V1
53		5.03	Sub-I -	V1
54		5.04	Other Site Specific Approvals
55		5.05	Regulatory Correspondence
56	6 Ethics (IRB)	6.01	Ethics Initial CTA Submission/Approval/Acknowledgment			Folders are present but all information within will be provided and filed by site	Folders are present but all information within will be provided and filed by site
57		6.02	Ethics Amendment CTA Submission/Approval/Acknowledgment
58		6.03	Ethics Progress/Periodic/Clinical Study Reports Submission/Approval/Acknowledgment
59		6.03	Initial IRB Approval							See IRB Letters Tab
60		6.03	Ethics Study Deviation(s) Submission/Approval/Acknowledgment
61		6.03	Ethics SAE Submission/Approval/Acknowledgment
62		6.04	Ethics Expedited Safety Report Submission/Approval/Acknowledgment
63		6.05	End of Study Submission/Approval/Acknowledgment
64		6.06	Ethics GCP Compliance Statement
65		6.07	Ethics Documenatation of Non-Voting Status (if not inlcuded in Submission/Approval/Acknowledgment)
66		6.08	Ethics Correspondence
67	7 Legal & Financial Documentation	7.01	CDA			Folders are present but all information within will be provided and filed by site	Folders are present but all information within will be provided and filed by site
68		7.02	Indemnification
69		7.03	Clinical Trial Agreement(s) Investigator and site, including translations
70		7.04	Clinical Trial Agreement(s) Site Departments and Service Providers, including translations
71		7.05	Other Legal and Financial documentation (W9, DPA, PAF if not included in CTA)
72		7.06	Invoices and Payment Information
73		7.07	Insurance
74		7.08	Legal and Financial Corespondence
75	8 Safety	8.01	Investigator Brochure/Investigational Medicinal Product Dossier/SPC, translations (if any)	V16	17-Apr-21	Yes	Yes
76		8.02	SAE and Pregnancy reporting forms
77		8.02	Exposure During Pregnancy Reporting Form	V1	N/A	Yes	Yes
78		8.02	SAE Reporting Form	V1	N/A	Yes	Yes
79		8.03	Expedited Safety Reports
80		8.04	PsiXchange Reference Guideline			Yes	Yes
81	9 Laboratory (Central and Local)	9.01	Laboratory Manual (Central laboratory)	V1	13-Nov-21	Yes	Yes
82		9.01	Reference Ranges
83		9.01	CV of Laboratory Head
84		9.01	PPD Laboratories Collection Flow Chart	V1	14-Sep-21	Yes	Yes
85		9.02	Reference Rangse/Normal Values (Local Lab)	N/A	24Sug21
86		9.03	Accreditation Records and/or Head of Laboratory CV (Local laboratory)
87		9.04	Training / Meeting Materials
88		9.04	Preclarus Lab Data Portal User Guide	V2	20-Jul-18	Yes	Yes
89		9.05	Bio-analytical Documentation
90		9.06	Labeling
91		9.07	Lists/Logs (e.g. record of retained body fluids/tissue samples (if any),
92		9.07	Lab Samples Storage Temp Log	V1	11-Oct-21	Yes	Yes
93		9.07	Lab Samples Storage_Subject-Specific	V1	11-Oct-21	Yes	Yes
94		9.07	Shipment records
95		9.08	Laboratory Correspondence
96	10 Vendors/Local Department(s)/Third Party Provider(s)	10.01	Almac IRT
97		10.01	Vendor Manual/ IxRS User Guides	V1	5-Nov-21	Yes	Yes
98		10.01	Training/Meeting Materials
99		10.01	Lists/Logs/Vendor Correspondence
100		10.02	BAIM Institute
101		10.02	BAIM Manual	V1	3-Nov-21	Yes	Yes
102		10.02	Training/Meeting Materials
103		10.02	Lists/Logs/Vendor Correspondence
104		10.03	BioTel Research
105		10.03	ECG acquisition guide	V1	3-Aug-21	Yes	Yes
106		10.03	ECG site information form	V1	17-Jun-21	Yes	Yes
107		10.03	ECHO data transmittal form	V1	30-Aug-21	Yes	Yes
108		10.03	ECHO participant screening guide	V1	30-Aug-21	Yes	Yes
109		10.03	ECHO participant screening guide	V2	8-Feb-22	Yes	Yes				15-Feb-22
110		10.03	ECHO quick reference guide	V1	27-Jul-21	Yes	Yes
111		10.03	ECHO site questionnaire	V1	23-Aug-21	Yes	Yes
112		10.03	Ambra eTransfer Site User Guide	V1	N/A
113		10.03	QMS Site User Guide	V1	10-Oct-20
114		10.03	Site Validation/Qualification
115		10.03	ECG Test Transmission Successful Email		No		Yes
116		10.03	ECHO - First Participant Ready Notification		No		Yes
117		10.03	Lists/Logs/Vendor Correspondence
118		10.03	ECHO Peer Training Log	V3	2-Jun-20	Yes	Yes			Note: 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 records	24-Feb-22
119		10.03	ECG Peer Training Log	V3	2-Jun-20	Yes	Yes			Note: 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 records	24-Feb-22
120		10.04	Greenphire
121		10.04	Welcome to Clincard	V1	N/A	Yes	Yes
122		10.04	Welcome to ConneX	V1	Feb-20	Yes	Yes
123		10.04	ClinCard Reference Guide for Coordinators	V7.3	Oct-19	Yes	Yes
124		10.04	ConneX Travel Reference Guide for Coordinators	V2	Dec-21	Yes	Yes
125		10.04	Grenphire New User Training Handout	N/A	N/A	Yes	Yes
126		10.04	Greenphire Generic ClinCard	V3	Jun-18	Yes	Yes
127		10.04	Greenphire ClinCard Messgae Template	V5	Feb-18	Yes	Yes
128		10.04	Greenphire CliCard Rideshare Message Template	V1	Apr-19	Yes	Yes
129		10.04	Patient Materials (filed in section 4.02)
130		10.04	Training/Meeting Materials
131		10.04	Lists/Logs/Vendor Correspondence
132		10.05	Jumo Health
133		10.05	Site Material	N/A	N/A	N/A	N/A
134		10.05	Patient materials (filed in section 04.02)	N/A	N/A	N/A	N/A
135		10.05	Training/Meeting Materials	N/A	N/A	N/A	N/A
136		10.05	Lists/Logs/Vendor Correspondence	N/A	N/A	N/A	N/A
137		10.06	Medable/PPD Digital
138		10.06	Medable Site Guide	V1	22-Sep-21	Yes	Yes
139		10.06	Training/Meeting Materials
140		10.06	Lists/Logs/Vendor Correspondence
141		10.07	Medidata Rave
142		10.07	eCRF guideline	V1	5-Oct-21	Yes	Yes
143		10.07	eCRF guideline	V2	2-Dec-21
144		10.07	eCRF guideline	V3	23-Feb-22	Yes	Yes				2-Mar-22
145		10.07	Training/Meeting Materials
146		10.07	Lists/Logs/Vendor Correspondence
147		10.08	PRA Safety Reporting System: psiXchange
148		10.08	Reference Guideline V2.0	V2	6-Aug-20	Yes	Yes
149		10.08	Reference Guideline V2.0	V3	4-Nov-20
150		10.08	Training/Meeting Materials
151		10.08	Lists/Logs/Vendor Correspondence
152		10.09	Local Department(s)/Third Party Provider(s)
153		10.09	Training/Meeting Materials
154		10.09	Lists/Logs/Vendor Correspondence
Test

Here is the destination workbook "ISF Test List":

ISF Test List.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Section	Section Sub Code	Document Name	Version	Dated	In eISF	In Final ISF	In Site ISF	In PhlexEview	Comments	Date Master List Updated
2	0 TOC & Study Team Contact Sheet	0.01	Table of Contents	V1	11-Oct-21	Yes	Yes	Yers	Yes
3		0.02	Study Team Contact Sheet	V1	O4Oct21	Yes	Yes	Yrs	Yes
4	1 Site Personnel Documentation	1.01	Site Signature and Delegation of Authority Log	V1	O4Oct21	Yes	Yes	Yes	Yes
5		1.02	CV and Medical Licenses
6		1.02	PI CV
7		1.02	SC CV
8		1.02	Sub-I CV
9	02 Site Procedural Documents and Forms	2.01	Site Procedural Documents and Foms
10		2.02	Site Recruitment and Activation Plan
11		2.02	SRRP Attachment E	V1	4-Oct-21	Yes	Yes	Yes	Yes
12		2.03	Source Data Agreement	V1	18-Nov-21	Yes	Yes	Yes	No
13		2.04	EMR Checklist	V1	15-Sep-20	Yes	Yes	Yes	Yes
14		2.04	EMR Certified Copy Memo			Yes	Yes	Yes	Yes
15		2.04	EMR Permission to Access Form			Yes	Yes	Yes	Yes
16	03 Study Protocol	3.01	Study Protocol	V1	26-Apr-21	Yes
17		3.01	Final and Amendments
18		3.01	Protocol Singature Page	V1	18-Aug-21	Yes
19		3.01	Study Schedule Flowchart
20		3.01	Inclusion/Exclusion Checklist	V1	18-Oct-21	Yes
21		3.02	Study Procedure Manuals
22		3.02	6MWT guideline & checklist	V1	9-Sep-21	Yes
23		3.02	Blank eCRF	V1	5-Oct-21
24		3.02	EDC Completion Guidelines	V1	5-Oct-21	Yes
25		3.02	RHC Guideline			N/A	N/A
26		3.02	RHC Worksheet	V1	12-Aug-21	N/A	N/A
27	04 ICF and Subject Materials	4.01	Informed Consent, including translations (if any)
28		4.01	Main ICF V1	V1	15-Sep-21
29		4.01	Newborn ICF V1	V1	15-Sep-21
30		4.01	Greenphire ICF V1	V1	10-Sep-21
31		4.01	Pregnant Partner NTF from Advarra	V1	9-Nov-20	Yes	Yes
32		4.01	Subject Information Sheet and Informed Consent Form
33		4.01	Signed Informed Consent Forms, if not filed with Source Documents or under 19.02
34		4.02	Subject Materials, including translations (if any)
35		4.02	Patient Letter	V1	20-Jul-21	Yes	Yes
36		4.02	PCP Participant enrollment letter	V1	20-Jul-21	Yes	Yes
37		4.02	Recruitment poster	V1	8-Jul-21	Yes	Yes
38		4.02	Study introduction trifold (for subjects)	V1	22-Jul-21	Yes	Yes
39		4.02	Thank You Card	V1	20-Jul-21	Yes	Yes
40		4.02	Patient Card	V1	2-Jul-21	Yes	Yes
41		4.02	Borg CR10 Instructions	V1	N/A	Yes	Yes			Not Printed in Binder - Provided by Jumo Health
42		4.02	Borg CR10 Scale	V1	N/A	Yes	Yes			Not Printed in Binder - Provided by Jumo Health
43		4.03	Greenphire Travel and Reimbursements							Also available in Spanish if applicable
44		4.03	Greenphire ClinCard Cardholder RideShare FAQ	V1	18-Apr-19	Yes	Yes
45		4.03	Greenphire ClinCard Carrier English	V5	Sep-20	Yes	Yes
46		4.03	Greenphire ClinCard Travel Ref Guide	V3	11-Oct-16	Yes	Yes
47		4.03	Greenphire ConneX USA Travel Contact Card	V1	Jan-19	Yes	Yes
48	5 Regulatory	5.01	Site Specific Regulatory Submission/Approval/Acknowledgement			Folders are present but all information within will be provided and filed by site	Folders are present but all information within will be provided and filed by site
49		5.02	FDA 1572	V1	11-Aug-21
50		5.03	Financial Disclosure
51		5.03	PI FDF -	V1	11-Aug-21
52		5.03	Sub-I FDF -	V1
53		5.03	Sub-I -	V1
54		5.04	Other Site Specific Approvals
55		5.05	Regulatory Correspondence
56	6 Ethics (IRB)	6.01	Ethics Initial CTA Submission/Approval/Acknowledgment			Folders are present but all information within will be provided and filed by site	Folders are present but all information within will be provided and filed by site
57		6.02	Ethics Amendment CTA Submission/Approval/Acknowledgment
58		6.03	Ethics Progress/Periodic/Clinical Study Reports Submission/Approval/Acknowledgment
59		6.03	Initial IRB Approval							See IRB Letters Tab
60		6.03	Ethics Study Deviation(s) Submission/Approval/Acknowledgment
61		6.03	Ethics SAE Submission/Approval/Acknowledgment
62		6.04	Ethics Expedited Safety Report Submission/Approval/Acknowledgment
63		6.05	End of Study Submission/Approval/Acknowledgment
64		6.06	Ethics GCP Compliance Statement
65		6.07	Ethics Documenatation of Non-Voting Status (if not inlcuded in Submission/Approval/Acknowledgment)
66		6.08	Ethics Correspondence
67	7 Legal & Financial Documentation	7.01	CDA			Folders are present but all information within will be provided and filed by site	Folders are present but all information within will be provided and filed by site
68		7.02	Indemnification
69		7.03	Clinical Trial Agreement(s) Investigator and site, including translations
70		7.04	Clinical Trial Agreement(s) Site Departments and Service Providers, including translations
71		7.05	Other Legal and Financial documentation (W9, DPA, PAF if not included in CTA)
72		7.06	Invoices and Payment Information
73		7.07	Insurance
74		7.08	Legal and Financial Corespondence
75	8 Safety	8.01	Investigator Brochure/Investigational Medicinal Product Dossier/SPC, translations (if any)	V16	17-Apr-21	Yes	Yes
76		8.02	SAE and Pregnancy reporting forms
77		8.02	Exposure During Pregnancy Reporting Form	V1	N/A	Yes	Yes
78		8.02	SAE Reporting Form	V1	N/A	Yes	Yes
79		8.03	Expedited Safety Reports
80		8.04	PsiXchange Reference Guideline			Yes	Yes
81	9 Laboratory (Central and Local)	9.01	Laboratory Manual (Central laboratory)	V1	13-Nov-21	Yes	Yes
82		9.01	Reference Ranges
83		9.01	CV of Laboratory Head
84		9.01	PPD Laboratories Collection Flow Chart	V1	14-Sep-21	Yes	Yes
85		9.02	Reference Rangse/Normal Values (Local Lab)	N/A	24Sug21
86		9.03	Accreditation Records and/or Head of Laboratory CV (Local laboratory)
87		9.04	Training / Meeting Materials
88		9.04	Preclarus Lab Data Portal User Guide	V2	20-Jul-18	Yes	Yes
89		9.05	Bio-analytical Documentation
90		9.06	Labeling
91		9.07	Lists/Logs (e.g. record of retained body fluids/tissue samples (if any),
92		9.07	Lab Samples Storage Temp Log	V1	11-Oct-21	Yes	Yes
93		9.07	Lab Samples Storage_Subject-Specific	V1	11-Oct-21	Yes	Yes
94		9.07	Shipment records
95		9.08	Laboratory Correspondence
ISF

Hope this helps! Thanks so much for sticking with me while we figure this out!

 

[gordsky]

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

 

[JenJarchow]

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!

 

[gordsky]

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.

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