Transfer data to closed WB; based on criteria

[Sleeplol]

Hello All,

I think this would be neat and super helpful for an application, so I'm hoping for some help.
Copy data from one WB and paste into a closed then re-closed WB based on conditions

4 Workbooks

• Master
• Ln1
• Ln2
• Ln3
• Ln4

All Ln WBs are set up the same but with different data.

The Master allows a Lead to write (update) to any of the Ln books
All data is stored in Master

Active WB Master Criteria:

• A1 value determines which Ln WB to open
• B1 value is exact matched to a value in Column G of a Ln WB on Worksheets("Prob_Answ")
• Workbooks("Master").Worksheets("Edit").Range("C1") is then pasted to Ln WB Cell in Column H on the same row as matched value in Column G.
• Then

Thanks for help with this

 

[JLGWhiz]

Since some of the narrative is unclear as to sheet names, file cell content and a couple of other details, the code is based on the assumption that unspecified sheet names will be Sheet 1. You can edit the code for name corrections. It was also assumed that all workbooks are in the same directory. The code runs from the Maste workbook.

Sub t()
Dim wb As Workbook, shm As Worksheet, fPath As String, fn As Range
Set shm = ThisWorkbook.Sheets(1) 'This is master workbook
fPath = ThisWorkbook.Path & "\"
Set wb = Workbooks.Open(fPath & shm.Range("A1").Value) 'Assumes A1 will contain name and file extension.
Set fn = wb.Sheets("Prob_Answ").Range("G:G").Find(shm.Range("B1").Value, , xlValues, xlWhole)
    If Not fn Is Nothing Then
        fn.Offset(, 1) = ThisWorkbook.Sheets("Edit").Range("C1").Value
    End If
End Sub

 

[Sleeplol]

Hi JLGWhiz,

Thanks for your reply and effort.
Well, your script does open and write to column H the Ln Workbook (offset 1 from the last used row in Column G)
but, it doesn't seem to be finding Master[B1] value in Ln1 column G and writing to the adjacent cell in column H.

 

[JLGWhiz]

Change the 'xlWhole' to 'xlPart' in the find statement and see if it finds B1 value. It is likely a leading or trailing space issue.

 

[Sleeplol]

Hi JLGWhiz,

I made the update, but it still transfers the value to the same position.

Prob_Answ data starts in on row 10 and the script always transfers the value to H2
I've trialed manually added in data (different single words; no spaces) to G1:G8 then the value is placed in G9

???

 

[JLGWhiz]

I am having difficulty understanding the problem.

fn.Offset(, 1) = ThisWorkbook.Sheets("Edit").Range("C1").Value

This line of code tells Excel to put the value of cell C1 from sheet Edit into the cell to the right of the matched value from cell B1 of the ActiveSheet. I don't know where it is finding the searched item, but the codewould indicate that it is found in G2 if the data is being posted to H2. If you are trying to run the code to find the same B1 value a second time, it will still find it in the original logaction in column G. Since you only specified one sheet name I have no idea if everything is on the same sheet or on different sheets. As I previously stated, I just assumed certain details when writing the code. Yoiu can get better results by:
1. If more than one workbook is involved, specify the workbook name, type and its file path.
2. If more than one worksheet is involved, specify the name of each sheet.
3. If the data is in a named table, provide the table name for each table.
4. When referring to cells, specify which sheet the cell is on.
5. Use names and cell references and avoid using indefinite pronouns like it, they, them, since in most cases those pronouns can be interpreted by the reader differently than the writer intended.
6. If your sheets have dupilcate data in search ranges, merged cells and/or formulas in the ranges where code will manipulate data, then include that information in your post.

Renenber that we cannot seet what you are working with, so you will need to give a good description, post a screen shot or provide a link to a free share server so we can understand how your data is organized.

 

[Sleeplol]

Fair enough;
So far you've managed to figure out exactly what I was attempting to describe.
I will start from the top.

WB1 = Master
WB2 = LN (1,2,3,or 4)

Criteria:
WB1.worksheets("Edit").Range("A1") : File path of WB2
WB1.worksheets("Edit").Range("B1") : Value to search in WB2.worksheets("Prob_Answ").Range("G:G")
WB1.worksheets("Edit").Range("C1") : Value to paste in WB2.worksheets("Prob_Answ").Range("H:H") on same row as found value in column G

What script does:

• Opens WB2
• Pastes WB1.worksheets("Edit").Range("B1") value into WB2.worksheets("Prob_Answ").Range("H:H")

Problem:

Pastes to first row in column H that has a blank in adjacent column G

Example:
WB1.worksheets("Edit").Range("B1").value = "Fourth Issue"
WB1.worksheets("Edit").Range("C1").value = "Fourth Solution"

The pic refers to WB2.Worksheets("Prob_Answ")
I need "Action Item" to be replaced with "Fourth Solution"
However, the script found the first blank in column G (G5) and pasted to the adjacent cell in H5

BTW,
thanks for taking so much time with this

 

[JLGWhiz]

I tested this code. It is almost identical to the one in Post #2, but I added sheet names for all references. Notice that I reverted to the xlWhole for to get an exact match. I have no idea why it would post the value of Edit!C1 on an offset row in your file. It places the value correctly in mine. i suggest you delete the old code and just copy this one into your code module.

Sub t3()
Dim wb As Workbook, shm As Worksheet, fPath As String, fn As Range
Set shm = ThisWorkbook.Sheets("Edit") 'This is master workbook
fPath = ThisWorkbook.Path & "\"
Set wb = Workbooks.Open(fPath & shm.Range("A1").Value) 'Assumes A1 will contain name and file extension.
Set fn = wb.Sheets("Prob_Answ").Range("G:G").Find(Trim(shm.Range("B1").Value), , xlValues, xlWhole)
    If Not fn Is Nothing Then
        fn.Offset(, 1) = ThisWorkbook.Sheets("Edit").Range("C1").Value
    End If
End Sub

 

[Sleeplol]

BOOM! That worked.

Looks like the only change was the sheet reference.

Many thanks JLGWhiz for writing this great for me. It will really help out my project.

 

[JLGWhiz]

You're welcome,
regards, JLG