Copy data from one workbook to another based on criteria

**goranimo** · 09-02-2019, 06:46 AM

Hi

I hope you will help me with a code. It is really not that complicated I think, although not so easy to explain, but I will try.

I have two workbooks. One source and one target. Both workbooks are similar but with different information.
I want to copy information from Source workbook to Target workbook based on some criteria.

Both workbooks will be open when doing this, so no need to add path to the location on disk.

I have uploaded sample workbooks. The "Target example" sheet in Target workbook show how it will look like when the operation is done.

The criteria is that if a value in column D in Target workbook matches a value in column D in Source workbook.

If value in Column D match then:

In the same row as the match in Source workbook, check if there is any value in Column F.

If YES:

Operation 1:
Copy the cell value to Column F in Target workbook in the row where the matching number
is in that workbook.

Operation 2:
Copy the cell value from Column E in Source workbook and Paste (overwrite) it in the matching cell in Target workbook.

Operation 3:
Add this text to Column G in Target Workbook: "Copied from Source workbook"

If NO:

Add this text to Column G in Target workbook: "Found in Source workbook, but no Remarks"

If no match in Column D, add this text to column G in Target workbook: "Not found in Source workbook.

**maras_mak** · 09-02-2019, 09:31 AM

Hi, check my solution.

Please Login or Register  to view this content.

**Mumps1** · 09-02-2019, 10:08 AM

Place this macro in a standard module in your source workbook and run it from there. Before you run the macro, you will have to re-format column D in the Target workbook so that it matches the format in column D of the source workbook. In column D in the Target workbook, you can see that in some of the cells you have a little green triangle in the upper left hand corner of the cell. I got rid of these green triangles by copying and pasting the values from column D of the source workbook. I have attached a copy of the revised Target workbook. I have also saved this workbook with an "xlsx" extension since it doe not contain any macros.

Please Login or Register  to view this content.

**goranimo** · 09-03-2019, 06:17 AM

Thank you both!

Mumps1: I got your code to work, however it missed Part number 53482 in line 4. Too complicated for me to find why the code would miss that

maras_mak: Your code worked! I will have to try it out on more workbooks than only this sample to make sure it works like intended. I would like to ask if you would do a minor change to the code. I would like to fill the cells with colors instead using text in Column G. I tried to do it myself and I thought it would be straight forward to change the .Value with .Interior.Color, but it messed up everything

Instead of text in column G, i would like these colors to fill the cell:

"Not found in Source workbook" will be
Interior.ThemeColor = xlThemeColorDark1
Interior.TintAndShade = -0.249977111117893

"Copied from Source workbook" will be
Interior.ThemeColor = xlThemeColorAccent5
Interior.TintAndShade = 0.599993896298105

"Found in Source workbook, but no Remarks" will be
Interior.Color = 7961087

To be clear, no text in the cells in Column G, only fill the cell with colors. Is this possible?

Thanks a lot for your help! I appreciate it!

**Mumps1** · 09-03-2019, 07:58 AM

I suspect the problem is caused by the formatting in column D as I mentioned in my previous post. Here is the revised code to address the color fill. I have attached a copy of the Target file with the results of the macro. If you have a large data set, the approach in this macro will be very fast as it uses arrays instead of looping through the sheet data.

Please Login or Register  to view this content.

**maras_mak** · 09-03-2019, 08:03 AM

goranimo, I think it's better to put the code in a 'Target' workbook.
Look at the attachment.
Does it give the expected results?

Please Login or Register  to view this content.

**goranimo** · 09-06-2019, 05:50 AM

Thanks to both of you! I got both codes to work now.

I need to have the code in Source workbook as I have to use it on multiple target workbooks.

I will do some more testing on target workbooks before I mark this tread as solved.

**goranimo** · 09-06-2019, 07:54 AM

Ok, so I stumbled upon a couple of problems.

1.

The file name of target workbooks are dynamic, which make it a problem when the code refer to a specific target workbook name.
I don't know what the best solution is here. If I have the code in the Source workbook, then I can copy the target workbook name and past it to a cell in source workbook, and that cell can be linked in the code?
Then I have to copy target workbook file name and paste it in a cell in source workbook every time, but that will be an ok solution if it will work.

2.

Your codes works beautifully based on my sample, but I found that the target workbooks have different variations of numbers in the cells. This will be a problem if the code only look for exact match.
I've uploaded an example of how the part numbers may be listed in the target workbook.

Some cells contain 2 different numbers. The cell is wrapped. I want the code to pick up any of these two numbers in the source workbook, as they may be listed with only one of the numbers in source workbook.

Some cells contain the number + ":NOP". In source workbook, not all of the numbers are listed with ":NOP" in the end, and I want the code to pick up the number from source workbook
regardless of ":NOP" or not.

I understand if this is difficult, but if you have any ideas I would be very happy.

I really appreciate your help!

**Mumps1** · 09-06-2019, 09:45 AM

Can you attach an updated copy of the source workbook that contains Part Numbers that match the target file you posted in Post# 8?

**goranimo** · 09-11-2019, 05:46 AM

Mumps1:

I've uploaded updated versions of Target and Source workbooks.

I've included all eventualities that I can think of. In the Target workbook in Part Name column I have written what to look for in Source workbook just to make it easier to see.
The text is the same in Source workbook, although in Upper Case to see if the code works and the information is copied to Target workbook.

Please note that the Target workbooks will have Part Numbers stored as text as default. In Source workbook I can have whatever works best.

**Mumps1** · 09-11-2019, 08:17 AM

Based on the information you placed in column E of the target workbook, I don't think it will be possible to do what you are asking. Where there are 2 part numbers in column D, sometimes you want to find the first one and sometimes the second one. Sometimes you want to include "NOP" and sometimes not. Sometimes you want to find both. Macros depend on patterns to do their work. There doesn't seem to be any pattern in what you want to do unless I misunderstood.

**goranimo** · 09-20-2019, 07:37 AM

Mumps: I understand, it's getting very complicated

Anyway, when I tried to run your code in the samples I posted it seems like there are some errors.

Row 3: Internal comments is marked red which is correct (There is nothing in remarks), but if there is match in part number it should also copy the part name, which it didn't.

Row 6: Same problem as row 3.

Any solution to this?

Also, as I mentioned in post #8, the target workbook will vary What is the best solution to make it work? I tried to paste the Target workbook name in a cell in Source workbook and make the code pick up that, but it didn't work. I guess this is an opportunity, I just wrote the code wrong.

HTML Code:

**goranimo** · 11-08-2022, 10:04 AM

Originally Posted by Mumps1

Place this macro in a standard module in your source workbook and run it from there. Before you run the macro, you will have to re-format column D in the Target workbook so that it matches the format in column D of the source workbook. In column D in the Target workbook, you can see that in some of the cells you have a little green triangle in the upper left hand corner of the cell. I got rid of these green triangles by copying and pasting the values from column D of the source workbook. I have attached a copy of the revised Target workbook. I have also saved this workbook with an "xlsx" extension since it doe not contain any macros.

Please Login or Register  to view this content.

Hi Mumps.

I am trying to make this work in an other workbook as well, but the data is in different columns. I tried to figure out this code to make the necessary adjustments, but with no luck.

Could you please look at the attached Source and Target workbook and update the code so that it will work?

Look up the Target workbook item numbers in Source (database) workbook. If there is match, then copy (overwrite) Description, Stocking Line and List Price.

Any help will be appreciated!

Thank you.Source.xlsx Target.xlsx

**goranimo** · 11-09-2022, 03:06 AM

.. forgot to mark thread unresolved..

**Mumps1** · 11-09-2022, 07:32 AM

Could you please look at the attached Source and Target workbook

I don't see the target workbook.

**goranimo** · 11-09-2022, 07:52 AM

Hi Mumps1, they are here both here: Capture.JPG

**Mumps1** · 11-10-2022, 11:37 AM

In which columns in the Target sheet do you want to place the following comments:
"Copied from Source workbook"
"Found in Source workbook, but no Remarks"
"Not found in Source workbook."

**goranimo** · 11-11-2022, 02:18 AM

I don't need those comments.

**Mumps1** · 11-11-2022, 03:12 PM

Try:

Please Login or Register  to view this content.

**goranimo** · 11-14-2022, 04:49 AM

Perfect. Thank you. Really appreciate your help!

**Mumps1** · 11-14-2022, 08:56 AM

You are very welcome.

**goranimo** · 01-31-2023, 06:59 AM

Originally Posted by Mumps1

You are very welcome.

Hi Mumps.

I've experiencing problems with this code when there is only 1 row with data in Target workbook. Is this possible to fix? Debug in bold below.

Please Login or Register  to view this content.

**Mumps1** · 01-31-2023, 01:21 PM

Try:

Please Login or Register  to view this content.

**goranimo** · 02-01-2023, 03:16 AM

Thank you! Awesome.

**Mumps1** · 02-01-2023, 12:22 PM

My pleasure.

**Ssammy** · 02-08-2023, 07:04 AM

Hi Mumps.
hope you are doing well.

I am trying to make some changes to the code you share (see code below). I tried to figure out this code to make the necessary adjustments, but with no luck.
My sheets (NewToAdd and FEB23) share column C, I’m trying to update column F in FEB23 when cells in both sheet C have the same record.

Could you please look at the picture attached (I was unable to include the workbook, the system give me an error) NewToAdd and FEB23 workbook and update the code so that it will work? Also, let me know what make the different, I think is how you address the variants.
Thanks in advance.

Sub MatchData()
Application.ScreenUpdating = False
Dim dewsWS As Worksheet, srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String
Set srcWS = ThisWorkbook.Sheets("Source")
Set desws = Workbooks("Target.xlsx").Sheets("Target")
Dim LastRow As Long
arr1 = srcWS.Range("A2", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 16).Value
arr2 = desws.Range("B5", desws.Range("B" & Rows.Count).End(xlUp)).Value
Set rnglist = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr1, 1)
Val = arr1(i, 1)
If Not rnglist.Exists(Val) Then
rnglist.Add Key:=Val, Item:=i + 1
End If
Next i
For i = 1 To UBound(arr2, 1)
Val = arr2(i, 1)
If rnglist.Exists(Val) Then
If arr1(rnglist(Val) - 1, 3) <> "" Then
desws.Range("F" & i + 4) = arr1(rnglist(Val) - 1, 3)
desws.Range("J" & i + 4) = arr1(rnglist(Val) - 1, 16)
desws.Range("M" & i + 4) = arr1(rnglist(Val) - 1, 12)
End If
End If
Next i
Application.ScreenUpdating = True
End Sub

Capture - Sheet1.JPG
Capture - Sheet2.JPG

**Mumps1** · 02-08-2023, 10:44 AM

Please start you own new thread. Include a link to this thread if you feel it is useful. Please use code tags when posting code. As well, it is hard to work with pictures. Please attach a copy of your file (de-sensitized if necessary. See the yellow banner at the top of the this screen for instructions on how to attach a file.

**Ssammy** · 02-10-2023, 12:23 PM

Thank, I will do

**goranimo** · 02-14-2023, 06:03 AM

Originally Posted by Mumps1

My pleasure.

Hi again Mumps1. Sorry to bother you again, I have tried to figure out this myself. How can I change this code to add more criteria?

Here the data will be copied from source to target based on criteria if cell value in column A in source matches cell value in column B in target.

I want the exact same data to be copied, but I have 2 more criteria I would like to add in to the code.

I really appreciate if you have the opportunity to look into this.

Attachments: Target workbook, Source workbook and Criteria workbook (only for explanation)

**Mumps1** · 02-14-2023, 10:47 AM

The link you posted doesn't work. Please follow the instructions in yellow at the top of this page to post an attachment.

**goranimo** · 02-15-2023, 03:12 AM

Mumps1: Sorry, I updated the post with attachments.

**Mumps1** · 02-15-2023, 09:24 AM

Your source file does not contain any data in columns G and I and your target file does not contain any data in columns O and P. Please post updated files that contain data in those columns.

**goranimo** · 02-15-2023, 09:46 AM

Hi,updated with data.

**Mumps1** · 02-15-2023, 12:16 PM

Try:

Please Login or Register  to view this content.

**goranimo** · 02-20-2023, 10:35 AM

Works beautifully!

If there is data in Column B, O & P (all 3 of them) in Target workbook, but no match based on the criteria in Source workbook, could you add "Not Found" in column J?

If no data in Column O or P in Target workbook, then do nothing. (like the code is now)

**Mumps1** · 02-20-2023, 11:03 AM

could you add "Not Found" in column J?

Column J of which workbook?

**goranimo** · 02-21-2023, 02:21 AM

Oh sorry, Column J in Target.

**Mumps1** · 02-21-2023, 09:34 AM

Try:

Please Login or Register  to view this content.

**goranimo** · 02-22-2023, 08:15 AM

Edit:

It worked, however should only show "Not Found" if there is data in Column B, O & P in Target workbook AND no match based on the criteria in Source workbook.

Now it shows "Not Found" even if there is no data in Column O & P.

**Mumps1** · 02-22-2023, 09:49 AM

Try:

Please Login or Register  to view this content.

**goranimo** · 02-23-2023, 06:33 AM

Thank you! I hope this was the last time I have to bother you with this.

I greatly appreciate your help!

**Mumps1** · 02-23-2023, 09:47 AM

My pleasure.

**goranimo** · 02-27-2023, 10:42 AM

Please Login or Register  to view this content.

Mumps1, sorry to bother you, again..

After the last update i'm having this error if there is only data in cell B5 (or if no data in any cell in column B).

Subscript out of range (error in bold in the code)

If there are no data in column B I just want to let the code run finish without doing anything. I really appreciate if you could take a look at this.

**Mumps1** · 02-27-2023, 11:46 AM

Try:

Please Login or Register  to view this content.

**goranimo** · 02-28-2023, 03:01 AM

Hi,

It now worked if there is no data in B5, but I still get the Error if there is data in B5 (in Target) only.

**Mumps1** · 02-28-2023, 10:00 AM

Which line of code is highlighted when you click Debug?

**goranimo** · 03-01-2023, 02:41 AM

It is the line in bold in post #43. Line 7 from the bottom.

**Mumps1** · 03-01-2023, 10:08 AM

Replace this line of code:

Please Login or Register  to view this content.

with this one:

Please Login or Register  to view this content.

**goranimo** · 03-02-2023, 03:17 AM

Hmm, still the same error in the IF statement on bold below

Please Login or Register  to view this content.

**Mumps1** · 03-02-2023, 09:50 AM

Try:

Please Login or Register  to view this content.

**goranimo** · 03-03-2023, 07:28 AM

It works if there is only data in B5 IF Column B, O, P in Target matches A, G, I in Source.

If I change B5 or O5 in Target so that it not matches A, G and I in source I get the same error.

I'm sorry if you are getting tired of this

**Mumps1** · 03-03-2023, 09:17 AM

Replace this line of code:

Please Login or Register  to view this content.

with this one:

Please Login or Register  to view this content.

**goranimo** · 03-06-2023, 09:04 AM

When I did this change I got no error, but I get "not Found" in column J for all numbers not found in Source workbook even if the criteria in post #35 is not met.

However I can change the code back to what it was and just add a "-" in B6 if there is only data in B5. Then it will work

**Mumps1** · 03-06-2023, 10:05 AM

Sounds good.

**goranimo** · 03-06-2023, 10:06 AM

Thanks again.

**Mumps1** · 03-06-2023, 10:14 AM

My pleasure.

Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Re: Copy data from one workbook to another based on criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy Data from one workbook into another based on criteria

[SOLVED] Need code to copy data based on filter criteria to new workbook as save in a file location

Bookmarks

Bookmarks

Posting Permissions