Copy data to another workbook based on search results

[Debzzz]

Hi

I'm new here, I'm Debbie and hoping to get some help with a holiday and sickness planner I have been creating.

I am looking for a way to copy data from one worksheet to another in a different workbook (I'm using Excel 2010).

I have identical column headers in two worksheets, columns D through to Column I have data in. If the data in columns D to I matches the Data in Columns D to I on the other worksheet I want it to copy columns J to L into the other workbook. the data could be on different rows in each workbook, would anyone know of a way to do this please?

I tried putting a code into column B to use as the search region, but then I'd have to write code for each line and there are a lot, however the code I tried to write didn't work anyway (I'm fairly new to VBA).

    Sub FindandPaste()


    Dim codename As String
    Dim Finalrow  As Integer
    Dim i As Integer


   Workbooks.Open ("C:\Users\Margaret\Desktop\newest")


   codename = Workbooks("Master").Worksheets("Holiday Requests").Range("B6").Value
   Finalrow = Workbooks("Newest").Worksheets("Holiday Requests").Range("D1000").End(xlUp).Row




   For i = 2 To Finalrow
 
         If Cells(i, 1) = codename Then
    
             Cells("codename").Offset(6).Copy
             Cells(i, 10).PasteSpecial xlPasteFormulasAndNumberFormats
             End If
   
   Next i


   End Sub

Thanks in advance for any help with this

 

[oldbrewer]

Hi Debs - welcome to the forum

a	b	c			d	e	f
d	e	f			g	h	I
g	h	I			a	b	c
	do you count these two tables identical ?

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Debzzz]

Hi and thank you

The headers are identical

Name Department Date Requested Date From Date To Additional Information Accepted/Declined Date Accepted by



The data that it contains though is different. Its for holiday requests each member of staff has there own planner, when they want to request a holiday a user form pops up, they enter their requests into this and it populates the next row down in their requests from Name to additional information and also the next row down in a master workbook for managers to approve. I've got all that working. The data in the master document though contains everyone's holiday requests, were as the data in the other workbook only contains that one persons requests. What I want it to do when a manager accepts or declines the holiday request is to copy the last three columns in that row back to the users workbook, against the relevant request which is likely to be on a totally different row as to what it is in the master workbook, so it would need to match the data somehow to be able to paste it in the correct row.

Hope that makes sense.

Thanks

 

[dmt32]

Hi,
If you are read / writing data to / from a master workbook (database) across your network, I would suggest that you add a unique request number field for each entry to make it easier match the correct record.

Also to assist in development of your project, it would be helpful to forum if you could place a copy of your workbook in a dropbox & place a link to it here.

Dave

 

[oldbrewer]

a	b	c			d	e	f
d	e	f			g	h	I
g	h	I			a	b	c
	do you count these two tables identical ?	ie the rows are mismatched

<tbody>
</tbody>

 

[Debzzz]

yes they are identical but the rows are mismatched. I have pasted a link to the files and the table is on the holiday requests tab on both workbooks. To get into it the username is Debbie and the password is pw123

https://www.dropbox.com/s/srg2pdan8t7rmng/newest.xlsm?dl=0
https://www.dropbox.com/s/5zga12h94hlkq9j/Master.xlsm?dl=0

Thanks

 

[dmt32]

Hi Debbie,
Had a quick look at your project - as suggested it would make it easier to track records accross your workbooks if each entry had a record no.

I will see if i can work something up for you to play with with asap.

Dave

 

[Debzzz]

thank you, that's really appreciated. It originally started off as just the calendar, but as I've been slowly learning VBA, I've got overly ambitious with it, and realised it can make my job so much easier, if I can get it too work.