Create New Sheet For Each Group & Copy Needed Data To Each
I have just inherited an Excel spreadhseet that displays job titles and what types of documents each one should be trained on. What I am trying to achieve is a formula, or something to that sort, that will look at each job individually and tell me only those documents that they need to be trained on and list it on a seperate sheet.
If you look at the attached document you will see that the first 2 columns contain a document number and description. Across the top you see each of our job descriptions. In the middle you see an X that indicates that person should be trained on that document.
So once more, I would like to take each Job Description (seperately) and list only the documents that have an "x" in their column on another sheet. I already have the sheets created, I just need to know if there is some kind of formula or macro that can do this for me.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Copy Group Of Rows To New Sheet
I have a workbook with data that represents trouble tickets assigned to different applications and sorted by application. I want to select all of the rows assigned to one application, and paste them into a new worksheet, then do the same for the next application, and so on. I don't know which specific applications I will be looking for, as this will change from day to day (the system has more then 160 apps). I've attached an example.
View Replies!
View Related
Copy Sheet & Create New Monthly Sheet From Present Sheet
I want to create a macro button that can create copy, insert, paste and rename the new sheet in next month's name, like if the active sheet's name is January, I want to copy the whole sheet of January, insert new sheet, paste the new sheet and rename the new sheet to next month like February? Also rename the new sheet (February) cell B3 the same as new sheet's name (February) So if month of February is near end, the macro button in February will create the same way as Jan did which means the next sheet will be named March and so on.
View Replies!
View Related
Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet
I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated: 1-the sheet is renamed to the value of F1, 2-a new tab is made (a carbon copy of the hidden sheet "Template") 3-the new tab is named "New Tab" and marked as unhidden. Public Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Name = Range("F1").Value End Sub Function WorksheetExists(SheetName As String, _ Optional WhichBook As Workbook) As Boolean Dim WB As Workbook Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook) On Error Resume Next WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0) End Function..................
View Replies!
View Related
Create Copy Of Sheet For Each Name In List
The following URL has a great solution to this problem, I was unsuccessful in implementing it. Excel VBA: Create Worksheets for Each Item in an Excel Table of Data Need A macro to copy a pre-set sheet, we can call it a "template sheet", for each name in a pre-set range, a list. This list will have empty cells and names from F9 to F190. For each name i need it to copy the template sheet and place the sheet name within the new sheet. Should a user delete a sheet, activating the macro again should re-create the missing sheet and not just crash (was told there could be an issue).
View Replies!
View Related
Create New Sheet For Each Row Of Another Sheet Auto Populate Data.
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on. from row 2 onwards the data has been populated for roughly 200 rows. i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet. my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'. how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row? i know this is possible i just dont know how to do it.
View Replies!
View Related
Macro Pull Data From Each Sheet And Create A Summary Sheet
I need to create a macro that will scan a spreadsheet for the number of sheets and then pull data from the same places on each sheet in order to create a summary sheet. Let me try to explain a little better. The spreadsheet I'm working with has a separate sheet for each new deal our company makes. Each of these sheets is in the same format - we use a template and fill in the data based on that whenever a new deal emerges. The sheets contain basic info about the deal in the first few rows and columns, then some narratives with dates describing the progress of the deal, and then a list of issues and whether or not they have been resolved. The problem is, each of these sheets contains too much info for a quick, high-level overview with the bosses so they've asked me to create something that will pull the basic info, the most recent narrative, and any unresolved issues from each sheet. This way, each time there's an overview scheduled with the bosses, the macro can be run and it will create a new sheet with data from each sheet in the workbook.
View Replies!
View Related
Create Copy Of Sheet & Name From List
I am trying to create a copy of the sheet "template" using a list of department numbers from the sheet "list". The following code works sometime but other times it stops without copying a sheet for all the names in the list. Sub CreateNewSheets() Dim NewSht As Worksheet, Crow As Integer, NewName As String Dim c As Range, wks As Worksheet, TempName As String, BaseName As String Application. ScreenUpdating = False Set wks = Sheets("List") 'list of department numbers to be copied for a sheet.
View Replies!
View Related
Create Copy Of Active Sheet & Convert Original To Values Only
I need to copy the Selected Sheet (Sheet name will be different each month) on a spreadsheet and paste the copy to the left of the selected Sheet. Then I need to copy and paste values the entire sheet of the sheet that the copy was made from (the one on the right). I am very new to macros, and I tried recording and manually editing the macro with no success. The number of sheets will be different always as I will be adding this to different workbooks and also because new sheets may be added to any workbook at any time. I attached my code that I came up with, as I am not familiar with code enought to "[code]" my code.
View Replies!
View Related
Create Workbooks & Worksheets For Each Group In Table
I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report. The columns are as follows: Table Joined Tables Column Name Column Alias Column Description Column Data Type Column Length From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.
View Replies!
View Related
Create Individual Worksheets For Each Related Group Of Table Range
I have a huge worksheet containing Blackberries expenses of my company divided by multiple sections. Each section follows the following pattern: Example: Client 222-5555 MY COMPANY NAME LCC International Calls - Call to Bahamas on June - $50,00 Local Calls - Call to NY on June - $ 30,00 Total Price - 80,00 I would like to select each row between client and total price and paste then on multiple sheets in order to create individual invoices. The selection must be based on the client number (for instance 222-5555) that is part of the cell value.
View Replies!
View Related
Copy Data From One Sheet (Fixed Cells And Sheet) To Another Sheet
I want to be able to copy a name from one sheet (Available Players), paste it to a cell in another sheet (Round 1 through Round 20). The cell that will be copied is fixed but the place where it will be pasted will be different and may be on a different sheet. also i would like to change the color of the copied cell to "greyed" out or cut if it can not be greyed out. I have created a button and put in a macro that i created but have been having problems with it, generic 1004 errors that i can not figure out. i am attaching the document.
View Replies!
View Related
Array (delete Un-needed Data)
Is there a way to delete this un-needed data while it is still in an array (effectively resizing the array - removing specific columns and rows) and then writing the array back to the worksheet? Example: (5X5) Array with the following data
View Replies!
View Related
Data Dump Of Charted Data Needed
I have an interactive moving average model (which has an adjustable moving average feature) which successfully charts the moving averages of the data on the chart. I have coded this with VBA. However, I would like to get a data dump of the individual moving average data points. That is, I would like to see the individual data points (that are plotted on the chart). Would anyone be able to help me with this?
View Replies!
View Related
Create Multiple Sheets And Copy Data To Them
I have a worksheet with a list of project managers and a bounc of data on them. How can i make a new file with sheets for every project manager in the list and copy the data for each one into his own sheet ? Every project manager has many lines (there are about 200 managers and 30 000 lines and 30 columns ) I have managed to create a sheets for project managers from a list of unique entries but that's all.
View Replies!
View Related
Formula Needed To Count Cells With Data
formula to calculate how many cells in a column contain data of any kind. The data might be a number, a word, or even a symbol, such as "):>:)" for example. The range will change from time to time. For example, one day it might be a2:a4956. A few days from now I might delete a few rows from the top, such as rows 2 through 26, so then the range would be a2:a4931. Then I might add rows at the bottom with data that I need to be counted. I dont want to have to change the formula evertime I add or delete rows with data. I do not want the formula to count a blank space created by the space bar as "data." One formula I used sorta worked, but if the cell appeared empty, but had been cleard out by pressing the space bar instead of the delete key, the formula counted it as a cell with data.
View Replies!
View Related
Copy Row From Sheet 1 To Sheet 2 When Data Entered In Cell F
I have Worksheet 1, with columns A to E. I would like a row to be copied to Worksheet 2, as soon as cell F in Worksheet 1 is populated. Also the row to be deleted from Worksheet 1. So, as soon as F1 in worksheet 1 is populated and enter button pressed, row A1:F1 will be copied to the next empty row in worksheet 2, while being deleted from worksheet 1. So eventually all rows in worksheet 1 will be deleted and rows in worksheet 2 will be populated.
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Copy All Data From Data Sheet, Sequence Top Down By Spend, Manually Delete All After 50
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet. In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low. The workaround: Copy all data from data sheet, sequence top down by spend, manually delete all after 50. My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above. Is there a way to do this without VBA i.e. pivot tables etc
View Replies!
View Related
Allow For Group/ungroup On Protected Sheet
I'm building a template for various users. I have protected the sheet with only certain cells available for input. The file is very large and there are some sections that can be group/ungrouped as the user desires. however, once I protect the sheet, they can no longer click the - or + to expand and collapse. How can I enable group/ungroup on a protected sheet?
View Replies!
View Related
Group By ID And Copy To A Single Row
I am working on a macro that sorts by user ID Number (in "C") then copies cells "A", "G", "H", and "K" and pastes them at the end of the row starting at "P". What I want to do (and can't quite figure out how to do) is to is to take those copied cells and paste them together, sequentially in the row containing the first instance of the ID number starting with "P" and continuing across the row as far as necessary (there will be varying number of rows being copied). Repeating this for each different ID number. A viable alternative would be to add a couple of cells to the copy (ID Number and Name) and insert a new sheet and paste to rows there, but the end of the first row would be my first choice.
View Replies!
View Related
Copy Data To Another Sheet
I have a sheet that contains multiple rows of information, i would like to copy the details of the last entry on each row to the corresponding row on the next sheet. Normally this would be the last 3 cells on each row to be copied, but some rows will also contain a couple of cells that may be coloured which would also be required. My question is - firstly - what is the best way of copying the last few cells on multiple rows to another sheet? secondly - is there a way of copying an extra cell or 2 if they are coloured?
View Replies!
View Related
Copy Data From One Sheet To Another
I got the following code from this forum to copy data from one sheet to another. Is it possible to use it with an input box code. So instead of always getting data from a specific range, it may be selected through an input box and copied on desired location with the help of also an input box.
View Replies!
View Related
Look Up Data And Copy Into Another Sheet
All data is organized by date, and to make comparisons easier, the different types of data is in it's own individual column, and different levels of analysis is on different worksheets. "Sheet1": This is the raw data. "Sheet2": What data is valid out of the raw data, (already have a script to only show valid results based on user's input) "Sheet3": 3 days before, and 3 days after each valid date out of sheet2. I'm having a problem figuring out how to make Sheet3 automated. I need some sort of look up function for Sheet2, and copy and paste the seven raw data points into Sheet3 (As in the screenshot above). I am currently doing it manually and it's very frustrating on thousands of data points.
View Replies!
View Related
How To Copy Selected Data From One Sheet To Another
Hi excel masters, I have questions on how to built a macro to copy selected the data from one sheet (contains data) to a summary sheet, I used excel 2007. The macro I want to run to achive those result: The data on the data sheet listed similar like below: in first column (contains name) as below, xx xx xx xx yy yy zz zz in the summary sheet, I want to copy one particular name from datasheet to cell A17 and below, based on the the selection criteria I enter in cell B1. For example, if I want to show xx in summary sheet, I input xx in cell B1, and run macro to list xx one by one from A17, A18, A19.... I need to macro to run to search entire list to include the value I selected.
View Replies!
View Related
Copy Only Data In Some Columns To Another Sheet
I am trying to copy rows that match a date range but I only want some of the columns to be copied to a pre-defined sheet. For example, if the row is selected as a match because it is within the desired date range, I want the data in columns 'A-F', 'I-J','N-O' to be copied to the sheet 'Report' starting in 'A3'.
View Replies!
View Related
On Value Change Copy Data To Another Sheet
I have a sheet in Excel (sheet 1)which reads in eleven values. If the data in the 11th value changes then I would like to copy the other 10 values to sheet 2 appending to previous values copied. Date and time on each copy would also be nice if possible. The values in sheet 1 are being read in via an OPC server from a machine and only change at the and of a batch which could take a few hours. We are trying to gather this information as a test just for stocking levels on a short term basis. We may look at a more sophisticated method after our trial period is completed.
View Replies!
View Related
Filter Data On One Sheet And Copy To Another
Is there a way to easily filter the data according to the products' "index %" of a given month and then copying all data (including name, price, index%, and index value for the whole year) of those deviating from 100,0% to a new sheet? I tried this myself with auto filters but the layout presented some problems. The sheet is created automatically by another programme, so the layout is what it is. I really hope someone might have a solution for this since I need to sort through these sheets, with thousands of products, every month by hand!
View Replies!
View Related
Autofilter Copy Data To Other Sheet
Trying to filter list and copy filtered data to another sheet in the same workbook. I'm receiving this error: Run-time error '438' Object doesn't support this property or method Worksheets("Data"). CurrentRegion.Copy _ Worksheets(" Totals"). Range("A1") Sub GetTotals() ..............
View Replies!
View Related
Auto Copy Data From One Sheet To Another
So im setting up a big list of stuff, and basically i have multiple sheets. One of them is a "master sheet" so to speak, and the rest are sub-sets of data. Is it possible to have the Master sheet updated automatically every time data is entered on one of the sub sheets? Basically its an alphabetical list of names, and my sheets are "Master sheet" "sheet A, Sheet B" etc .... so If I enter a name on Sheet B, or format the cells on sheet B, is it possible to have that automatically copied to the master sheet? its becoming a pain to have to enter the same name on two sheets manually.
View Replies!
View Related
Compare And Copy Data From One Sheet To Another.
SalesImport contains sales data, and one column of that sheet is a unique reference number to identify the agent responsible for those sales. Main contains much more data, and I need to transfer sales data from SalesImport to Main in the most efficient, automated way possible - on a monthly basis most likely... but possibly weekly or even ad-hoc at a later date... so it really does need to be quite quick. At the moment I have come up with an idea to loop through each row on the SalesImport page, and for each row, compare the unique reference number (Column I) with the unique reference numbers on the Main sheet, (Column C). If they match, set the value of the sales column on Main to the same value as the sales column on the SalesImport sheet. However, this is not working as i would hope, and doesn't copy anywhere near all the rows I know are found in both sheets. Sub SalesDataImport_Main() Dim c As Long, d As Long, Limit1 As Long, Limit2 As Long, Limit3 As Long, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet Set sh1 = Sheets("Main") Set sh2 = Sheets("SalesImport") Limit1 = sh1.Cells(Rows.Count, 3).End(xlUp).Row Limit2 = sh2.Cells(Rows.Count, 9).End(xlUp).Row For c = 2 To Limit2 sh2.Cells(c, 9).Select For d = 3 To Limit3 If sh1.Cells(d, 3).Value = sh2.Cells(c, 9).Value Then sh2.Cells(c, 27).Value = "In Main List" Else: sh2.Cells(c, 27).Value = "Not in Main List" End If Next d Next c End Sub
View Replies!
View Related
Get The Data From Sheeet And Copy To Other Sheet
I have 3 workbooks, I have a sheet that will have a button on it that will run the code, when the button is pushed, data from another workbook will then copy to a 3rd workbook. Control.xls - Has the button, which will run the vba code. Source.xls - Has the data to be copied. Dest.xls - Were the data will be copied too.
View Replies!
View Related
Copy All Data And Paste To Other Sheet
I have a workbook with multiple sheets. Within the workbook there is 1 mastersheet and 30 other sheets. On a daily basis, I need to copy all the data from the 30 sheets to one single sheet. For each sheet being copied, I want to copy all data with the exception of the first row. Some of the sheets may contain blank lines. I am have trouble getting my code to copy all the data.
View Replies!
View Related
Copy Selected Data Onto New Sheet...
I have a button the brings a dialog box with a list box. on selecting a figure in the listbox and clicking 'OK' the figure is transferred to a cell in a worksheet. The column under this cell has a formula with the result True/False. The autofilter then selects only 'True', and then should copy the active range to a new sheet, preferably named according to the value first selected. It must be possible for the user to have several of these ranges copied to different sheets for printing purposes, which is not a problem as this already works with the code.
View Replies!
View Related
Copy All Data From Different Files Into One Sheet
Here is my problem: Every month I have *.xls files containing one sheet with different data. I tried to make a macro whose job was to copy all data from different files into one sheet but i failed. Please tell me is there some kind of macros which will copy all data sheet from all files into one sheet and to detect every next empty row to paste the information there?
View Replies!
View Related
|
TRACKER
