How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data
Apr 23, 2014
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies
ADVERTISEMENT
Jun 4, 2014
I have an invoicing spreadsheet, what I want is to have a main working page and then tabs after separated by vendor, but save myself a bunch of manual work in the future.
I want to be able to work in the main tab and the other tabs automatically update with the new info.
So I want the macro to search the text in the first column and if it finds "AIM Land Services Ltd." then I want it to populate the appropriate tab, in this case "AIM", with all the respective information after it ie: A-O. But I dont want it to select any other vendors/ info.
This is what I have:
Sub MoveData()
For Each cell In Range("A1:A1") ' Where to look for the date
If cell.Value = "AIM Land Services Ltd." Then ' The date
Range("A5:O350").Select ' What is the range to copy
Selection.Copy ' Copy it
Sheets("Invoice_Log").Select ' Select the Sheet it applies to
Range("A5").Select ' Where to paste the data
[Code] .........
View 1 Replies
View Related
Feb 25, 2013
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
View 4 Replies
View Related
Sep 26, 2013
I would like to separate data into multiple workbooks based on a unique value in the column. For instance if I have Departments in Column F, that has data of accounting, HR, etc. I would like to put all accounting data into a accounting workbook and all HR data in a HR workbook.
View 4 Replies
View Related
Nov 7, 2007
I have a "memo of meeting" excel document used to capture notes during client meetings. In this document there's a cell for "purpose", "agenda", and "notes". The 'consultation type' cell is a drop-down populated by values set in Sheet2. The 'agenda' & 'notes" cells are then populated with information (also stored in Sheet2) based on what drop-down option was chosen in the 'purpose' cell. I'm currently making this happen with basic =IF() statements; however, the problem is that by doing it this way, the =IF() statement must reside in the actual cell which will end up showing the data.
This is undesirable as there may be additional notes, agenda items, etc. that I'll need to add during the meeting and as it's currently setup, I can only have either the pre-populated info OR the ad-hoc notes - not both. It seems a simple fix. Maybe put the =IF() statement in a cell off to the side but have it "assign" the value to 'agenda'/'notes' but that doesn't seem to work.
View 9 Replies
View Related
Jan 14, 2008
I have a spreadsheet of ~5000 rows of data that I would like to separate based on the product code column (11 product codes - 43,301,304,313,332,334,979,984,985,986,992). As it stands, I've been copying the sheets, then removing the data by filter, but doing this every week seems to be a waste of time.
View 9 Replies
View Related
Mar 12, 2008
I have created a workbook for a co-worker. The way it works is data comes from an outside source with customer names in Column A and their Account Reps in Column H. This data is pasted into a template that I created with a very simple macro recording that filters on each Account Rep and is copied and pasted each into a different workbook. Then I had to go through each workbook (because of the info being sensitive) and create a macro that deletes the unwanted data because when the data is pasted it transfers all of the data and you can unfilter to see the full sheet.
I know there is a better way to extract data on each change in column H into a new workbook. Does anyone know how to do that? Another issue is the original data is not very clean so there are blank rows in the list. For Example:
CustomerTechnology SegmentOpportunity Dollars ($)Opportunity StatusAction Item(s)(ST) or (LT)Due dateAcct RepCustomer 1Action 1ST3/30/2008ACCustomer 2Technology 1Action 2LTTBDACCustomer 3Technology 2$10,000FindAction 3LTTBDACCustomer 4Technology 3FindAction 4LTTBDBHCustomer 5Technology 4FindAction 5LT1/31/2008BHCustomer 6$30,000Action 6LTTBDBHCustomer 7Action 7LTTBDMJCustomer 8Technology 5$10,000QualifiedAction 8ST3/1/2008MJCustomer 9Technology 6QualifiedAction 9ST2/28/2008CGCGCustomer 10Technology 7$18,000FindAction 10ST3/1/2008CGCustomer 11Technology 8$10,000QualifiedAction 11ST3/30/2008BHCustomer 12Technology 9FindAction 12LTTBDACCustomer 13FindAction 13LT3/15/2008AC
View 9 Replies
View Related
Dec 20, 2013
So the problem is that I have two workbooks: one has a set of identifying values which are a subset of one of the sets of values in the second and I need to match them up. In addition I need to take the values two columns to the right of the matched values in the second workbook and put them in the first.
Here is what my formula looks like now.
=IF(A5=VLOOKUP(A5,'[WBOOK2]Sheet1'!$A:$A,1,TRUE),INDEX('[WBOOK2]Sheet1'!,MATCH(A5,'[WBOOK2]Sheet1'!$A:$A,0),3),"NF")
Whenever I try to run it an error message comes up and highlights the match function name.
View 12 Replies
View Related
Jul 7, 2009
I have a large workbook with many worksheets which are all grouped into pairs - i.e. Sheet1 & Sheet2 go together, Sheet3 & Sheet4 go together; Sheet5 & Sheet6 go together, etc. etc.
I need a macro to divide this workbook into separate workbooks where each group of worksheets has a separate file of its own and I want to name the new workbooks after the second sheet in each group:
i.e. A new workbook for Sheet1 & Sheet2 called Sheet2.xls; a new workbook for Sheet3 & Sheet4 called Sheet4.xls; a new workbook for Sheet5 & Sheet6 called Sheet6.xls; etc. etc. etc.
View 12 Replies
View Related
May 25, 2014
is it possible to place sheet names beside the data? What I mean is that, I'm currently working on a report which extracts data from the current workbook (many sheets) and paste it into a new workbook. As there would be duplicates due to many different sheets. I would like to have the duplicates removed but have the sheets name which they are found in the columns beside.
View 9 Replies
View Related
Jan 30, 2010
Im working with a huge amount of data in BOOK1.xls and once or twice a week I received an update of the list.
1. I need to compare and find out from the new updated worksheet if there is a new revision, and if there are new documents added to the list. I have tried Vlookup..but it didnt work for me. Ex. Doc1 has revision 1, 2 in my BOOK1 that I am working on. Note that there is a repetition of the DocName, Doc Number except for the revision.
View 3 Replies
View Related
Jul 25, 2014
So I have a workbook (Workbook1) that opens a userform on launch. I have another workbook (Workbook2) that has a button to open Workbook1. Upon opening Workbook1 (and as a result the userform) I'd like to input data from Workbook2 into the fields of the userform. I'm not exactly sure how to reference the userform fields to input data from Workbook2.
I'm trying the following code:
Workbooks.Open (Workbook1)
With Workbooks(Workbook1).Userform1
UserformField1 = "Billy"
UserformField2 = "John"
End With
The macro is opening Workbook1 and the userform but the fields return blank.
View 1 Replies
View Related
May 22, 2008
I have a form which creates a budget based on user input. I want to save that user input in a separate workbook. My goal is to reduce the file size. I have several modules which perform evaluations/ calculations, and then format the output in a worksheet, but the size is pretty large 450KB.
I was thinking that I could set the control source, to different cells on the worksheet, and then just copy the worksheet to a new workbook, and save the data by itself. Then if I wanted to change the data for a given budget, I would copy the sheet back into the workbook that contains the modules, and load the form again. Is this a good solution? Is there a better way? Please let me know if I can provide more information.
View 9 Replies
View Related
Jul 16, 2012
I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.
I.E.
ATL
ATL
ATL
ATL
CEN
CEN
JCK
JCK
etc.
There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV
The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.
So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.
I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.
View 5 Replies
View Related
Jul 30, 2013
I have a userform, UserForm1, which lives in a spreadsheet called 'Data Entry.xls' There is nothing else on the spreadsheet itself, it's just for the use of a userform.
I would like the user to populate textboxs in UserForm1 but have that update cells in a separate spreadsheet 'Training.xls' in the same directory.
I have this code at the moment to find the next empty row and to input data into it, which is working perfectly to enter data to sheet1 in Data Entry.xls:
[CODE]Private Sub CommandButton1_Click()
eRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(eRow, 1) = TextBox1.Text
Cells(eRow, 2) = TextBox2.Text
Cells(eRow, 3) = TextBox6.Text
End Sub[CODE]
How can i modify it so it would do the same thing, i.e find the next empty row and then populate with what the user types in the textbox but in Sheet1 of 'Training.xls'
Would 'Training.xls' need to be open?
View 2 Replies
View Related
Feb 25, 2014
Is it possible to have a single userform put data into two separate sheets within the same workbook.
I have a userform set up for entering client data into a quote register,with no problems ( sheet 1)
A1, A2, A3, A4, A5, A6, A7, A8, A9
What I would like is for the data to also go to sheet 2, where I have a form set up to print off for our technicians to take on site.
The problem will be that I would like the data A1, A2, A3, A4, A5, A6, A7, A8, A9 not to store on this sheet after it is printed, so the next set on entries , can be printed etc.etc
View 2 Replies
View Related
May 24, 2014
I have 30 xlsx files in a folder and I want the first sheet of all that files to be merged to a new workbook. The thing is I don't want the macro to copy paste the value in to the same sheet of the new master sheet like Ron's excel merge tool does. I want a macro to create new 30 sheets on the master file and copy the data from source files. And I want the newly added sheets to be renamed as the source file name. This works well except the sheet renaming thing.
Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
[Code].....
View 1 Replies
View Related
May 12, 2009
I have a worksheet with 10000 records. For example, entire data in ColA-ColZ. In that, ColA-ColF - Personal Information; ColG-J Group1; ColK-N Group2; ColO-R Group3; ColS-U Group4; ColW-Z Group5. I this case, the data to be copied into another sheet as follows:
1) the personal data should be copied repeatedly.
2) Each Group data should be copied next to personal data.
3) The group's name is mentioned at the top of the datasheet.
I have attached a sample workbook for your kind reference.
View 2 Replies
View Related
Oct 4, 2013
How to pull data based on column B to individual tabs.
I have the following data:
A B C D
Jane Doe | L1 | 20% | High
John Doe | L2 | 15% | Medium
Mike Smith | L1 | 60% | Low
Marie Smith | L4 | 10% | Low
I want to have the first tab/spreadsheet only pull records that have L1 values for column B
In the next tab/spreadsheet I want to pull records that have L2 values in column B etc.
What formula can I write that will pull all L1 records (A.B.C.D) in to Tab 1, then all L2 records (A,B,C,D) into the next tab?
View 10 Replies
View Related
May 22, 2014
I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.
creating a macro or implicating one into the workbook to run for future reporting.
View 13 Replies
View Related
Apr 11, 2012
I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.
My VBA skills is limited to this particular task.
View 2 Replies
View Related
Apr 22, 2013
I have a huge list of survey responses which asks people to identify their location and then rate how important 6 facilities in their area is (rating 1 - 4, 4 being most important). So the table has cells are similar to this, except that there are about 5000 responses with about 2500 different locations identified
Location
Location
Location
[Code].....
I have been able to analyse the average results for the whole dataset by using the following formulas:
=COUNTIF(A2:C4,"camden") Count number of occurrences a location is listed
=ROUND(AVERAGE(D2:D4),2) Average response for medical centres
=COUNTIF(D2:D4,1) Counts number of times '1' comes up, would repeat '2', '3', and '4' to create a pie graph
But what I would really like to do it be able to count each response for each question by location (for example "Camden"). I've tried both of these but they don't seem to be working.
=IF(A2:C4,"camden"),COUNTIF(D2:D4),1
=COUNTIF(D2:D4),1,IF(A2:C4,"camden")
View 6 Replies
View Related
Dec 3, 2013
I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies
View Related
Dec 18, 2013
I have a data that has several columns. But I need to separate the spread sheet based on one particular column values.
View 14 Replies
View Related
Oct 28, 2009
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs.
I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
View 2 Replies
View Related
Aug 2, 2012
C5=ACDefg-0087HzY
In D5, I want 1st 4 letters (EXACT)
In E5, last 10 letters (EXACT)
How to accomplish?
Ans
D5=ACDe
E5=gf-0087Hzy
View 2 Replies
View Related
Feb 10, 2014
I got a column of data in format as below:
Column 1:
ABC - AC123
ASD - DS335
BBC - KK311
SWT - TR735
TIM - DN849
Between two texts to be separate, there is a " - ", we can separate them with that symbol right?..The cells should be look like after separate shown belows:
Column 1:
ABC
ASD
BBC
SWT
TIM
Column 2:
AC123
DS335
KK311
TR735
DN849
What is the macro code to do this
View 3 Replies
View Related
Jan 12, 2007
how to separate data such as this, into different columns?
85878; null;OMX;OM;2004-09-13 08:58:29.0;691.91;OMX Stock Index;693.01;688.67;691.91;15055;0.0;14.64;0.0;0.0;2004-09-10 00:00:00.0;0.0
Today this data is inserted into one cell, in one column, but in 50 000 rows (in 9 different sheets!). I would like to separe it into separe columns so I could run calculations on the data. Separating it manually would take me at least 1 year,
View 4 Replies
View Related
Oct 15, 2012
formula to separate information in a single cell into multiple columns. I have hundreds of rows of data, so a formula to quickly separate the information into individual columns would be good. Below are a few lines of data.
ALMADEN STATION* 900 227 168 182 176
OAKRIDGE STATION* 900 276 332 362 349
OHLONE‐CHYNOWETH STATION* 900 469 480 522 504
ALUM ROCK STATION 901 947 925 1007 972
BAYPOINTE STATION 901 181 196 213 205
BONAVENTURA STATION 901 137 140 152 147
CAPITOL STATION 901 650 680 741 715
CONVENTION CENTER STATION 901 788 814 886 856
CURTNER STATION 901 569 571 621 600
ORCHARD STATION 901 87 94 91
View 6 Replies
View Related
Oct 23, 2009
I use Excel 2003. I need help building a macro, please, that will copy data to a specific page in another workbook based on two criteria. Here's the deal:
The data in Workbook A, Sheet 1, Cell A1 may contain the word ALPHA, BAKER, or CHARLIE. Cell A2 may contain the number 1, 2, or 3. Cell B1 contains the data I want to collect from various Workbook As and keep in a list to analyze.
Over in Workbook B, Sheets 1 through 3 are named ALPHA, BAKER, and CHARLIE. Rows A, B, and C are titled 1, 2, and 3.
How can I copy the data from Workbook A, identified as ALPHA 3, to it's place in Workbook B, Sheet ALPHA, Row C?
Furthermore, Workbook A is a one time form will be used many times. Thus, when I copy B1 to Workbook B, Sheet ALPHA, Row C, I need to paste the data in the first empty cell in the row.
View 9 Replies
View Related
