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.
I have just looked through most of the postings I could find on the board regarding VBA to identify duplicates. Virtually all of these use the entire column when checking for duplicates.
I am after code which will identify duplicates in column A within a contiguous range of data. For example there is a heading in column B and below this photo details will be entered and in the next column the photo number is entered. Finally a number (for sorting) is entered in column A. The photos will be numbered 1 to whatever. So I need code which will highlight duplicates in that contiguous range of data. Then there is one or two blank rows before another batch of photo details are entered. This means the same sorting numbers are used and are separated by a blank row or rows.
I'd like to be able to have a macro that will create a pivot table based on a range, open up each of the total rows within the pivot table (which will create a new sheet), name the worksheet the title of the total row corresponding to it then copy each worksheet created by the pivot and paste into another sheet in a required format.
i have a worksheet that has information filled in columns A-N and rows 3-7262 (not including title and labels)
i need a macros that will separate this data into multiple worksheets based on the word in Column A; these words could be HDA, CCR/NHT, GRU/ATU (essentially dividing this data up by area name, there will be 14 worksheets)
i would also like to be able to update this master worksheet and have it be reflected in the multiple worksheets that are divided by their areas.
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
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.
I'm trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with. Secondly, someone appearing in the first or last groups cannot appear in that group again.
I've been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.
I attached an example dataset : demo dataset.xlsxā€ˇ
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.
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.
I am trying to combine data from two separate worksheets onto one so it can be sorted for printing. Using the macro recorder, and the search function on the forum, I managed to ham-fist my way through most of it - except for one issue.
How can I have Excel/VBA go to the first open cell in column A before it pastes the 2nd batch of information? I get an "object required" error with the MyRange variable.
Code: Sub UpdateSortedTab() Dim MyRange As Variant
' Removes Old Information Sheets("Sorted").Select Columns("A:E").Select Selection.Delete Shift:=xlToLeft ' Copies Bench Stock Information
I need a VBA function to extract number sequences from a string and separate them with hyphens In the example below cell A1 has the value 'xx2 yyy34 zz515' The code must produce the value '2-34-515' from the above example I have the following function that extracts the numbers but need a way to separate the groups with a hyphen
Code: Function parseNum(strSearch As String) As String Dim i As Integer, tempVal As String For i = 1 To Len(strSearch)
I have on inventory sheet that has all the data in each cell 2612 to be exact! That changes month to month with deletions.
The format is: Sheet 2 A/B/ 334702/UEMR88QX 334703/UEMR85QX 334704/UEMR81QX 334707/UEMR8JQX
The master worksheet has about 5000 items and the (A and B) data are both on it with other data ranging from (A-Y). My question is how do I have a cell look up data and return that it exists or doesn’t exist on the inventory sheet?
My other problem I need to take that months inventory list and have it look at the master list and return the system number from the master list (B) next to the matching inventory number (C). Allowing me to cross match inventory to master each month and save inventory worksheet each month.
I know I can copy the column and past is as a value so I won’t lose the numbers as the master changes but getting the data onto that months inventory has been a pain.
The first one, I do i remove #DIV/0! from cell O7 in the worksheet "My overview"?
The second one, I need to get averages of the QA score for the 3 months from January to March in the worksheet "My Overview" Cell E14 for the corresponding person on each sheet. I understand that there need to be values in place to get an average but is there a way to overcome this?
I have a custom data entry form which is working fine. The form completes customer data for reviewing at a later date. I have now been asked to change it so it will seperate the data in to customer's who require some documents sent out and some that dont.
Is it possible to have a combobox on the form and if the options on the combobox are Yes and No (original), when either option is selected the data will complete on to a seperate sheet for cases with documents required or documents not required?
how to filter and use certain functions and make pretty looking charts.
my Problem is; I have rows of different clients in one worksheet with various columns of extra data called ALLRECORDS. I have created new worksheets(tabs) in the same workbook for individual clients. I want to to automaitcally pull all the mixed client data from ALLRECORDS into their individual client worksheets along with all the columns of extra data.
so whenever I update ALLRECORDS it automatically updates individual clients worksheets.
I need to be able to pull information from a "master database" into seperate worksheets. Here is my problem. When I use a vlookup and my identifer is say "office property" then the vlookup will only pull the first "office property" and not retrieve any of the remaining "office property" rows.
I want to be able to loop through all the sheets and copy similar named worksheets to seperate workbooks. So all the VARIOUS go to one workbook, all the PART 1000 goto another, and all the PART 1001 goto another. I can only determine the worksheet names by looping through all sheets - as I am not sure what they will be called because it is done programmatically. ALL sheets will contain a "(n)" where n is the sheet number, so I believe can extract similar names from the string preceding the left bracket.
At the moment I just lump ALL the worksheets irrespective of name to a single workbook creating an array and using the SPLIT function. This is very efficient and what it does, and I still want to use the SPLIT function and arrays.
this is the code I currently use, any help much appreciated!!! Please tell me how to modify this existing code to clump together similar names and copy - I guess I will need to loop multiple times to achieve this.
For Each mySheet In ActiveWorkbook.Sheets
Range("Arc").Value = "'" & Range("Arc") & "," & mySheet.Name If Left(Range("Arc").Value, 1) = "," Then Range("Arc").Value = "'" & Right(Range("Arc").Value, Len(Range("Arc").Value) - 1) End If Next
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.
I am running a Macro to fix my hyperlinks in excel, and I have several worksheets that I want to preform the same function for but with different new and old strings. The code I'm running is the hyperlink fix code:
Sub Fix192Hyperlinks() Dim OldStr As String, NewStr As String Dim hyp As Hyperlink
I have two worksheets and each one has it's own number. Sheet1 has "100" and Sheet2 has "101" and they're located in "A4" on each. Each sheet will have a control button that will be pressed when a sheet is complete. The control button will clear a range of cells "B5:C20" and increase the sheet number in "A4". So if you press the control button on Sheet1 the range will be cleared and the number will change to "102" since Sheet2 is already using "101".