Transfer columns from comparable workbook if cell value exists

[mkuznetsov1]

Hello,

I have a report fire every morning (1 workbook with 1 sheet) showing what active items need to be worked on (my team tasks are no color fill rows and all others team tasks are grey fill rows). Columns A and lets say J are blank for me to write status notes/dates in. Once I push it out of my department the row becomes grey, once it is done with all applicable departments the row goes away (based on column B "location" that is automatically updated). Currently I open both workbooks, look at column "D" or "description" to make sure it is the correct item and then manually copy/paste from old workbook to new workbook and save over old workbook. I also can't just copy paste the new sheet as the rows switch around. There is more information on the sheet such as current date so I do need to make sure to copy (or replace) from old to new. Is there a module code that I can paste in one workbook and run it every morning to save me about an hour a day. I still need to check all the lines so I don't need full automation but just a quick transfer.

If we call them old workbook, new workbook, and tasksheet and my location in column B is "mk", I am looking for a code that says something like "If a cell in column B contains "mk" in new workbook.taskheet copy cells in column A and J from old workbook to new workbook...and somehow put them on the correct row based on description column "D".

Any help would be a lifesaver!

[jdelano]

This is a super simple example, I placed a button on my "old workbook" and when clicked it opens the "new workbook" and loops through the data in each. If a match between old and new is found then it copies the data from old to new and then marks the row in new with an X (so you can see which have been modified) when it is finished it brings the "new workbook" to the forefront so that you can manually check it.

the code:


Dim newWorkBookFile As String
Dim newWB As Workbook
Dim newWBSheet As Worksheet
Dim oldWBSheet As Worksheet

Dim oldWBDataStartingRow As Long
Dim newWBDataRow As Long

Dim oldWBCurrentRow As Long
Dim newWBFoundMatchingDescRow As Long

Dim oldWBLastRow As Long

Dim oldWBCurrentDesc As String
Dim newWBCurrentDesc As String

newWorkBookFile = "C:\Users\jd310\Documents\NewProjects.xlsx"

Set oldWBSheet = ThisWorkbook.ActiveSheet

Set newWB = Workbooks.Open(newWorkBookFile)
Set newWBSheet = newWB.Sheets("Sheet1")

oldWBDataStartingRow = 2

' find the last row with data in the old workbook in column A
oldWBLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row

' loop through the rows in the current worksheet (old workbook)
' use the description column information
' and compare it to the description column in the new workbook
' if it is found then copy the data in cols C - F
' to the new workbook row

For oldWBCurrentRow = oldWBDataStartingRow To oldWBLastRow

' the description to compare is found in column B
oldWBCurrentDesc = oldWBSheet.Cells(oldWBCurrentRow, 2)

newWBCurrentDesc = " " ' set this to 1 space so the Do loop starts
newWBDataRow = 2 ' what row to start searching in the new workbook

' now loop through the new wookbook to see if this description is found
' stop when there is nothing contained in the description cell
Do While Len(newWBCurrentDesc) > 0
newWBCurrentDesc = newWBSheet.Cells(newWBDataRow, 2)

If oldWBCurrentDesc = newWBCurrentDesc Then
' found the description, now copy the information over to the new workbook
newWBSheet.Cells(newWBDataRow, 3).Value = oldWBSheet.Cells(oldWBCurrentRow, 3).Value
newWBSheet.Cells(newWBDataRow, 4).Value = oldWBSheet.Cells(oldWBCurrentRow, 4).Value
newWBSheet.Cells(newWBDataRow, 5).Value = oldWBSheet.Cells(oldWBCurrentRow, 5).Value
newWBSheet.Cells(newWBDataRow, 6).Value = oldWBSheet.Cells(oldWBCurrentRow, 6).Value

' mark the row in the new wookbook as updated so you know which rows to manually check
newWBSheet.Cells(newWBDataRow, 7).Value = "X"

Exit Do ' once a match is found there is no need to keep looking in the new workbook
End If
newWBDataRow = newWBDataRow + 1
Loop

Next oldWBCurrentRow

' show the new workbook so it can be manually checked
newWB.Activate




Here is what my spreadsheets look like

[mkuznetsov1]

Thanks, I will try it this morning and let you know if it works!

[jdelano]

You're welcome. I hope it at least gets you closer to your goal.

[mkuznetsov1]

Good Morning, I am having some issues...the macro seems to run (I modified it as needed to my rows/columns) but it seems to only run the first line and puts an X thats it. Additionally if you can see I have the "waiting" columns that advance each day to see how long I have had the item....with the copy row wouldn't it copy the entire row? I would need to just copy the A and L column cells. so it doesn't override some columns. Also would it keep the color fill?

Here are my actual files...appreciate it!

[jdelano]

I'll take a look at the files.

I changed this line

newWBCurrentDesc = newWBSheet.Cells(newWBDataRow, 4) to
newWBCurrentDesc = newWBSheet.Cells(newWBDataRow, 6) so that the same columns were being compared. It worked okay for me.

Let me know if that fixes it for you.

[mkuznetsov1]

Thanks jdelano! That did work....I would consider this solved....I guess I didn't realize as I am super new to this....are columns coded as "row"...that is what threw me for a loop.
Now I just need to get past the paste row and keep certain columns as they were.

[jdelano]

You're welcome. I'm happy to help. Don't hesitate to post your code if you get into a spot where you need a hand. Also, you can also look at the subbreddit r/vba on Reddit.com (I help there as well) it is more active than this forum. Just to give you another resource to pull from. Good luck with the rest of your project!

[Annaat29]

You're welcome. I'm happy to help. Don't hesitate to post your code if you get into a spot where you need a hand. Also, you can also look at the subbreddit r/vba on Reddit.com (I help there as well) it is more active than this forum. Just to give you another resource to pull from. Good luck with the rest of your project!
Thank you very much for your answer, it helped me

[jdelano]

Glad that it did.