I have 4 employees by 52 weeks tabs in a workbook. The first 4 tabs are as follows:
Pat 010810
Dan 010810
Max 010810
Ann 010810
I would like some method (maybe VBA?) that I can rename the subsequent tabs, having the same names but the date with a seven day increment; i.e.the next tab would show Ann 011510, etc.
I don't know how to code, but can get to the VBA page and copy really well.
I have a workbook that pulls in data from other sources and contains 15 linked sheets. The data changes when different criteria is selected in the first sheet. The first 2 sheets are summary sheets and the tabs are named correctly.
I would like to be able to rename the remaining 13 tabs using labels found in the cells in the first sheet. These tabs should update or change when the first sheet is manually recalculated (F9).
I have used ActiveSheet.Name=Range("A1") but it doesn't apply in this case. Is there a macro that could run with something like Sheet1.Name=Range("A1"), Sheet2.Name=Range("A2"), Sheet3.Name=Range("A3"), etc.... or maybe something in each sheet that would change the name of the tab to a specific name off sheet 1 when the whole workbook is recalculated?
Could you help with an onerous task that I must complete every Quarter.
I have a spreadsheet with multiple tabs.
The first 3 Tabs are Calculation sheets and do not need to be re-named.
All the preceeding sheets each need to be renamed to the days of the month (British Format), skiping Sundays.
i.e Tab 4 should be renamed 010409, Tab 5 should be renamed 020409, Tab 6 should be renamed 030409, Tab 7 should be renamed 040409, Tab 8 should be renamed 060409 and Tab 9 should be renamed 070409 etc etc ...
Extra - Also if possible on each sheet could the Tab date be placed into Cell A4 (eg. 010409) and also the Day number (eg. 01) (Starting from 01 on 010409, 02 on 020409, 03 on 030409, 04 on 040409, 05 on 060409, 06 on 070409 etc etc ...) into Cell A6.
I am pulling my hair out in frustration with this right now. I have a .csv file that I am attempting to run a macro on. I will need to move between tabs, and want to rename a tab to remove the date as it is not consistent in format. I have tried numerous ways, and I don't get an error message, but the tab is not renamed either. Code I have tried is below ....
View 9 Replies View RelatedI have a workbook with multiple tabs. On sheet one a person chooses a date to initialise a formula. That date is then copied to sheet two and placed in cell D5. The date is projected out for two weeks and then the next fornight starts on sheet three and so on. The start date for each new fortnight appears in cell D5 of each sheet. I want that information to be the name of the tab for sheet two, three etc. I have put this code into the "This Workbook" section:
Private Sub Worksheet_Change(ByVal Target As Range)
' Renames all worksheet tabs with each worksheet's cell A1 contents.
'If cell A1 has no content, then that tab is not renamed.
For i = 1 To Sheets.Count
If Worksheets(i).Range("$D$4").Value "" Then
Sheets(i).Name = Worksheets(i).Range("$D$4").Value
End If
Next
End Sub
I have also tried:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then ActiveSheet.Name = Left(Target.Value, 10)
End Sub
I then go back into sheet one and change the date and nothing happens. I seem to be missing something. Can anyone please help?
I have a workbook with 30+ worksheets
named 23 Wed, 24 Thur, 25 Fri, etc etc
meaning April 23, 2014 Wednesday, April 24, 2014 Thursday, etc etc
The next project may start on May 19, 2014 Monday - so I have to rename each worksheet beginning with 19 Mon, etc etc
Renaming each worksheet manually takes a long time and is prone to error
I would like to just rename the first worksheet to start date of May 19, 2014 and have the rest renamed automatically.
I am looking for a macro to rename tabs based on information in A1 on each tab. The thing that is causing me issues is the cell A1 has a formula in it and it changes on a daily basis with days of the week but the tab only updates when i double click A1 and press enter.
I need it to update automatically.
There is a folder in a shared location which many people use at work. Within this folder, I have an excel document with all current stock (called "Current Stock"). There will never be more than 20 items in current stock. There is also other documents within this folder which relate to lots of other things, Interest, Deals etc.
What I want to do is every time a new item is added to the "Current Stock" document, a tab is created with that Item name within all the other documents.
I pull an excel sheet from a database each day that gives me sales data for different products sold. The way the spreadsheet comes from the system, the first tab is named "Document Map" and lists all of the products, and each product then has its own tab but they're generically named (Sheet1, Sheet2, etc.). The name of the product is always in cell T8, but the name itself is only the last 8 characters of that cell.
What I'm hoping to accomplish with a macro is two things:
1) Retrieve the last 8 characters from T8 in each tab, and name the tab with those characters (same effect as the formula "=right(T8,8)").
2) Ensure that this only occurs on tabs with the word "Sheet" in the beginning. Depending on the day, there could be any number of products and they each get their own tab, so I was hoping to be able to get the macro to only work on sheets where the first 5 characters are "Sheet" regardless of the number after that.
I have 5 sheets. In the first sheet I have set up 4 cells where I want the contents of them to automatically rename the other 4 corresponing sheets.
Eg.
In Sheet 1, Cell B6, I want the contents (which will be text) to be the name of sheet 2 automatically.
In Sheet 1, Cell B7, I want the contents (which will be text) to be the name of sheet 3 automatically.
I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc).
What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).
I have a table with 6 columns (with headers A -F) and 10 rows. There are values in each cell. I want to take a value from each of the 6 columns and concatinate them. I would like some code that would generate every possible resulting string and I would like the possiblities set out in one column.
Apart from the fact that it might be tricky enough to produce the code , by my calculations there will be 10 to the power of 6 possibilities and as far as I know there are only 65000 rows in my version of Excel. If they had to be put into 2 or 3 columns I would not mind
I have the below spreadsheet and require to format it depending on what appears in column AE and column N.
example if in AE it says AAA and the figure in column N is >=-1 and Microsoft Excel - Belgium - Jupiler League.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAB3AC3AD3AE3AB4AC4AD4AE4AB5AC5AD5AE5AB6AC6AD6AE6AB7AC7AD7AE7AB8AC8AD8AE8=
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE2DateHomeAwayFTHGFTAGFTRHTHGHTAGHTRHDA51GoalsHDA17GoalsHDA7GoalsWHHWHDWHA51177*303/08/2007MechelenAnderlecht01A00DA-1.02A-2.18A-1.715.53.51.534.54.54.5AAA404/08/2007CharleroiRoeselare11D10H*****H2.00H3.291.623.45-1-1-1HH504/08/2007Club*BruggeBergen21H11DH2.65H2.47H2.001.3647.50.360.360.36HHH604/08/2007DenderGerminal12A00D***************2.883.22.2-1-1-1*
704/08/2007FC*BrusselsWesterlo01A00DA0.76D0.94H1.712.23.22.881.2-1-1ADH804/08/2007GenkCercle*Brugge31H31HH2.31H2.24D1.001.3348.50.330.33-1HHD2007-2008*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
How can I put in a formula which will look at the contents of cell A1 and B1 and then if the contents of cell A1 match you put in the contents of cell B1 into B2. I know this sounds confusing so let me explain cell A1's contents are 1 and B2 has the amount of $312.50 so if cell A2 is also a 1 then I need to put in the amount of $312.50 into B2, same would go for B4 since A4 matches the contents of A3 I need to put $625.10 into B4....
View 9 Replies View RelatedI have a file with thousands of records and on the name field, I have case like this:
1 "John Dough"
2 "John Dough "
As a result, when I pivot the data, I see duplicate lines for the same name. How can I eliminate the extra spaces at the end (row 2 and 3 above)?
I need to transfer a large amount data (~2,5 million lines of data) from one spreadsheet to another while making sure the destination file is kept in a certain format.
Spreadsheet "oxno" is the file that needs filling out with data provided to me in spreadsheet "ox"
The value that is the matching criteria will be the ITS account number (Field B in "ox" and field A in "oxno") and the currency (Field B in "oxno" and field G in "ox")
So I want a macro to look in "oxno" and use field the values of field "a" & "b" and search for the them in "ox" in fields "B" & "G". Once it finds a match it should copy the value of field "I" in "ox" and paste into field "n" on "oxno".
The next data to be copied should be what goes into field "O" in "oxno" this data is derived from field "L" or "M" but only when its of numerical value with one non numerical field in it (usually a "/"). Last value that I need copied over is Field "T" on "oxno" this is derived from field "J" or "M".
I realise the potential conflict because two sets of queries will be looking to gather values from field "M" on "ox". That is why I am hoping when we look for value for field "O" in "oxno" we only look for numbers with one non numerical field in it (usually a "/") .
After finishing writing this up, I realise how wordy this has become appologies for that, but hopefully someone will be able to understand what I am looking to achieve and will be able to suggest a solution.
I would like to do a mass paste of this formula: =VLOOKUP(A23,A2:C9, 2)
With the only variable that changes along the way (A24, then A25, etc). But as I paste, (A2:C9), the table array, keeps changing as well, to A3:C10, A4:C11, etc.
How can I prevent this and only have A23 change as I paste?
I have a list of 2000 hyperlinks (http) that I need to download, I'm working on a corporate network so I can't just use a mass download client. Cany anyone give me some guidance creating a macro to save the target of the hyperlink as a file then move on to the next hyperlink until the list is exhausted.
=NORM.DIST(-0.7525,0,1,FALSE)=0.30057
I want to do inverse 0.30057 To get -0.7525 ,NORM.INV(0.30057,0,1) This function returns only cumulative distribution .
I am working on combining several Excel spreadsheets and a report from a different program into one master Excel report. I have a macro built so the report can be compiled efficiently every quarter. However, I have one obstacle to overcome before I reach report programming bliss: The other program's report does not use a date format that Excel can interpret as a date. Excel interprets it as a value.
Specifically, the other program outputs September 3, 2009 as 9032009. It is crucial that the report be arranged in chronological order (time is a separate field). Does anybody know if there is a way to mass convert a bunch of values that look like 9032009 into 9-03-2009?
I have a workbook with lots of analytic data from my company which I want to share with other people not associated with the company. Obviously I can't share private data so I'm wondering if theres a quick way to modify all the data I have with 'fake numbers' which means I could share it. With fake numbers no privacy rules will be breached etc.
Is there a quick way to randomly change all numbers by +/- 10-20%?
Lets say i have 2 columns A and B as shown below. I want to have a userform that has a list that shows the uniques in column A ie. 1,2,3 and a drop down next to each one that shows my options yes, no, maybe. If the user selects yes for 1 and submits the form i want all 1's in column a to have yes in column B.
A B
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
1 Yes
2 No
3 Maybe
I am trying to separate a huge group of phone numbers from one cell into individual cells in a column. the numbers are currently separated by a semicolon. Is there an easy way to perform this action?
View 5 Replies View RelatedI have a large worksheet with a unit name column, category, brigade, etc.
Everytime I get the data, I have to make the same changes. Is it possible for me to automate the process so that for example if the name column contains 'CID' or 'AMC MGD' delete those rows, if it contains 'ENG' change the brigade column for that row to '92ENG' etc.?
I'm trying to make a BMI (Body Mass Index) calculator. I've attempted it already but to no success. My first mistake was to read the values from cells in the spreadsheet which made things more complex than needed. So I started a new macro using a form instead for all data entry. I'm running into multiple problems with this
-Ask the user which method they prefer (see below formulas).
-Input the result into a certain cell.
English BMI Formula
BMI = ( Weight in Pounds / ( Height in inches ) x ( Height in inches ) ) x 703
or
Metric BMI Formula
BMI = ( Weight in Kilograms / ( Height in Meters ) x ( Height in Meters ) )
I have an accounts spreadsheet that I copy and paste customers names and addies into from the website back end sales information.
I do not copy e-mail addresses.
I have a mailto: with an e-mail address appear in the file in lots of places, it seems I delete it from some cells and it appears in others, my file is infested with the things now.
I can delete one by one, but this would take me weeks any ideas of how I can ctrl a select all cells and mass delete these things.
I am face with making a brand new accounts file which is a lot of work.
I have around 200 files, all named from 001.xls to 200.xls, all containing the same format, the same number of colums, but a different number of lines.
Now I have the great task to actually open all these files and copy each files data into one file and start analyzing it. Well, since I personally have no interest to open, copy and paste 200 hundred times, I am wondering if there is any faster way to let excel automatically copy the data into this master file?
I am thinking about somehing like *='001.xls'Sheet2!B2:E20*
One thing is that each file never has more than 20 entries, thus i would only need excel to import the from line 2 to 20 from a certain file and then do same thing all over again with the next file.
I am trying to do a mass import of several thousand excel files into one file. The headers of all the files are the same, but the data inside each file is different. I thought about using Access but it wants me to import each individual one.
I have zero experience in writing a Macro
trying to insert rows into certain sections of a large data set.
Currently, there are over 25k rows of data and I need to insert individual blank rows between certain lines. The process is too daunting to try and do it individually (CTRL+Click row number) so I need to find a way to do it based on a specific number in one of the columns.
I can insert individual rows after filtering the data to only show the rows that need blank sets under them
I've included a sample to illustrate my point. There needs to be a blank row before every row with a "1" in the "count" column.
Data Sample.xls
I'm trying to convert some data which excel is reading as text into numbers for example I have:
[Code] ......
All the numbers I've imported from an app analytics company into excel which are greater than 1.0 appear as numbers (to the right side of the cell) and all the numbers less than 1.0 appear as text (to the left side of the cell). I want all the numbers appearing as text to appear as numbers so I can multiply them with other cells.
I've clicked format cells - number but this has no effect. I click format cells -protection - unlocked but this has no effect.
I have thousands of rows with all numbers less than 1.0 as text instead of numbers. What's the easiest way to mass convert them all to numbers seeing as the above options haven't worked.
I don't want to have to click through thousands of rows to edit it manually...