Copy data to another workbook based on search results

Debzzz

New Member
Joined
Feb 3, 2019
Messages
4
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).

Code:
    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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi Debs - welcome to the forum

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64"]b[/TD]
[TD="width: 64"]c[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]d[/TD]
[TD="width: 64"]e[/TD]
[TD="width: 64"]f[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]h[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]do you count these two tables identical ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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
 
Last edited:
Upvote 0
[TABLE="class: cms_table, width: 576"]
<tbody>[TR]
[/TR]
[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64"]b[/TD]
[TD="width: 64"]c[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]d[/TD]
[TD="width: 64"]e[/TD]
[TD="width: 64"]f[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]I[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]h[/TD]
[TD]I[/TD]
[TD][/TD]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]do you count these two tables identical ?[/TD]
[TD]ie the rows are mismatched[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top