every month I import (from SAP) Local Authority staffing data for 200 schools into an Excel template. After a bit of tweaking I end up with a single spreadsheet showing formatted and adjusted data for all schools, with relevant headings separated by page breaks.
This is fine if for distributing a hard copy to schools, but I want to email it. I've seen macros that would allow data to be split onto separate tabs, but is there a way to break it down into 200 separate spreadsheets? I confess to not knowing any VBA, so be gentle with me
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.
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 giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data * Copy Data * Create a New Spreadsheet named (Person's Name) mm-dd.xls Paste Data (values, formats, column widths) to the new spreadsheet * Delete Several Columns from the new spreadsheet * Rename the sheet it's been pasted to Re-filter data (new filter) * Copy Data * Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 * Set the header of the spreadsheet with (Person's Name) and the date Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
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 have 2 separate spreadsheets. Spreadsheet #2 is a full list of items. The item names are in Column A. In Column B is an associated primary number. In Column C is an associated secondary number. Spreadsheet #1 is a partial list of items. The item names are in Column A. The primary number is in Column B. There is no secondary number given in Spreadsheet #1.
My objective is to find out what items in Spreadsheet #1 have a secondary number in Spreadsheet #2, and to group them all together so they can be easily identified. I am attaching a couple of example spreadsheets to give an idea of what I'm working with. The example's are small so it would be easy to just eyeball them and see what ones match up however the real spreadsheets I am working from are thousands of lines long so obviously eyeballing them is not the ideal way of doing it. Is there a formula I can use to reach my objective?
I have a large excel spreadsheet with alpha-numeric data. I want to be able to compare two cells in different row side by side and return the difference in another cell.
e.g. I have in cell B2, "tom, rick, mike, I" and in cell C2, "mike, rick". I need to compare the cell C2 to cell B2 and return the difference in cell D2 which in this case would be the characters " tom, I". They are separated with "," and they can be in different order as you can see in the example.
After I imported this data, the date and time is in the same column in the format of "mm/dd/yyyy hh:mm:ss" military time. How do I write a VBA code to split up the date and time into two separate columns. One column would only have "mm/dd/yyyy" while the other only have "hh:mm:ss" in military time.
Eventually, I need to extract information from the data by looking for a specific time. I would also plot time vs something.
I don't know if treating it as a string would work, because it would just become a text rather than a time, right?
I have a spreadsheet with a few thousand rows, yeah i know..lol Each one of the rows has contact details for individuals. I have the address for each person in one field with up to four different sections, seperated by tabs. How do I go about seperating each part of the address so it is in a different column? I have tried text to columns, and it only seperates the first portion of the address, seperated by the delimiter 'tab'.
The following code takes a large range of data (currently 20K+ rows) and breaks it out into separate worksheets. This takes a while, and I have been trying to insert a progress bar into this macro, but the progress bar goes in reverse (from 100 to 0) but the userform will not unload at the end.
Sub MoveCells() Dim objBook As Workbook Dim objSheet As Worksheet Dim lngRowSpace As Long Dim strName As String Dim lngTimeRow As Long Dim lngStartRow As Long Dim lngInteration As Long Dim strDataSheet As String Dim boolError As Boolean Dim counter As Integer Dim PctDone As Single
splitting out date/time strings to separate cells, but I cannot find how to split the following.
I have the serviceable date of an aircraft delivery set as 'S hh:mm dd-mmm-yy', which is due to being used by an external application in this format. This is entered by the user and I need to be able to extract the date time strings and switch them to dd-mmm-yy hh:mm, excluding the S from the result as a formula in another cell.
I keep getting a #value error or returning the whole string result.
Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg
Concatenated comma delimited data
Cust Name Cust Identifier Locations L Kim 543545 California,Chicago,Florida S David 31434 Maryland,Vermont,Maine D Bryant 572575 Texas,Oaklahoma,Nebraska
i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one
Customer name Customer Identifier Location L Kim 543545 California L Kim 543545 Chicago L Kim 543545 Florida S David 31434 Maryland S David 31434 Vermont S David 31434 Maine D Bryant 572575 Texas D Bryant 572575 Oaklahoma D Bryant 572575 Nebraska
I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.
Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.
VB: Sub Create_Subfiles()
Dim FDMName As String Dim FBName As String Dim DIYName As String Dim WMName As String
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.
Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g: A2: aa,ab,ac
String Value aa 1 ab 1 ac 3
What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.
I have got a few spreadsheets which save information for each departments, about 8. They contain information on bookings & I am being asked to get information on certain date ranges, mainly monthly reports. (They are being filled in automatically from another excel file.)
Can I have a file which I could call "Master file" which has a macro in that looks up and copies all the relevant rows from each departments spreadsheet into the new master file. I dont think this would be too difficult (still beyond my capabilities though) My intial thoughts were something like (in half code half english).
Create a form where you specify your date range and a submit button e.g. txtstartdate txtenddate
Lookup 'G:FolderGeneral[General.xls] IF in between txtstartdate and txtenddate then paste
ActiveWorkbook.Sheets("master sheet").Activate Range("B2").Select Do
I'm doing an audit where I need to compare data between two spreadsheets that are housed on the same worksheet. The data I'm trying to compare is the ssn#. I'm expecting duplicates, I'm trying to find ssn#'s that do not match up with another. In other words, if one spreadsheet has 92ssn#s, the other has 79, I want to be able to identify those that do not have a matching ssn# from one spreadsheet to the other.
Both spreadsheets have the following columns:
Column A Column B Column C SSN Last Name First Name
The tab on the bottom of sheet 1 is titled "CX Data", the tab on the bottom of sheet two is titled, "BCBS of AL Data"
in creating a VLOOKUP formula that will meet my needs?