Autofill From Another Workbook By Matching Cell Contents

Jan 12, 2007

In the attachment, I have two different workbooks named 1099 and SJC. What I would like to automate if possible is to have Column F in 'SJC' autofill by matching the Vendor # (Column A) values to the '1099' Account Number (Column A). The values that need to be copied cover are located in Column C. Hope that is clear enough.

As an example:
I've highlighted one row in yellow to show what I need done. For Vendor No. A1W01 from '1099', the value I need copied over to Column F of SJC is 920.61. I need this automated because I have a collection of files like this that I need to link in this way and they are all quite large (the attached versions are stripped in order to meet the size requirements at this forum).

View 3 Replies


Post Codes To Autofill Suburb Cell And Matching More

Apr 7, 2008

I am working on a large volume spreadsheet comprising of the usual mailing list fields such as first name, surname, company, address1, address 2 , suburb, state, postcode etc etc

Is there a way for Excel to auto fill a line of details (or display a list the matching entries)?

this way, i can start to enter a semi-unique field such as surname, company or address and a list of matching entries pop up for me to select the best fit.

I then would fill in the remaining data.

View 9 Replies View Related

Replace Cell Contents Based On Matching Data In Another Cell (On Another Sheet)?

Apr 28, 2014

I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.

I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?

In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.

View 5 Replies View Related

Get Cell's Contents From Workbook With ADO

Nov 3, 2008

I would like a code that will look for a workbook and then look in cell a5 and return number to a45 of active work sheet.

I would also need it to add a6 + a7 + a8 and return sum to a46

View 14 Replies View Related

Copy Cell Contents In Given Order From One Workbook To Another?

Apr 5, 2013

How can i copy contents of cells in desired format from one workbook to another in the following format with VBA code :


A!H1 to B!K1
A!H2 to B!L1
A!H3 to B!K2
A!H4 to B!L2

View 3 Replies View Related

Clear Content Based On Contents Matching?

May 25, 2014

I have a column with various values in, string and numeric. The strings I want to keep have "," in and letters one side and "space" on the other. All the cells I want cleared do not have ","

The rest of what I'm using works fine accept this line which forces an error.


View 5 Replies View Related

Copy Contents Of Userform Into The Matching Sheet

Oct 5, 2009

I have a userform in a workbook and depending on what selection the user makes from a combo box, I want the data to get pushed into the relevant sheet.

I did think I could get away with
If Me.cmbtype.Value = "Tool" Then
Set ws = Worksheets("Tools")
ElseIf Me.cmbtype.Value = "Guide" Then
Set ws = Worksheets("Guides")
etc but its now not copying data into Tools if I selected Tool from cmbtype (but when I select Guide it does appear to work).

The rest of the code is the same regardless of what type they select from cmbtype, its just which sheet the data gets copied into that changes.

View 9 Replies View Related

AutoFill One Column In Destination Workbook

Jun 15, 2006

I have the following VBA code that will copy data from one workbook to another.

Sub copytoanotherworkbook()
Dim smallrng As Range
Dim destrange As Range
Dim destWB As Workbook
Dim Lr As Long
Application. ScreenUpdating = False
If bIsBookOpen("P&WM Estimate Tracking Sheet.xls") Then
Set destWB = Workbooks("P&WM Estimate Tracking Sheet.xls")
Set destWB = Workbooks.Open("N:Estimate SheetP&WM Estimate Tracking Sheet.xls")
End If
Lr = LastRow(destWB.Worksheets("Tracking Sheet")) + 1
Set sourceRange = ThisWorkbook.Worksheets("Links").Range("A1:L1")
Set destrange = destWB.Worksheets("Tracking Sheet").Range("A" & Lr)
destrange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

The problem I am having is that when it copies the data to the last line of the destination workbook, I have a formula in column I of the destination workbook that calculates days remaining. How would I go about creating an autofill to fill the formula to only the last row of data? I've read some posts on this, but I don't think they deal with what I need.

View 7 Replies View Related

Saving Cell Contents To XML File Saved As Current Workbook File Name

Dec 12, 2012

I have numerous spreadsheets that I need to open and unhide a sheet, that has XML data stored in cell A1. What I need to do is copy that data in cell A1 and paste it into a text document and save that as an XML file saved as the XLS workbook name with a date stamp.

I'm running into many issues, the main issue is the saving as current file name, and the formatting of the text/XML file.

Here is my current code, which doesn't reference the current file name and is just very generic. Once I get the saving as file-name correct and the formatting of the xml file correct, I will work on it a bit more.

Sub Test()
Dim Rng As Range
Dim wb As Workbook
Set Rng = Range("A1:A2")
Set wb = Workbooks.Add
With wb


View 4 Replies View Related

Matching Cell Formats Based On Matching Cell Values

Jan 28, 2010

I have a cell (A1) that contains text.
That text will match a single value in a reference list (i.e. range of cells e.g. Z1:Z26).
When the match occurs (e.g. A1 = Z16), I want the format of the reference cell (fill color, text color) to apply to the cell of interest (A1).
The reference cells are manually formatted.

I know that I could create a long list of conditional format formulas instead of using the reference, but it seems that there must be some way to tell excel: if the cell value contents are equivalent, the formatting should be as well.

View 9 Replies View Related

Bank Reconciliation - Many To One Matching And Partial Cell Matching

Nov 19, 2009

programming 2 macros in a bank reconciliation sheet I am trying to create.

Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.

The following two situations can occur and which need to be covered with a macro.

Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.

View 12 Replies View Related

Compare Cell Contents With Given Range Of Contents

Dec 30, 2013

I have attached a workbook stating my problem.


View 10 Replies View Related

Return Contents Of 1 Cell Based On Contents Of Another Cell?

Mar 12, 2014

I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.

View 3 Replies View Related

Macro To Clear Contents Of Workbook

Dec 8, 2011

I have a workbook that I re-use on a weekly basis. The header information must stay intact (Rows 1 and 2). I recorded a macro that would clear all tabs in the workbook, and that macro is:

Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlUp

Which made me curious if there was a VBA code to say "Clear Worksheet" as opposed to having to explicitly list the cells that need to be cleared?

View 6 Replies View Related

When Open Workbook Delete Contents

Feb 20, 2009

ive tried creating a macro/code that when you open a certain workbook it automatically deletes the contents. you see i have a workbook containing 100 sheets that i need to update each week but i have to delete all the contents first, is there a quick way to do this when i open the workbook up?

View 9 Replies View Related

Encoding Workbook Contents And Then Decoding Using Same Password

May 8, 2014

Like am having a excel sheet which has N number of cells which cells contails data and numbers and formulas..By using macro how i can first Encode the workbook contents and then decode the workbook by using the same password.

View 8 Replies View Related

Getting Contents Page To Exclude First Three Tabs Of Workbook

Apr 11, 2012

I have produced a table of contents using the following suggested code:- [URL] .....

Is there a way of getting the contents page to exclude the first three tabs of the work book.

At the moment my table of contents is including the cover sheet and table of contents.

View 4 Replies View Related

Move Worksheets To Workbook Matching A Criteria

Aug 14, 2006

What I want to try and do is move certain worksheets if their name is changed into another workbook. So there are two scenarios:

1. If any worksheets name is changed to begin with "Old", then it is automatically moved to a workbook called "OldPlans"
2. If any worksheets name is changed to begin with "Expired", then it is automatically moved to a workbook called "ExpiredPlans"

When worksheets are moved to new workbook I would like them to be positioned towards the end of the workbook. So I am not sure how to go about this, first would it have to have a Name Change Event or something to trigger off what gets moved?

View 5 Replies View Related

Populate Workbook With Matching Data From Other Workbooks

Dec 10, 2006

I have a one excel workbook with columns customer id , customer name, customer address
and another excel work book with columns customer id , account name, account type.

I am in need to produce a new excel workbook with datas from above said 2 workbooks with customer id column as reference by macro programming in excel.

So the new workbook should have data from the columns customer id, customer name and account name

View 5 Replies View Related

Excel 2007 :: Extract File Contents To Workbook

Mar 17, 2014

I have large number of sql files in various folders in my windows7 laptop.

I have extracted the file names with full path in column A in an excel work book.

For each row, I now need to parse the file and extract the file contents and put it in column B, for the respective row.

I want the entire query content to be populated in the row without parts of query to spillover to other column (based on comma etc in the query)

I am using excel 2007.

View 3 Replies View Related

Find And Move Matching Names On Sheet2 Of Workbook

Oct 21, 2013

Identical Data exists on Sheets1 and 2 (A3:V50)
A3:A50 = human names
B3:V50 = numeric values

I'm trying to VBA code:

If human names on Sheet2(A3:A5) match human names on Sheet1(A3:A5)
Move Sheet2 matching name row(s) to the bottom of the data range on Sheet2.

Sam 4 4 4 4 4 4 4 4 4 36 4 4 4 4 4 4 4 4 4 36 72
Bill 4 4 4 5 4 4 4 5 4 38 5 5 4 4 4 4 4 4 4 38 76
Bob 5 5 5 4 4 4 5 5 5 42 4 4 4 4 4 4 4 4 4 36 78

[Code] ......

View 3 Replies View Related

Copy Column Rows From One Workbook To Another Based On Matching Value

Apr 21, 2014

Copy rows from one Sheet to another based on a separate cell value But specifically, I am trying to copy row values from Columns C through column Z in Worksheet 1 of file POHeader.xlsx to row values Columns N through AK in file POReceiv.xlsx when the (Purchase Order #) values in Column A of each file match.

The reason is behind this is - one file has the unique Purchase Order number as the key without associated parts and the other file has the associated part number as the key with purchase order number attached.

I don't know whether I need to use VBA or if I can just use an index and match function.

View 9 Replies View Related

Remove Matching Items From A Workbook Based On A Master List

Jun 16, 2009

I have Workbook A populated with skus from a report and I want to remove all rows in this workbook that do not have a matching sku in Workbook B. Each workbook contains only one sheet and the skus are in column 1 of both workbooks.

Also, I am looking for a good resource/tutorial for working with workbooks, worksheets and ranges within. I don't anticipate this being the last time I will be confronted with this and would like to learn more.

View 4 Replies View Related

How To Autofill Information From The Next Cell

Dec 20, 2013

I have this information available.


And what I want to do is use this information further down in my excel-sheet. So whenever I plot in "Location 1" I want "Security" and "John Johnson" to magically come automatically.

Like this : test.png

View 2 Replies View Related

Autofill Based On Cell Condition

May 20, 2014

I have a workbook sheet change condition that, when triggered runs the following code (amongst others).

[Code] ......

What I would like is to amend the above (possibly in a loop?) so that it skips the rows in which the value "New" features in range F17:F190.

So basically Autofill as per the code but leave the values in the cells of the range in which New features in range F17:F190

View 4 Replies View Related

Autofill Last Day Of Month Based On Another Cell

Jul 25, 2012

In cell A12 the format is mm-###. Based on the mm (2 digit month) in cell A12, I would like cell B12 to auto fill the last date of the month as mm/dd/yyyy.

View 4 Replies View Related

AutoFill Cell With Designated Label

Aug 1, 2012

I am trying to do is have a VBA automatically fill a blank cell with text of a different color.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B8") = """" Then
Range("B8") = "Name"
End If
End Sub

Is what I've been trying to get to work. If the value in B8 is suddenly deleted, I want that cell to show "First Name", in a lighter grey. I know I can get the color of the text by using the conditional formatting, but how do I get that cell to auto-fill when deleted?

View 3 Replies View Related

Autofill Until Non-blank Cell Macro

Jan 10, 2009

I have searched the forum a few times and haven't seen an exact example of what I am trying to do, so I hope this isn't a duplicate.

I am looking for a macro to copy a cell and autofill it down the column until it gets to a non-blank cell. When it reaches that cell it needs to then copy THAT cell down until it reaches another non-blank cell... this could happen just a couple times, a couple dozen times, or only the very first time.

So for example:

In cell B2 there is text "Data1"
In cell B13 there is text "Data2"
In cell B25 there is text "Data3"

I would like the macro to search column B to find "Data1", and then copy it down the column until it reaches B13, then copy "Data2" down until B25, where it would then copy "Data3" down until the end of the document. The location of the data in column B varies, and the number of cells with data in column B will vary as well.

View 9 Replies View Related

AutoFill Cell W/ Data From Different Sheet After Action

Jun 22, 2009

I have an excel file that contains two sheets. Sheet2 contains rows of different types of products with each of the columns containing details about the product.

On Sheet1, I have setup a data validation list in which a certain cell contains a drop down box that selects a product from the first column of all the rows on t from Sheet2. I used

View 3 Replies View Related

Select A Cell To Autofill Userform Textbox

Jul 2, 2012

I current have code that fills in textboxes in a userform with values in cells:


Private Sub CODE_Change()
Dim ws As Worksheet
Dim fm As Worksheet
Set ws = Worksheets(ActiveSheet.Name)
Set fm = Worksheets("Form")


How to I modify the lines under Checkbox1.Enabled = False so that they only take a section (of varying size) of the text in a cell and put it in textboxes in a userform?

ie. The text in a cell is "Outer: A123 ; Inner: B456." I would like A123 to appear in a textbox called OuterColor and B456 to appear in textbox called InnerColor

View 4 Replies View Related

Copyrights 2005-15, All rights reserved