An impossible search and paste?Maybe not

[Gil]

Hello
A lot of help needed please.

I have 3 Workbooks named WorkbookA, WorkbookB & WorkbookC. WbA has a user form, WbB has the data I want to find. WbC has the data across 3 sheets, examples attached.

In WbA my user form (Ctrl u to open) has command buttons to open & close WbB & WbC, There is also a button for closing the userform. These all work ok.

This is where I need the help.There is also a button I want to press which will look at the cells in WbB (other cells are populated with different data but need to be ignored) and find the matches in WbC, across 3 sheets.
When found I want the column heading of the matched data and the row data from column A & B to be copied and pasted in WbB column B cell below and column H adjacent column I.

I f this is too much to expect from the press of 1 button I am quite happy to break it down to individual searches as long as I get the copy & paste I am looking for. Which should look like Column Header/ Row Data Column A\Row Data Column B.

I hope that makes sense.

Gil

[Gil]

Hello
I am never going to understand VBA and all its intracacies. I can only pick at pieces and put them in to simple use , and learn from them, my workbook examples are basic but I have put them together with the information I have gained from VBAX. For this task I now realise that I may have bitten off more than I can chew as my post has passed on to page 2 with 50+ views and not 1 reply. If the task is too extreme can someone please tell me.
The truth will not hurt
Gil
ps I am not looking for sympathy. I believe the vision of your mission will be a result for all.

[lucas]

Gil, It just seems to me as if you have assembled the most complex situation that you can possibly think of and want to learn from it.

Why not start taking a stab at it and when you run into an obstacle, post a question about how to deal with that specific step.

The fact that your data is so jumbled in Book C and the fact that you want to copy Column A and B data to Workbook B where there is data in Column B complicate things.

[Gil]

Hello
I have put together the following vba code and it works. However there are 2 points I would like help with.

1. The search task needs to be done with a loop. I have struggled but got nowhere fast.
2. When the data is found the offset is -1 & -2 with a formula in both refering back to columns a & b. Is there any other option to offset.

The code here is also in the attachments

Private Sub SearchWorkbookCfromcellvaluesinWorkbookB_Click()
Workbooks("WorkbookB.XLS").Sheets("Sheet1").Activate

ActiveSheet.Range("B1:B1000").Select

Range("B1:B1000").Copy

Workbooks("WorkbookC.XLS").Sheets("Sheet2").Activate

ActiveSheet.Range("B1:B1000").Select

Range("B1:B1000").PasteSpecial

'I put this in because I think the loop works backwards
Dim lngLastRow As Long
lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
Cells(lngLastRow + 0, 2).Select


' This loop needs to run the following code
Do
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(0, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
Loop Until IsEmpty(ActiveCell.Offset(0, 2))


' These 3 will be deleted when the loop works

ActiveSheet.Range("B6").Select


Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(1, 0) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveSheet.Range("B11").Select

Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(1, 0) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveSheet.Range("B13").Select

Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(1, 0) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveSheet.Range("B1:B1000").Select

Range("B1:B1000").Copy

Workbooks("WorkbookB.XLS").Sheets("Sheet1").Activate

ActiveSheet.Range("B1").Select

Range("B1").PasteSpecial


ActiveSheet.Range("H1:H1000").Select

Range("H1:H1000").Copy

Workbooks("WorkbookC.XLS").Sheets("Sheet2").Activate

ActiveSheet.Range("B1:B1000").Select

Range("B1:B1000").PasteSpecial

'I tried to insert the goto last cell agin but got a conflict
ActiveSheet.Range("B6").Select


' This loop needs to run the following code
Do
Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(0, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
Loop Until IsEmpty(ActiveCell.Offset(0, 2))


' These 3 will be deleted when the loop works

ActiveSheet.Range("B13").Select


Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveSheet.Range("B19").Select

Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveSheet.Range("B22").Select

Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveSheet.Range("B1:C1000").Select

Range("B1:C1000").Copy

Workbooks("WorkbookB.XLS").Sheets("Sheet1").Activate

ActiveSheet.Range("H1").Select

Range("H1").PasteSpecial



End Sub

Please see the above run by opening all 3 files select WorkbookA, ctrlU opens the userform and press (Search WorkbookC).



Many thanks for your patience and good will.
Gil

[Gil]

Hello
I think I am getting somewhere. I seem to have sorted my loop problem. However I have had to set the search data at regular points.
This is the code I now have.You will have to substitute it in the above attachments.

Private Sub SearchWorkbookCfromcellvaluesinWorkbookB_Click()
Workbooks("WorkbookB.XLS").Sheets("Sheet1").Activate

ActiveSheet.Range("B1:C1000").Select

Range("B1:C1000").Copy

Workbooks("WorkbookC.XLS").Sheets("Sheet2").Activate

ActiveSheet.Range("B1:C1000").Select

Range("B1:C1000").PasteSpecial

'I put this in because I think the loop works backwards this first one does.

Dim lngLastRow As Long
lngLastRow = Range("B" & Rows.Count).End(xlUp).Row
Cells(lngLastRow - 0, 2).Select

Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveCell.Offset(-8, 0).Select

' This loop runs the following code in column B

Do
Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)

ActiveCell.Offset(-8, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))

ActiveSheet.Range("B1:C1000").Select

Range("B1:C1000").Copy

Workbooks("WorkbookB.XLS").Sheets("Sheet1").Activate

ActiveSheet.Range("B1").Select

Range("B1").PasteSpecial


ActiveSheet.Range("H1:H1000").Select

Range("H1:H1000").Copy

Workbooks("WorkbookC.XLS").Sheets("Sheet2").Activate

ActiveSheet.Range("B1:B1000").Select

Range("B1:B1000").PasteSpecial

'I tried to insert the goto last cell agin but got a conflict so found this option worked
ActiveSheet.Range("B20").Select


' This loop runs the following code
Do
Set Sh = Sheets("Sheet1")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find(Trim(Split(c)(0)), lookat:=xlWhole)
c.Offset(, 1) = Sh.Cells(2, Fnd.Column - 0) & "-" & Fnd.Offset(, -1) & "-" & Fnd.Offset(, -2)
ActiveCell.Offset(8, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, 0))

ActiveSheet.Range("B1:C1000").Select

Range("B1:C1000").Copy

Workbooks("WorkbookB.XLS").Sheets("Sheet1").Activate

ActiveSheet.Range("H1").Select

Range("H1").PasteSpecial



End Sub
I'm afraid it does not look as good as some, can someone briefly explain the indents etc and the purpose.( If you fancy you can tidy it up please)

My last problem is still to select the row data column A & B from the same row as the found data.

Gil

[lucas]

My last problem is still to select the row data column A & B from the same row as the found data.


That was the one that I thought would cause the most concern.

I don't have a good solution for it.

[Gil]

Lucas

To be truly honest I did not think I could get this far under my own steam. Now that I have, the last bit must be staring me in the face.( I must have been on every Excel reference on the internet so far)

Any advice on the presentation would be appreciated.
Gil