Split Worksheet By Value Macro?
Oct 5, 2011
I need to split a worksheet into new workshets in the same workbook using customer names in column D which includes subtotals. However, the macro i am using splits the customer name and customer subtotal into different sheets as the customer name now contains "total" so the macro doesn't recognize this is the same customer.
E.G
Customer name
Kwik Fit
Kwit Fit Subtotal
Mail Marketing
Mail Marketing Subtotal
I need the macro to split the above customer names so that the customer name including the subtotal gets split into a new worksheet AND the new worksheet is named after the customer name.
I am aware their are add ins for such tasks but i am using this at work and cannot install any files.
Code:
Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
[Code]......
View 1 Replies
ADVERTISEMENT
Aug 12, 2009
This code splits a worksheet into multiple sheets (based on sales person in column A). But when it does it, it deletes the sheets and then re-creates them.
However this messes with my formulas I have linked to the split sheets and turns them into #REF! errors.
View 5 Replies
View Related
Mar 1, 2013
I would like to split a worksheet to multiple sheets based on a column header. On browsing through the forums I found the VBA code below.
My problem with is that the code automatically uses only the first column for spiliting into different sheets. But I would like to modify this so that it searches the first row for a matching header specified by me (Eg "Name" , which may be column 10)
Code:
Private Sub PagesByDescription()
Dim rRange As Range, rCell As Range[code]....
View 4 Replies
View Related
Nov 8, 2008
I have a worksheet that has data in columns A to N. The Salesman’s number is in column A and I need to split this first worksheet in to separate worksheets in the same workbook for each salesman.
View 9 Replies
View Related
Nov 12, 2009
I need to open a text based file (not a txt file), read the data in as a String. Split the string and paste into a worksheet. The string really has 2 delimitors a & and a =.
Ideally, I would like to keep only what was between the = and the &. But would settle for splitting the string by &, then putting token to left of = in one column and token to right of = in the next column. The first sounded cleaner, but I'm getting desparate.
This didn't sound hard when I started, but I've never really done VBA before and it has been posing a much greater problem than I thought. I've tried several things, but this is the latest rendition. Am I thinking too much in terms of C++ and Java? ...
View 9 Replies
View Related
May 26, 2009
how to split one large worksheet into several worksheets using VBA. The only criteria I have to go on in the large worksheet is the existence of blank rows between each chunk of data I want on it's own worksheet.
Please see the attached zip file. There are 39 chunks of similar (but varying in number of rows) data that are separated by 3 blank rows, so I want my workbook be split into 39 new worksheets. My goal is to get the data into list/table format so I can work with it more easily and export into Access etc.
Also, just out of curiosity, is it possible to convert a HTM file into CSV? The attached data starts life as a HTM file that I just convert to XLS
View 6 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 9, 2012
I'd like to split up the rows in a worksheet based on the values in one of the columns. Also, I'd like the sheets to be named after the values in the column. I have attached example excel sheets to explain this better. I think the vlookup and Sheets.Add and ActiveSheet.Name formulas can be used but I'm not quite sure how to put them together. The actual data has about 20 columns and about 500 rows.
View 6 Replies
View Related
Jul 3, 2014
it doesn't like the fact I have split column headers and I was wondering if there was a way around it or another solution to save me time manually counting or filtering. I can't attach the actual spreadsheet for some reason but attach a screenshot to illustrate what I mean.
I need to count the number of 1's, 2's, 3's etc in each 'On Exit' column for each area. I then need to work out what percentage of the cohort they each are.
View 9 Replies
View Related
May 26, 2009
I have requirement to extact data into a spreadsheet. This data is extracted from CSV file which is huge normally over 7MB. I have found a macro on the internet which I have included with this post.
Sub split()
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")
'Check for no entry
If FileName = "" Then End
'Get Next Available File Handle Number
FileNum = FreeFile()
'Open Text File For Input
Open FileName For Input As #FileNum .................
I have also included reult data that I get after running the macro. I have cut down on data due to attachement restrictions. But usually there are multiple spreadsheets named "Sheet1, Sheet2, Sheet3 and so on". I have numerous requirements. First of all I want to be able to spreate data into columns. For now how I do that is by going to data -> text to columns and then selcting delmited and then selecting comma as my delimiter. Second thing I want to do is is only extract range of data from this output into a new worksheet. I am only interest in the name like for example ALBANY-Serial0/0/0 and data that is in the range of 8:00 AM to 6:00PM. So the new sheet should only have name and for that name data in the range of 8:00AM to 6:00PM for all the sheets "sheet1, sheet2 etc" until all data has been extracted.
View 7 Replies
View Related
Jan 15, 2014
I have a set of numbers (representing time), and I need a macro that will generate a split of these numbers that both have an equal sum (or as close to equal as possible). Also, everytime the macro is run, a different split should be created.
View 8 Replies
View Related
Nov 23, 2007
I have file that may contain about 100 or more rows depending on the request. In column A, i have several names that is seperated by comma in ever row so basically what i want to do is to remove each name and insert a new column and paste the removed name in the new column and keep doing that until only one name is left for each row. I would like the code to check every row until the last row of the file and perform the splitting method that i have mentioned above. Pls. note that some row may contain only one name in this case we don't need to copy and insert new column but if the row contains more that one name then i would like to split it as shown below. I have attached file for details. I really appreciate for your help.
Here is an example of the current issue
Mike, David, John
Eric, Dave
Joe, Johnson, Edward, Ellen
Thompson, Adrienne.........
View 14 Replies
View Related
Dec 1, 2009
I have a excel sheet that contains about 8000 entries... I need to create a macro to help me. my file is like this:
123
RT4
eliott.billy
mark.garth
elena.david
I would like to split first name and last name. All the ligne that contains "." split it in 2. Example: eliott.billy will become eliott (column A) billy (column B)
View 5 Replies
View Related
Dec 2, 2013
I want to split numbers in a cell. At the end of my report there is a text like this ;
Critical Path : 35-36-37-38-39-40-32-9-10-11-12-13-14-15 ;
Total Pressure Loss : 217.1
Pa text row changes in every report but it is always in a1 column is there any way to search critical path and split numbers in text.
My result should be : -35-36-37-38-39-40-32-9-10-11-12-13-14-15-
View 3 Replies
View Related
Mar 6, 2008
I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.
Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".
I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.
So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################
Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"
There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.
I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.
So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################
View 6 Replies
View Related
Apr 12, 2014
I usually copy ready macro from google. And I currently working on a check printing excel for my company to print check. I have googled and extract this macro from an author and place into my excel. But i run into a problem which is, the returned text will exceed the boundaries,
is there a way to spilt the text, lets say from after "dollars " to another appointed cell?
[Code].....
View 5 Replies
View Related
Apr 18, 2014
This is my input:
Col A
asde,dsfts-qwsa/001,asdasd-qwsa,asdas
etert,ttyutt-tww/013,dgdd-tww,dsfsaa
.
.
.
.
.
Col B
qwsa
tww
In Col C.. I want the output to be as
asde,asdas
etert,dsfsaa
Requirement as a text:
I need a macro that removes the value in column A based on the value in Col B.
The data that the macro removes is always between the "," (comma).
View 6 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
Jul 15, 2009
I have a huge data which needs to be divided and distributed to team members. What i want to achieve is to split, thru macro/VB, the entries in my main file into several files, say by 50s. I attached files for reference. Whereas, the Dummy.xls is the main file and Book1 & Book2 are the desired output.
View 3 Replies
View Related
Oct 30, 2010
I have a macro below which splits the data in my workbook (Attached) into seperate workbooks on the basis of each change in data, and saves the new workbooks with the value available in column A. Everything else works perfectly with this code I just want the code to take the workbook name from Column B, not Column A as it is currently taking.
Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
[Code] ......
View 9 Replies
View Related
May 16, 2011
I have a file, with this dummy data
Fruit Country QTy
Apple India 100
Apple USA 200
Orange India 150
Grapes Aus 100
Grapes India 100
I need to macro, wherby it will create 3 files namely apple, orange,grapes and store the relevant rows.
Hence I need a macro to split file into different files based on different values in a column and accordingly name it .
View 9 Replies
View Related
May 3, 2012
I have Column D containing multiple values that I need to split. The structure is:
Text Text (XYZ) 123 AB C
Text 456 C
Text Text (RST) 7 A CD EF
Text 22
I'd like is to split the cell using a macro rather than formulae so that the resulting cells can be filtered.
The output would be:
Text Text (XYZ)|123|AB|C
Text|456|C
Text Text (RST)|7|A|CD|EF
Text|22|
So the first split would need to be when the number is reached, the second split after the number and then subsequent splits each time a space is encountered (although there may not be any values after the number!).
View 6 Replies
View Related
Aug 5, 2013
My code below returns a type mismatch? It is looking at the values in column B which are formatted as text and the output is in column J. An example of a value is 2.1.15 I want to extract 1 (i.e. the central character between two ".").
VB:
Sub ConvertLineNo()
Dim r As Long, TempStr As String
For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -2
TempStr = Cells(r, "B")
TempStr = Split(TempStr, ".")
Cells(r, "J").Value = TempStr
Next
End Sub
View 3 Replies
View Related
May 2, 2014
I just want a macro to split the hours to a date and time in column A. I just preferred macro rather than a formula.
Ex: [Code] .......
Column B1 should be: [Code] ........
I've also attached the file : hours split.xlsm
View 5 Replies
View Related
Nov 1, 2007
I need a macro that can search text cells in column L for a space delimiter, then if the space is present, to split that cell into a row inserted below it, then copy the information in the rest of the row down. I'm not really up on my VBA.
View 13 Replies
View Related
May 20, 2008
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
10/05/0809:14:00 AM
10/05/0810:18:00 AM
10/05/0810:42:00 AM
View 10 Replies
View Related
Apr 16, 2014
Excel VBA 2010.
I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g
I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth
I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.
View 6 Replies
View Related
Jun 19, 2014
I am trying to divide a table of data (7 Columns, 500-1000'ish rows) into individual worksheets based on any unique values in column. There may be anywhere from 10-30 instances of the same value in Column C, and I need to bring all rows of each unique instance into a newly created worksheet to be labelled the same as the value in Column C.
The RAW data will be updated monthly by keeping a template file copying and pasting data, therefore I am hoping to have a "SORT" button with this Macro assigned to it. I have quickly thrown together a sample workbook with how the RAW DATA will be pasted into the file, with additional sheets showing how I would like the data split and organised.
View 2 Replies
View Related
Mar 30, 2013
Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .
Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).
Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).
E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
View 7 Replies
View Related
Jul 30, 2013
I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.
The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".
The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?
As an extra point, I'd like to use column B to check for blank rows (not column A).
View 6 Replies
View Related