Match 2 Cells On 2 Worksheets And Copy Contents Of Another Cell

Jan 3, 2014

Setup: I have 2 worksheets with between 8 and 9,000 rows on each

Column A in both worksheets Sheet1 and Sheet2 have an email address in them.

Not all addresses in sheet1 will be on sheet2 and visa versa

Column J on Sheet 2 contains a date

What I need: Column M on Sheet1 is empty

I need a formula to place into Column M on Sheet1 that will

Look at Sheet1:A

Locate the corresponding value on Sheet2:A

Pull the date from Sheet2:J same row into Sheet1:M

View 2 Replies


Copy Cell Contents Between Tabs Provided Cell References Match (VLOOKUP)

Apr 29, 2014

I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.

For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.

I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.

Screenshots for reference

First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]

View 2 Replies View Related

Copy Few Cells Of Data Between Worksheets If Values Match Between Columns

Mar 9, 2012

Been a while since I've worked with macros within excel and I can't seem to get what I remember being a basic macro to work whatsoever. I have 2 worksheets containing a massive amount of data and need to pull some cells from one into the other when values in 2 columns match.

To better explain, sheet1 has ID numbers in column G spanning for roughly 1700 rows. Sheet 2 has corresponding ID numbers in column EO. The data I need to copy over is in columns EP and EQ on sheet 2.

So I'm trying to build a macro to compare the values in Sheet1_Column_G to those on Sheet2_Column_EO and when a match is found, copy the value in EO and the adjacent entries in columns EP & EQ over into columns X, Y, and Z on sheet 1.

Couldn't get a VB function together to save my life so I tried working with MATCH & INDEX and didn't get very far either. I've included my current function below.

=IF(ISNUMBER(MATCH(G1606,Data!$EO$527:$EO$601,0)),INDEX(Data!$EO$527:$EQ$601,MATCH(G1606,Data!$EO$527:$EO$601,0)),"Not Found")

View 7 Replies View Related

VBA To Loop Column And Copy Cells Contents To Another Cell

Dec 17, 2013

I have a script that copies data to files based on many cells contents but where I am having a problem is creating series numbers for each file.


In my current code I copy files to folders by date and each folder I need series of files (Lab Testing series)

In column A1:A100 I have a series of numbers 01, 02, 03 ...100

Column B contains the Files to be saved

So this works fine

I now need the Script to do is to loop to Column A and select Cell 2 and do the File Copy again on the Next series


When Complete repeat until it reaches the end of column A

Since my Cells are populated by all the data in the workbook I thought at the end of my copy script I would take the next Cells data in A and put it in Cell H8 where all the constants are for the file names.

Column B is built using


View 1 Replies View Related

Link Cells In Adjoining Sheets If Contents Match

Apr 21, 2009

I have a list of items in column A of Sheet1 and the same list in column A of Sheet2. Both list will contain the same items, however not necessarily in the same order.

What I want the book to do is; when I click on an item in the list on Sheet1, it takes me to the same item in Sheet2.

Is this possible with VBA?

View 10 Replies View Related

Copy Cells To Worksheets With Worksheet Name In Opposite Cell

Aug 11, 2007

I am developing a Workbook where I import data to one worksheet and then disseminate it to other worksheets. I am not sure if I am overthinking the process, but I am having difficulty writing the macro and getting it to run through the entire list of rows.

There are 4 Columns in the one worksheet, named "Update":
Column A supplies the name of the worksheet that will be updated
Columns B-D contain the data that will update the sheets.

The process that I am trying to write the macro for is a follows:
1. Select/copy the range, starting with B1:D1 on "Update"
2. Navigate to the appropriate worksheet to be update, determined by A1
3. Identify the first blank row, starting with A5
4. Paste the data B1:D1 to A5:C5 (or next available line)
5. Navigate back to "Update" and begin the process again with the next row B2:D2

This process should continue until there is no value in Column B on the "Update" worksheet.

View 7 Replies View Related

Link Cell Contents Across Worksheets?

Feb 28, 2009

I work for an IT company who build their own PC's. And we use a simple Excel 2003 spreadsheet to piece-together and price-up PC's for retail, I've attached an example of the spreadsheet for you to see. It uses basic formulas so we can see at-a-glance things like VAT figures, margin, profit etc. Each PC we sell is contained within one Excel file, and each PC has it's own Workbook tab.

The problem I want to solve is whenever we change a component or price, we have to do each system manually, which gets very repetetive and time consuming. What I'd like to be able to do is change the price of, let's say, the motherboard, and have the price be reflected across each of the PC's using that same motherboard on each seperate tab. This same system would apply when we change a product name, product ID etc.

View 2 Replies View Related

Find, Match & Copy Data Between Worksheets

Apr 8, 2008

- I copy some records from a Database into an Excel sheet in workbook (say W1) in the Worksheet WS1.
- The i look up for values in a column (say column B) of W1 in another worksheet in another workbook(say W2) .
- If i find a match in Workbook 2(W2) ,Worksheet 1 , Row 2, Column A ; i look at the values in the Column B, F,G on the Row 2 itself.
- Each of these values can be found in the corresponding Worksheets 2;3;4 in the Workbook2(W2) .
- Then i need to pick up all these values from worksheet 1;2;3;4 in W2 and contatenate them and put them in the Workbook W1 against the Row .
- Similarly i process all the non zero rows in the workbook W1.

View 2 Replies View Related

Two Worksheets - Copy Value From One Worksheet To Another Based Upon Index Match

Apr 27, 2012

I think I could get this to work but the INDEX command is not returning an index of the row like it is documented. It is returning the value in the cell. In order to get this to work, I need to return the index of the row. I need to know what row the match found the item on because the value I need to copy is going to be in column J of that same row.

I have two worksheets within 1 excel document.1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)

1 worksheet is a hand filled and excel programmed worksheet (Name is website-upload)

The other worksheet is a table from a database connection worksheet. (Name is ODBC_Products)

Each worksheet has a Sku column in it.

website-upload: Column AODBC_Products: Column B

Each worksheet has a QTY column in it.

website-upload: Column KODBC_Products: Column J

Goal: I would like to search the Sku column ODBC_Products and find a match for a sku that is listed in the website-upload sku column and then when I find that match, display the qty from the ODBC_Products worksheet to the website-upload worksheet.

Notes: I add columns and delete columns all of the time on this worksheet so I would rather do this in excel vs. a macro. I figure this can be done with something like:

Returns N/A



Returns an Error



Here is an example of what I have: [URL]

The website-upload image says Website Qty column (J). That is supposed to say column (K)

View 9 Replies View Related

How To Match Number Strings In Cell Contents

Sep 3, 2013

I have a spreadsheet with 60k lines. One of the columns contains barcodes as per the example below, with a suffix of -XXX-XXX where the XXX are numbers. I want to highlight all the cells where the -XX-XX are the same number (as in the example below -303-303) how I can do this with a formula as I've around 30 spreadsheets each with 60,000 lines I need to sort through?


View 4 Replies View Related

Copy Cells / Range From Worksheets Positioned Between Two Worksheets

Jul 7, 2014

Let's say I have a workbook with 7 worksheets named, for example, "Instruction", "Begin", "Worksheet 1", "Worksheet 2", "Worksheet 3", "End", and "Data". (in that order)

What I want to do is run a macro to go to whatever worksheet that is in between "Begin" and "End" and copy, for example, cells $C$1:$D$10; then paste as formula into worksheet "Data" starting from cell C1 and then down a list (i.e., copied cells from "Worksheet 1" get pasted as formula into "Data" cells C1:D10; then copied cells from "Worksheet 2" get pasted as formula into "Data" cells C11:D20, and so on and so forth).

But if I were to add more worksheets (e.g., "Recipe" and "ToDo") positioned in between "Begin" and "End" and run the macro again, it'll either 1) re-copy all the formulas from the included worksheets back into "Data" including the formulas from the newly added/placed worksheets or 2) it'll add the formulas from the newly added/placed worksheets and paste into "Data" at the end of the list.

Can create the macro to run based on the position of worksheet, and not based on the name of worksheet, since ultimately there will probably be over 10 worksheets between "Begin" and "End".

View 4 Replies View Related

Compare Column C On 2 Worksheets If Partial Match Copy Row To New Worksheet

May 27, 2014

Basically, i have a workbook that contains 2 worksheets with stock data for two stock holding sites, we're consolidating our stores and need to confirm what stock is held at both sites. I need to compare column C on both sheets to find if an item exists on both sheets, and if so copy the entire row from Sheet 2 onto the next free row on a new sheet. Col C on each sheet contains the stock number, however, on Sheet 1 the stock number is abbreviated ot the final 7 letters, with Sheet 2 showing the FULL stock code.

I need whatever is in "Sheet 1:Col C" (for example '1234567') to be compared to whatever is in "Sheet 2:Col C", and if a match is made (for example 9999-00-1234567) then the row containing the match be copied to the next free row on Sheet 3.

View 1 Replies View Related

Search A Sheet For A Match And Then Copy All The Cells To The Right Of The Match

Jul 13, 2009

I need a macro that can search a sheet for a match and then copy all 7 cells to the right of the match. I have attached an example of the sheet that will serve as the database to search, and a userform example that will be similar to the userfrom that will display the copied cells when a match is found. I plan to copy and paste the 7 cells to a different sheet so that the userform can display the results with the control source property. I do not need a way to add to this database. I know very little about searching a database so.

View 6 Replies View Related

Macro To Validate Contents Of Cell And If Not Match Send Msg Box

May 21, 2014

Userform that people use. I need validating the users input into the form.

1. Need to validate that A1 only has 3 letters (Alpha)
2. Need to validate that A2 is not left blank
3. Need to validate A3 has only 10 digits

My state is that when the user hits the submit button in the user form the macro checks for these validations above and if everything is ok emails the sheet. However if the user havent inputed the data correctly a msg box will pop to let them know what is wrong. If thats the case i do not want the sheet emailed. below is the email code i'm using

HTML Code: 

Sub SendMail()
' Copy the sheet(1)
' Send Email
.SendMail Recipients:=Array(""), Subject:="Test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub

View 2 Replies View Related

Check On Cell Contents And If Match Change Another Cell

May 29, 2012

I want to check the contents of all the cells in S Column to see if it equals "Process Recover/Rebill"

If True, then Change corresponding cell in C Column to "Manual Correction"

I have this VBA and it doesn't look like anything is processing. It appears that I must have something missing in the If line..message about missing.

(tot_new is the number of rows of data) I begin in the 3rd row.

For Counter = 3 To tot_new
If Range("S" & Counter & ").Value = "Process Recovery/Rebill" Then
Range("T" & Counter & ").Value = "Manual Correction"
End If
Next Counter

View 3 Replies View Related

Index Match Offset - Find Contents Of Reference Cell

Jun 21, 2012

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.


I tried using G$3-1 but this doesn't appear to work.

View 1 Replies View Related

Copy Contents In 2 Cells - 1 Of Which Is A Date

May 7, 2014

In column A, i have cities.
In column B, i have dates.

I would like column D to have both the city and its corresponding date e.g. "paris (12/06/2013)"

However, I am getting "paris (41437)". Is there anyway to make the date appear properly?

View 3 Replies View Related

Copy Row Based On Contents Of 2 Different Cells.

Apr 5, 2009

I would like to do now would be to only copy the rows that also contain the word sold in column C. I guess that I could do this in two stages, first copying the rows based on the year then copy again based on the word sold in column C. It would be nicer to make a slight modification to my code and do it all in one step.

View 2 Replies View Related

Find Function Where Search Criteria Isn't Exact Match For Cell Contents

Dec 19, 2011

I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.

Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).

The relevant section of code attached below:

Dim role_count as range
Dim role as string

If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value

View 4 Replies View Related

Find And Copy Partial Contents Of Cells

Nov 19, 2007

I have a list of about 2 million phrases. The list was created by combining two lists in all possible combinations:

a list of about 800 street names
a list of about 1000 property names


church street flat
church street apartment
house church street
house king's road

what I would like excel to do is the following:

look at each cell and determine which of the 800 street names it contains. write that streetname into adjacent cell.

this would then leave me with the original column, where each of the 2 million cells has been assigned one of 800 streetnames.

for example:

church street flat | church street
church street apartment | church street
house church street | church street
house king's road | king's road

that's all. I think that can't be too difficult, and probably it has already been discussed here in the forum. unfortunately I did not know for which keywords to search, that is why I couldn't find the posts.

I hope somebody can help me as I need to get this sorted for work as soon as possible. All semi-automatic ways I could think of to accomplish this would take me days that I don't have.

View 9 Replies View Related

Fill Multi Cells In Row If Column Match Both Worksheets

Nov 6, 2009

I have an exported Excel worksheet1 from the parts database get every Friday. We get RFQ of parts list each week from multi customers about 3000+ parts as they send in MSword, MSoutlook or MSexcel to me I make into Excel worksheet2. I do not like to type in every part into the parts software I have my words for that software Cough Cough. The exported excel speadsheet tells the part numbers, Location, Qty, Price, ETC I would like to take the Excel RFQ list and have it populated from the other speadsheet

A1 PART number that on both spreadsheet
A2 QTY on both there request and our stock
A3 Price
A4 Location

So want a compare worksheet1 with worksheet2
A1 = A* the populate A2-A4 with worksheet1 data

View 5 Replies View Related

Copy Only Part Of Cells Contents Based On Condition?

Jul 6, 2014

I will have 2 columns. In column B, will be a list of invoice numbers. Now each one starts with the "job number" (Ex. 51APGC01) then a dash and unique number per invoice. (Ex. 51APGC01-01)

In column A, will be a list of Purchase Order numbers sent by the customer so we can bill. Now, sometimes we get one PO for multiple invoices, (Ex. 03, 05, 06, 07.....)

What I am looking to do, is when I get these PO's, I enter them in and then I print a report for our billing Dept. The way the output line (cell) will read, is Job Number, then each unique number: (51APGC01-03, 05, 06, 07)

There are multiple jobs and each one has it's own report, so the job number will be dynamic and defined else where in the workbook. I can define that. It's just getting the numbers after the "-" and putting them in only.

View 5 Replies View Related

VBA Copy Visible Cells Then Paste Contents As Values Over Same Location

Oct 4, 2012

Have a slightly unique issue that I can't seem to find the specific solution for. I am wanting to filter a range, using row 1, then copy the visible cells (Excluding the title row 1) and paste into the same location, so over the data as Values.

I can do the filtering part and the selection of the visible row. Only trouble is pasting it to the same location.

this is the code so far - anything better would be great

Sub Macro2()
Sheets("GF_Scoring Database").Activate
Dim extvalue As String
Dim rng As Range


View 2 Replies View Related

Copy Cell Contents And All Formatting

Nov 23, 2008

I am trying to copy a column of cells from one sheet to another, but also want to keep all the formatting. The origin sheet has times, but when I copy these to the destination sheet they are displayed as decimal numbers (using the code snippet below). I can change these back to times by formatting the cells using the format painter after the macro completes but I would like the VBA to do this for me. (using 2002 SP3).

View 4 Replies View Related

Copy Part Of Cell Contents

Dec 2, 2011

I need a way to copy part of contents from a cell, the cell contains product information like size, name and weight of a product, and I need a copy the weight to be put in it’s own cell, here’s a copy of one cell “E65 MAPP SKDV 5 DIGIT 90G ST.K” the information I need from this is “90” the number is always followed by a capital G, but it’s between two and three digits where the lowest is 55G and the highest is 300G. Is this possible?

View 6 Replies View Related

Copy Cell Contents To Textbox?

Mar 9, 2012

I would like to copy the contents of a cell from a sheet in workbook 1 into a textbox that is on a userform in workbook 2. This is what I have but I get a runtime error 438:

ActiveWorkbook.Sheets("ID").Range("a1") = Workbooks("Key.xlsm").userform1.TextBox1.Text

View 8 Replies View Related

Copy Contents Of Cell To Text Box

Mar 10, 2004

I need a macro to automatically copy the contents of a cell to a text box.

View 7 Replies View Related

Copy Based On Cell Contents Macro

May 13, 2013

Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.

Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.

What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.

Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:

Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]

View 4 Replies View Related

Copy And Paste Cell Contents From One Sheet To Another?

Jun 6, 2014

I have a line of code that returns a run-time error 1004 whenever it is passed through. All I am trying to do is copy and paste. I am missing some glaring error? (It is only a selection of the code to highlight the part I am having issues with. "maxdate" and "d" have been set)

Dim ws, ws1 As WorkSheets
Set ws = ThisWorkbook.Sheets("Data")
Set ws1 = ThisWorkbook.Sheets("Target")


View 5 Replies View Related

Copy Cell Contents From One Sheet To Another Within A Loop

Jul 8, 2014

I have a loop function that goes through a list of employees and i want to move their name to a different sheet(monday, tuesday...) so that I know who is in on what day. is there a simple way to have it add the next name to the bottom of the list?

View 3 Replies View Related

Copyrights 2005-15, All rights reserved