i have fixed headings in row 1. these could use up to 20 columns
in row 3 i could put data under any of the column heading
if i put data in any of the columns i would like a new worksheet created. the name of that worksheet to be the column heading not the data i have just entered. if no data is entered then no worksheet is created
I'm using some workbook-scoped named formulas to define some dynamic ranges which will be referred to by numerous worksheets. The named ranges are defined like:
NAME: gTable_costDetailsEquipment
REFERS TO: =globalParameters!$B$5:INDEX(globalParameters!$B$5:$C$1048576,1+countAdjacentNonBlank
(globalParameters!$B$5,"down"),1+countAdjacentNonBlank(globalParameters!$B$5,"right"))
From either of the tabs "Reports" or "DOR_Template" the user can press the large "+" icon to add a report (which copies the template or the last report to a new sheet).
When this Sheet copy takes place, excel is repeating my named formulas - this time it's making LOCAL versions scoped to the newly made worksheet.
I've used this copy sheet trick before and have never had excel create new, locally scoped, named formula for each workbook level name.
I also just recently started using excel 2013, is this a problem with the new version? I've just never seen this problem, usually workbook-level names are NOT duplicated on sheet copy.
Macro that could look at a row and take the values of two cells, combine them and then create a range name for a third cell in the row.
For example; for row 5420, in column C there is the word Florida, in column D there is the number 6235, and in column F there is a sentence or two. Is there a way to automatically create a named range for the cell of column F that would be named FLORIDA6235? And if so, can the macro do this for every row even if the word and/or the number changes.
I have roughly 28,000 rows and nine columns that I am working with. That's why I was wondering if there was a way to automate this. There are 10 states and I don't know how many different numbers attached to the states, however there are many state and number combinations that repeat, so there would be several rows with Florida in column C, 6235 in column D but a different description in column F.
I have to input information into a master worksheet which contains data (employee names, location, start date and client) that then needs to be repeated in several other sheets. I want to input the data into a master worksheet and then automatically populate the other sheets with the data. If the data could then be filterable on each sheet that would be good.
View 9 Replies View RelatedI am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies View RelatedDid anyone use the above tool ? Can we use a macro to launch that tool to search for words in a excel cell ?
View 2 Replies View RelatedI know this is probably very simple and basic but I have a workbook with a total of 8 worksheets.
The first sheet is going to be a summary of information contained on the 7 other worksheets.
All 7 sheets have the same header row and I would like to have it so that all 7 rows repeat the same headings and if I add more sheets they automatically also have the heading row appear.
Is this possible other than doing a copy paste when you insert a new sheet? Also is it possible to code it so that should a new colum (heading) be added to the first worksheet it would automatically be added to all the other sheets?
The first summary sheet will have headings that reflect the name of the employee and then the names of the following worksheets as the header row and will gather information about the progress of each employee on fulfilling the requirements setup on the other worksheets.... that is yet another question on how to get it to do this.... which I will post later..
Is it possible to have a tab named by referencing a cell within a worksheet?
View 9 Replies View RelatedPls help me do a macro that upon clicking a button it will copy and paste a column heading (located in Row1 of Sheet1) to all non-empty worksheets (Sheet 2 to Sheet N) in the workbook simultaneously. Note that each worksheet has no column heading and has the same number of columns as that of the column header in Sheet1. Worksheet 2 to Worksheet N contains data extracted daily.
View 7 Replies View RelatedI'm trying to set up a worksheet so that when i enter a number in say cell J5 it will automatically create a new worksheet with the next number in order so if cell J5 was 1500 it would create a worksheet named 1501.
View 8 Replies View RelatedI am trying to apply named cell from another worksheet to existing formula.
For example I have a formula in sheet2 which looks like =sheet1!A1+sheet1!B1. I then subsequently name sheet1!A1 = Jan and sheet1!B1 = Feb. Thinking that if I apply these names the formula in sheet2 would look like =Jan+Feb. However I get the message "Microsoft Office Excel cannot find any references to replace" when I try to apply the names using Excel menu "Insert - Name - Apply".
I am successfully opening a .csv file using a variable value stored in a named range in my Main file (the variable includes the directory and path).
I copy data from the .csv file to the Main file then I need to close the .csv file without saving but I want to do that by using the
Windows("xxxx").Activate
command where "xxxx" is the namedrange in my Main file which stores the .csv filename (without the directory and path prefix).
I can use the
ActiveWindow.ActivatePrevious
command but if I have another workbook open, this one closes instead of the .csv file I opened from the macro.
I realise this is probably very basic and I've searched the forums but can't find any identical postings.
I did a search for " import text" and found some promising leads, but not exactly what I was looking for. I have tried running macros and looking at the code but don't know how to pass the file names from the the D47:D147 range to VBA(see below). I saw elsewhere that Excel can be told to create a temporary batch file, and that the batch file with the command "copy text1.txt+text2.txt+text3.txt all.txt" for example could be used to merge the 3 text files into a file called all.txt. I don't however know how specify the path where copy starts, to tell the batch file to look in subdirectories or to pass the file into Excel. I've posted this question yesterday to Yahoo Answers http://tinyurl.com/omers and http://tinyurl.com/rfww9 without much luck.
Below is the macro I would like:I have text files whose names are found in the range D47:D147 although without the appended ".txt" extension. The sheets can only contain one name sometimes, but on average 8 to 10, so in the average case only cells D47:D56 would have entries.The text files are found in the say H:Textfiles directory or subdirectories.I would like Excel to find these files, concatenate them with a row between each file, and paste the results into cell K251.Finally, the text import wizard should be used with a space as a delimiter and the last 3 columns (it's sometimes only 2) of the concatenated file, not imported.
Public preValue As Variant 'Declares a variable to be used in both macros
'This is the first line for a macro that runs when a cell is changed.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Limits the change to 1 cell only
If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub
Target.ClearComments 'Clears any existing comment
Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format( Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 'Adds a new comment with the text. CHR(10) is a return.
End Sub
'This code sets the variable preValue when a cell is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'Limits code to 1 cell
If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub
preValue = Target.Value 'Set the variable preValue to the selected cell's value
End Sub
How do I take the changes made from the first work book and insert them in a cell on the second work book. Where in the code above do I Put this command.
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)
View 5 Replies View RelatedI occasionally give presentations with Excel and would like to make it easier for the audience to see a particular cell when I move to it. The cursor can be tiny, and some people have a hard time seeing the cursor, so I use the keyboard to navigate to the cells I talk about. Yet, the cell, which then has a border around it, still can be hard to see.
Is there a way in Excel to have a cell that is highlighted, that is I move to a cell with the keyboard, so that it pops out in a different font color or background or format, when I move to it, and it automatically reverts to its usual format and color when I move away from it?
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.AutoFilterMode = False
Range("d6:g6"). AutoFilter
Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _
Criteria2:=Range("e2")
I've attached the file also.
What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ??
(2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "
I have read post re this question but have not been able to answer my problem. I get the error message 'Application defined or object defined error' when running the code below. I should indicate the range counter currently indicated about 6,200 rows that this code will work on and the individual range names in the list of 6,200 rows are spread over at least 20 worksheets.
The code appears to be running but after some time it stops on the line of code 'Range(Cells(i, 1).Value) = Cells(i, 2)'.
Sub PopulateWithImportData()
Dim counter As Integer
counter = Sheets("Imported Data").Range("Counter")
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Worksheets("imported data").Select
Range("a1").Select
i = 1
Do Until i = counter
Range(Cells(i, 1).Value) = Cells(i, 2)
i = i + 1
Loop
I am very new to vba and trying to figure out an auto save macro that will automatically save my workbook when a certian cell range is changed. Right now I have a macro set that automactially record the time and date of when a change is made to the name cell, I want to set up a macro that will automatically save the file when the time is updated.
This program is used by several users and they have a tendnecy to forget to save the program so that when other people want to check the updated data nothing has changed because the changes have not been saved. I have attached the file that I am working on. When a change is made in column F then Column G automatically updates, now I want column G to trigger autosave. I would also like a msgbox to appear to tell user that file has been saved.
I am trying to use the worksheet calculate event to automatically change the color of a cell only when that particular cell changes. In E2 of the worksheet is a formula use to determine rating based on the result of 2 other cells. The rating is classified as follows
Low
Moderate
High
Maximum
I would like to generate a different set of color to the cell and fonts for each of the rating. For example,
"Cyan" to the cell E1 and E2 with Black font if the result is "Low"
"Plum" to the cell E1 and E2 with "Black font if the result is "Moderate"
"Blue" to the cell E1 and E2 with "White" font if the result is "High" and
"Red" to the cell E1 and E2 with "White" font if the result is "Maximum"
i have a file that people enter sales figures into each row. the numbers are sometimes beyond 2 demical places.
how would i go about making a macro button, that when pressed, AwardTotal (name of the all the award cells in column e) is rounded to two decimal places. I then would like to sort all the rows that might be inserted, by column "C" (name field).
people will insert rows, with row 11 being the first row with data. Row 10 is hidden, and row 9 is the header row. but the number of rows inserted will be different each time.
I already have a button set up (that someone helped with here of course) that asks how many rows to insert.
I am trying to merge 2 files (FileA and FileB) into a new file which will be automatically named based on the date. I would only need Sheet 2 of both files to be copied over and merged into the new file. The range to be copied is from Row 3 onwards to whichever point where the data ends. All the files can be found in a specific drive and the new file should be created there as well.
View 4 Replies View RelatedI am quite new on this forum.
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
I have a number of dates (columns) and under each date there is the demand value (Rows). Also, i have a column that has the current inventory. what im trying to do is to keep on adding the demand in one row (i.e multiple dates) until the sum just exceeds the inventory. After that, i would like to return sum the date at which we stopped adding. the point of this excercise is to see at which month will our inventory deplete according to the demand. Below is an example solved by hand.
Demand
Part #
month 1
month 2
[Code].....
Im trying to create a userform that will enable the user to select a record from a listbox and then to edit the info for that record by changing the contents of text boxes which are set to display the current info. What I have seems to work intermittently. I basically use autofilter to find the selected record on the worksheet, then I set the value of each cell in the row to that of the appropriate textboxes. ListBox2.Column(4) contains the unique id for the selected record.
Private Sub saveclient_Click()
Dim WS As Worksheet
Dim newrng As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set WS = Sheets("Clients") '<<< Change
Sheets("Clients").Select
WS.AutoFilterMode = False
Set newrng = WS.Range("A1:e" & Rows.Count)
MsgBox Me.ListBox2.Column(4)......................
Display Alert On Closing If Cell Not Filled In
How do I alter this code so that it acts like an INDIRECT function so will pick up a cell reference in another cell?
I can't just use A1 as I want this to change dynamically.
I have recently learned a little about Excel. Enough to be able to do our payroll for my company on it. My question is: On the 1st page of my workbook, is my quarterly report which shows all taxes, social security, and medicare we hold out each payday for each employee. The rest of the worksheets are for each employee. How do I have each employees withholdings automatically go to my quarterly report page?
View 5 Replies View RelatedI am starting within a workbook with a standard invoice/form. During the course of a month this invoice will be copied 20 to 30 times within the workbook and the amounts changed each time. Is there a way that I can:
a) Have amounts from 2 of the cells (say E30 and E31) within each new invoice worksheet automatically included on a summary page? These cells are calculated by formula.
b) (This one would be nice but is not essential) Have each new invoice worksheet that is added (by copying) automatically numbered sequentially in a cell (say A2) and also re-named with this number. The first existing invoice would be manually numbered and each additional invoice would follow from there.
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
I have a workbook 'logsheet' were i enter data like 'coil, supplier, thickness, width' and 'lot'. This happens 7 times on one logsheet. When the logsheet is filled, a new copy is created (from a hidden empty logsheet).
I want this data to copied to a seperate sheet - eg called data - in same workbook so that it can be used in other workbooks as external data.
I want to enter a formula on the logsheet worksheets and not on the data logsheet because the amount of logsheets (and coils) is unkown and can vary each lot.
Is this possible with formulas eg copyto? (if that formula exists ><)
Is it possible to add data from worksheets that still have to be created from the empty logsheet
