I want to make several charts with dynamic ranges. To do that, I wrote the offset functions I need in cells. I've attached a sample spreadsheet. I want to have a macro so that when I run it, it will take the contents of the active cell, insert a name called those contents, and make it refer to the cell 4 to the left of it.
In the spreadsheet, I'd like to be able to click on E2 (sentdate1), create a name called sentdate1, and make its value A2, or =OFFSET('Sentiment'!$A$3,0,0,COUNT('Sentiment'!$A:$A),1). Here's what I have so far.
Sub Macro8()
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveWorkbook.Names.Add Name:="sentdate1", RefersToR1C1:= _
"=OFFSET(Sentiment!R3C1,0,0,COUNT(Sentiment!C1),1)"
End Sub
Auto Merged Post Until 24 Hrs Passes;Here's the sample worksheet.
I'm trying to create a macro which will run through every salesperson in a list, and copy and paste their product and client data (from separate sheets) to a template sheet. The template sheet will then by copied to the Salesgroup workbook, and renamed to the salesperson's name. Then the macro should go back to the original workbook and do it all again for the next salesperson, putting them into the same workbook.
The problem I'm having is that the references to the Template worksheet seem to be being messed up by copying the template sheet to the new workbook, and it then ends up creating all the worksheets, but it stops taking the data from the data sheets, and ends up copying the previous worksheet and pasting that into the new copied template, which means that I get the template multiple times across the same sheet.
Sub SalesSheets() Dim Salesbook As Workbook Dim NewBook As Workbook Dim SalesDataSheet As Worksheet Dim ClientDataSheet As Worksheet Dim SalespersonListSheet As Worksheet Dim Template As Worksheet Dim rRange As Range Dim rCell As Range Dim strText As String Dim SalesGroup As String Application.DisplayAlerts = False Application. ScreenUpdating = False Set Salesbook = Application.Workbooks("2008 Salesperson TEST.xls") 'change filename here Set Template = Salesbook.Worksheets("Salesperson Template") Set SalesDataSheet = Salesbook.Worksheets("SP product YoY") Set ClientDataSheet = Salesbook.Worksheets("Client YoY") Set SalespersonListSheet = Salesbook.Worksheets("Salesperson List")..........................
I have a worksheet where a number of the cells have been assigned names. I want to copy these cell names & create a list on another part of the worksheet. Does anyone have some code I could use to achieve this?
I have a list of names in column A, and want to automatically create worksheets within this same workbook based on the list.
List of values: Dairy NI Dairy SI Sheep and Beef Equine Sports Turf Water Industry Horticulture Services Production Horticulture Other Agriculture
Therefore, a macro or coding to automatically create 9 spreadsheets named as per this list.... My list is approximately 50+ hence inquiring if there is an easier way!
I am at it again and maybe I am asking to do something that is not possible. I am trying to build a program that will allow the user some flexibilty down the road if I am no longer here to support my code. I have a workbook that contains several sheets. The sheets are labled by equipment name. The Sheets themselves contain specific data with regards to the equipment. What I want to do is as follows. Populate a user form with command buttons based off of the worksheet names. The reason for this would be if 2 years from now we add a new piece of equipment all the user will have to do is add a sheet in the workbook and the user form would reflect the new sheet with a new button. Like wise if I delete a sheet in the workbook the user form would refelect that change as well.
table looks like following: DepID name function S1 a YY S1 b XX S1 c ww S2 d oo S3 e ii S3 f ll S4 t mm . . . . . .. . . . . . . . . .. . . . . . . . . .. . . .
S7999 u ee S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
I have two sheets. Sheet A has a list of folders. This list is updates dynamically every time the sheet is opened. I have another sheet (sheet B) which has a list of all the files in the sub-folders, listed with the folder name at the top of the column and the files within in the column underneath with hyperlinks to them. The goal is to be able to navigate to the folders on sheet A and to click there to go to the column where all the files in that folder are linked to. What I need is a macro that will search a column and for every cell that has text in it and create in the column directly to the right a hyperlink to the appropriate cell in the top row in sheet B that has the same name as the text in the cell on sheet A that it finds. I already have the macros for listing the folders on sheet A and the macro for listing all the files in the sub folders in sheet B and they work fine. The goal is a link list which is dynamic and that recreates itself no matter what I add to the folders.
For instance, let’s say there is a folder timesheets. If I add a sub-folder called accounting to the folder Sheet A scans then timesheets would be bumped down one spot on the list, so the macro has to look dynamically for the text in the column on sheet A.
Also the addition of the new folder would move the order of the columns on sheet B, since both lists are alphabetical. So the link generation macro would need to search row 1 of Sheet B to find the match for the text in the cell to the left of where the link would be generated and create a link to the top of that column. I’ve looked all through the site and not found something that I can even modify to do what I need.
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel].
I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
I am trying to create a summary Calendar that would display all the names of the people who are off on a given day. I have a grid where I display each day of the month and a person in the group has their own column. If they are out of the office for any reason their name appears in that row. In the big Summary Calendar cell for each day of the month I want to look across all the people columns for that day and then display the names of the people who are not going to be in the office. Can I do this by a formula or will using VBA work best.
I have a macro that using a list of values in a range, checks if there is a corresponding worksheet and if not, use the Template to create one, rename the worksheet and add the value into a cell.
I now have 2 types of template and based upon on the value in the range, I want it to create a worksheet using 'Template 1' for all values < 1000 and 'Template 2' for values > 1000.
Is it possible to amend my code for this?
HTML Code:Â
Sub CreateTimeline() Dim rng As Range Set rng = Application.Range("Projects[No.]")
I have a list of references in a range (A5:A98) in a worksheet called "Architectural". What I am trying to do is create a seperate worksheet based on each cell value in the range. However, rather than blank worksheets, I would like each new worksheet to be a copy of a worksheet called "Template". If possible, I would like to have the name of each worksheet also put into cell B2 of each copy.
I have file contains two worksheets. 1st worksheet named "list" and the 2nd one named "Template". I have a range in WS "List" let say a1:d20 that contains names. This range is not fix, it might be more or less. I need creating new worksheet using worksheet "Template" for the new sheets in the mentioned range as follow:
"Create new worksheet for each name in this range."
List is not unique, some names are duplicated. If the name repeated, create only one. Use worksheet template for each new worksheet created.
I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P?
I have multi checkbox in column F,
So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1.
I want to entry data in the Range H14:P? by this code
Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
However, I want to use this same function in another place, without the limit set on the cells that will be cleared.
What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.
I need to activsheet create in .pdf witch i did with this
VB: Sub Save_As() ' macro za snimanje racuna u PDF pomocu Microsoftovog ugradjenog alata za Office 2010 Dim txtName As String txtName = "D: eports" & Range("G14") & "_" & Range("L3") & "_" & Range("E18") 'ime nove datoteke i folder u koji se snima ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ filename:=txtName, _ OpenAfterPublish:=False End Sub
and for that pdf file i need to create folder based on cell e.g. q1 and sub folder l3 and all that in case that they dont exist and if they are just put file in it. For example i fill out sheet (one of many in one day) and i click buton and sheet go to pdf in subfolder L3 in folder Q3.
I have a database output file where one of the columns contains managers names, often more than once. I want to apply an autofilter on manager name and then copy the result to another sheet or sheets. My criteria for the autofilter is a variable pointing to a list of names that at present I maintain by hand; a for-each-next loop then cycles through the names.
What I would like to do, before running the autofilter code, is to create the list of names via code. This would then automatically pickup names that are missing.
The code I have so far is below:
Public Sub find_managers() Dim managers1 As Range Dim names1 As Range Dim n1 As Variant Dim n2 As Variant
In my mind it should check the names in the unique list against the imported list and add any missing names.
I am trying to create a bar chart that changes color based on a percentage range. Any advice on how to do this? I am using Excel 2007, Windows XP, Sharepoint 2010.
I am trying to output tables from excel to an HTML file using the following subroutine. It creates the table as I would like, but I am having a brain cramp trying to figure out how to get the cell data into the routine. The part in error is in the code below, in Red. I need some way of entering something like " Sheet(SheetNum)&.Range(Column&Row)&.Value"
Private Sub CreateTable(Center As Boolean, Border As Integer, Width As Integer, SheetNum As Integer, StartRow As Integer, EndRow As Integer, StartColumn As Integer, EndColumn As Integer) Dim Row As Integer Dim Column As Integer Dim ColumnID(1 To 15) As String Dim CellLoc ColumnID(1) = "A" ColumnID(2) = "B" ColumnID(3) = "C" ColumnID(4) = "D" ColumnID(5) = "E" ColumnID(6) = "F" ColumnID(7) = "G"..................
I would like to have a sheet where there is a cell designated as "start number" (where any number may be entered) and a cell designated as "how many times". I would then like to be able to click a button and excel generates a list of sequential numbers starting from the number designated in the "start number" cell as many times as is designated in the "how many times" cell.
I have had some help with this (thanks!) but am stuck.
Can someone add to the code below and make it so that multiple tasks, with multiple assignees, can be created from a range of cells, such as:
Column A Column B Row 1Task SubjectAssign to Row 2GV LP for AZJohn Brown Row 3GV LP for COJohn Brown Row 4GV LP for ILSuzy Smith Row 5GV LP for INSuzy Smith Row 6GV LP for KYSuzy Smith Row 7GV LP for MNSuzy Smith Row 8GV LP for MSBob Barker Row 9GV LP for NCBob Barker Row 10GV LP for NJBob Barker Row 11GV LP for NMBob Barker Row 12GV LP for NVBob Barker Row 13GV LP for NYBob Barker Row 14GV LP for OHBob Barker Row 15GV LP for SCBob Barker Row 16GV LP for WVBob Barker
(Sorry, it didn't translate well. The "GV LP for AZ" is cell A2, "John Brown" is cell B2.)
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
Ive created the following code which I want to use to create a name in excel but it doesnt work.
when i put =INDIRECT i get an error and with out the = it places a text string in with speech marks on it e.g. defined name reference in excel reads ="INDIRECT('Health and Safety'!$AP$3)"
How do I get rid of those speech marks? the cell reference will change each time the macro runs and so will the sheet reference.
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
