Copy Data From One Book To Another From Common Data In One Cell
Feb 15, 2010
Got an issue that I can only seem to do with things like the program Hotkey and making physical macros. I'm sure it is easy but I'm lost.
I have data in one book that looks like this (Call it Book1)
Date: Service
ProviderReason for callCRM
SR #DM
SR #WO #Device #Serial #Issue15/02/2010DATE: 03/02/10
TIME: 11:29
BUS: 1234AO
ISSUE DETAILS: Issue with Console
and data in another book that looks like this (Call it Book2)
DeviceSerial NumberNameTerminal PositionTerminal Group Location DescriptionTerminal IDDescription Service Provider 104012343030123456ConsoleDriver Console1234AO90401234Service Provider Name found Here
I need to find the BUS: 1234AO from the cell in Book1 then look that Bus up in Book2 and then from Book2 copy the Device, Serial Number, and Service Provider into the cells into Book1 and repeat this infintely often. The problem I have is the the bus can have 1 Console and 1-5 Readers and I need to find that data also. So for example I need to find BUS 1234AO and get the Console details and past that into one issue and the next issue will have the same bus number 1234AO but I need the details for Reader2 (that detail is in the Name field as shown above.) The issue details will always have the Console text and Reader# text in it.
The problem I have is that the Reason for call cell has ALT-ENTER characters in it and the details are not always in the same spot but the details are almost always in the format ####AO (four numbers and the letters AO)
View 19 Replies
ADVERTISEMENT
Jun 22, 2007
I have the below code, which now looks to see if a file is open or not, if it is, then copy and past 'Data' and if not open the book and copy 'Data'.
I think the code is sort of right, but im missing something, as i keep getting runtime error when i try and copy. Here is the
Sub PrintSaveKPIUpdate()
Dim sFilName As String
sFilName = "C: estCashSales_KPI.xls"
Set Main = ThisWorkbook
If IsOpen(sFilName) Then
' Book is Open.
Worksheets("Setup Data"). Range("Data").Activate
View 9 Replies
View Related
Jul 16, 2014
so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.
2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.
View 6 Replies
View Related
Feb 5, 2014
I have a code in which we can split the data using column values into new and same sheet also. I want to modify this accordingly to my need but i am new in VBA so can't modify this. i want 2 changes in this macro:
1. I want to set a value (Like:"Resolved"," In Process" ) in the macro to extract the data related to the value only. Means, when i run this macro it will extract the data only for "Resolved" status. I have to create two macros for each defined status.
2. After splitting, it will ask me to save the new workbook location.
3. Save it.
The codes i m having are :
First code to make directories:
Code:
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
[code].....
View 4 Replies
View Related
Jan 8, 2008
I am trying to write a VBA code to conditionally copy and paste a range of cells on sheet A into sheet B if the specified cell value is not found on sheet B. Here's an example
On sheet A, there are phone numbers in column M that I need to find in sheet B. The reference column in sheet B is already a named range ("Description") which I'm using to conditionally format several cells in sheet A to turn green when the phone number is not found on B. This is the formula I'm currently using to do this "=AND( COUNTIF(DESCRIPTION, $M2)=0, $R2>0)" ...Obviously I'm using the AND function to tell the format to only work if the cell value of R is greater than 0.
What I would like to do to something very similar to what I already have in place, only now I would like to have a macro search through column M and instead of highlighting the proper cells...I would like to copy the associated row data from columns B:N in sheet A and paste special>values into A:M on sheet 2. Also, I need to keep the "R is greater than 0" condition in place.
View 3 Replies
View Related
Oct 24, 2006
I have a range of data that has been collected by date starting in H4 across several columns. This means that my entries start at different points in each row i.e. -
Row 4 first entry Column AJ
Row 5 first entry Column K
Row 6 first entry Column AB
Using vba is it possible to find the first entry in Row 4 and copy it and all the figures following it in that row to K4 on Sheet2, and then row 5 to K5 on Sheet2 and so on?
View 5 Replies
View Related
Mar 6, 2012
I have managed to use ADO to copy data from a closed book to my active sheet. Problem : it copies only the text, and not the dates or figures...
Code:
Sub TestReadDataFromWorkbook()
' fills data from a closed workbook in at the active cell.
GetDataFromClosedWorkbook "H:P&LYE TempDiv P&LP&L Report 020312.xls", "A1:Z1000", Range("A1"), False
End Sub
Code:
Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _
TargetRange As Range, IncludeFieldNames As Boolean)
'GetDataFromClosedWorkbook "C:FolderNameWorkbookName.xls", "A1:B21", ActiveCell, False
'GetDataFromClosedWorkbook "C:FolderNameWorkbookName.xls", "MyDataRange", Range("B3"), True
[Code]...
View 4 Replies
View Related
Jul 12, 2006
I have a workbook with several sheets containing prices of products. One column has the product number and a second column has the price. We just received new prices. Can a macro read the product number from the new sheet, find the same product number on the old workbook and transfer the new price. and than change the price so I know it was updated.
View 2 Replies
View Related
Apr 30, 2013
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
View 8 Replies
View Related
Jan 21, 2010
this is in reference to a question I asked some time ago in which I was given the following: =IF( COUNTIF(SHEET1!$A$1:$A$1200,SHEET2!A1),SHEET2!A1,"")
To summize as this is not quite working, I have two worksheets and in Column A are id#s. Worksheet 1 has100's more rows of data than Worksheet 2. I need to find the unique ids from column A in Worksheet 1 that match Unique IDs in worksheet 2.
I need to copy the row of cells from worksheet 1 where the unique id equals that u.id in worksheet 2. That row needs to be copied to Sheet 2 that has the same unique id, and after the pre-existing cells which are already present.
So for instance: if Sheet1 A100 = Sheet2 A24, then copy row A100from Sheet 1 and paste it to Row A24 of SHeet 2 start with the first empty cell- just say Sheet2 G24 as an example.
Using the formula I was given before, it finds matches, but the pasted rows are off. If the match occurs with Sheet 1 A450 and Sheet 2 A36, it will copy the value from ROW/SHeet 1 A36 instead of ROW/Sheet 1 A450
View 9 Replies
View Related
Aug 19, 2009
I have a workbook with a single worksheet that has about 2000 rows, columns A, B, C, D, E & F
Cells in colums A, B, C, E & F all have very different information in them, nothing is similar in any of those columns that I can base a criteria on. Cells in column D however will have 1 of about 18 possibilities in them
What I want to do is have another workbook with 18 worksheets, each worksheet named 1 of the 18 possibilities, and somehow magically pull the data from the first workbook and insert it into the correct worksheet in the second workbook, leaving the data in the first workbook intact.
I update the first workbook several times a day, adding and deleting from it so would need to update as it goes, or be able to run the update as and when needed.
My skills are limited to simple formulas inserted into cells and dragging them down!
View 11 Replies
View Related
Apr 12, 2009
I have two spreadsheets that have one set of data in each column in common.... simply put it is a number.
From the one spreadsheet, I want to pull the column that is to the right of the number in column and merge it with the other data.
I am trying to figure out a formula that will accomplish this... I have attached a file to show the two spreadsheets and my end result I would like to have.
View 13 Replies
View Related
Aug 3, 2014
I have attached the excel sheet here for reference.
I would like to sum up the values across rows, so long as they have the terms "FQ1", "FQ2", "FQ3" or "FQ4" in the first row.
In other words, for the row "Total Revenue", I would like to sum up the quarterly values, using VBA, since the number of quarters increase with time.
How should I code up a VBA for that?
View 4 Replies
View Related
Nov 7, 2008
I'm using Excel 2003
I have done this once already, so I know its possible but I cannot remember what it is called and how to do it.
I have a "form", I open it up, fill out a few different things, and then save and print it. When I save it I want it to take a few different cells information and report it into a seperate workbook, without having to open the report workbook. I use this seperate workbook to reference when I fill out the form and who it was sent to.
I remember there was a wizard that I used to get it to do all this, but I can't for the life of me remeber what it was.
View 10 Replies
View Related
Apr 22, 2014
I need to transfer data from one sheet to another sheet in another workbook. The output row corresponds to a value in input column a, and output column corresponds to a date in input sheet column B.
I've previously dim-ed the input/out workbooks/sheets as wbin, wbout, sheetin, sheetout respectively. The error I get is runtime error '9': subscript out of range in the copy destination line.
Code:
Windows(wbin).Activate
Sheets(sheetin).Select
iMaxRow = 5000
Dim subj1 As String
Dim subj2 As String
For iRow = 1 To iMaxRow
[Code] .........
For now, i've disabled the on error resume next. Also, the input column a has 4 numbers followed by string, while the corresponding output row header only has the 4 numbers, hence I tried matching with the wildcard.
View 9 Replies
View Related
Jul 7, 2011
Table 1
Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June
[Code]....
I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:
AMWG16=01_0211-20
AMWG16=01_0212-16
and these entries will appear multiple times as well
AMWG16=01_0212-16
appears twice and the data is different and will need to be added up
How can I:
1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"
2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show
AMWG16=01_0212-16
in the first column, and then all the statistics from the two tables in the columns to the right.
I'm guesssing...
Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:
AMWG16=01_0211-20
Shows up three times, you'll need to sum up all the values and this needs to be done on both tables
THEN
You can combine the data
I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together
View 4 Replies
View Related
Dec 30, 2008
I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.
*The number of employees for city would be variable.
Original data (The columns used go from A to E)
1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...
View 9 Replies
View Related
Feb 24, 2010
where do i put the indirect.ext to below formula so that that Test.exe file be checked offline .. this below works only if the test.exe is open.
=IF(A2="";"";COUNTIF('C:Folder[Test 24.02.10.xls]Table1'!A$1:A$65536;A2))
Since the Test file is generated every day..
how can i make the Test file shown every day with different date.. this 24.02.10 is not permanent.. everyday changes.. meaning tomorrow it checks Test 25.02.10.exe, Friday it checks 26.02.10 etc.
I could make a cell for date input .. and from that cell it could be added up to enter into above formula automatically..
View 9 Replies
View Related
Feb 20, 2007
I've had a scan through the archives trying to find a post or item on importing Outlook data to Excel, and have so far only found articles on importing data from users' 'Contacts' lists. I need functionality so that when the user selects an office (e.g. 'London') from a list, further details for those offices - which are stored as individual members of the global Outlook addressbook - for example postcode, can be operated upon.
Because this data is relatively dynamic (e.g. potential for relocation of office premises), I need any procedures to refer to a single unified data source, therefore I'd like any VBA procedures to use data directly from the outlook address book, instead of importing a static copy of the data and then performing operations on this.
View 3 Replies
View Related
Aug 20, 2009
I would like to know if there is a way to copy a spreadsheet and paste it into a new tab on another spreadsheet. We currently download 2 lots of spreadsheets from SAP and would like to create another spreadsheet to act as a 'central' preadsheet. What I would like to happen is that the data from the 2 downloaded spreadsheets will be automatically pasted into 2 new tabs on the central spreadsheet, then run some formatting macros (which I can do fine).
View 3 Replies
View Related
May 7, 2013
I have searched and there are some examples that are similar to what I'm looking for but I cannot seem to use those answers to make my problem work. The example from the forums that seems to match my problem the closest is [URL]....
Summary: I have an Excel file called "COST TEMPLATE.XLSX" that has product information (serial number, quantities, pricing), calculations and reports; then on a monthly basis I receive a file (VENDOR_EXPORT.CSV) with updated product quantities to be merged into the cost spreadsheet.
I would like to merge specific data fields from the VENDOR_EXPORT.CSV file into my cost template based on the serial number. I am attaching three dummy files that hopefully describes the issue:
1. COST TEMPLATE.XLSX
2. VENDOR_EXPORT.XLSX - which highlights the cells to be import and to which corresponding column
3. VENDOR_EXPORT.CSV - raw CSV file
My preference is to do this with functions, not macros. How to add the export into the existing workbook as a temporary worksheet, or just pull from the other workbook as CSV file.
View 3 Replies
View Related
May 9, 2014
Looking for some code to do a simple compare column A to Column B (row 1 contains headings) and highlight any differences.
View 3 Replies
View Related
Aug 18, 2009
I have a list of parts in Column A, often times with duplicates in the column.
I have a list of parts in Column B (from another source), again, with duplicates.
What I need is - leaving columns A and B alone;
Column C to be unique Column A parts
Column D to be unique Column B parts
Column E to be parts in common between Columns C and D.
View 9 Replies
View Related
Oct 6, 2013
I have two columns of data and what I would like to do is have excel look over one column ( I5-I379) and for each cell that has say 30 in it I want it to return the value from a corresponding column (G5-G379), to form a separate column.......is this possible?
View 9 Replies
View Related
Oct 22, 2009
find the attached file. Book 2 there is 4 columns ie,reg no,fleet no,product & model and on book 1 there is fleet no,reg no and model and what i need is to capture fleet no & model from Book 1.
View 2 Replies
View Related
Aug 17, 2009
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?
Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.
View 4 Replies
View Related
Sep 30, 2009
I have built a data entry form (Input tab) and have code populating a table (Table tab). Currently both are in the same book, but I would like to house them in separately.
What is the proper VBA syntax for this? Will both books need to be open in order to populate the table?
The 'real' data is hosted on sharepoint and is getting darn big. The result is problems opening the doc and looong save times. My thought was to have the secretaries doing data entry open a single sheet (Input Form) which will then populate the data in another sheet. I'm dreaming, aren't I...
I know Access would solve everything, but for cost reasons I am stuck with Excel.
View 10 Replies
View Related
Apr 8, 2008
I'm transferring data from one workbook to another. The first workbook ('Request Form') will always have a different file name depending on which user is saving it. However, the worksheet within that book will always have the same name ('Tab A'). Workbook and worksheet 2 ('PTS' and '2008' respectively) will remain the same.
Basically I want the macro to open 'Request Form' (whatever the name, wherever it's saved) and stay open as the rest of the macro runs. I need that file name to be a sort of wildcard, since the file path and file name will never be the same. How can I do this?
Dim TabA As Worksheets
Application.GetOpenFilename
Application.ScreenUpdating = False
Set wbOpen = Workbooks.Open("Request Form.xls") --THIS NEEDS TO BE A WILDCARD
'Declares the ClientName
Sheets("TabA").Select
View 9 Replies
View Related
Jul 24, 2014
i want to compare two excel sheets and extract common data in either sheet. For eg: If sheet 1 and sheet 2 contains some common data, i need to compare or lookup or whatever i do but i need to pull out that common data in sheet 2.
View 3 Replies
View Related
Feb 18, 2007
I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
View 4 Replies
View Related
