I made a macro that will name the current sheet whatever text is found in cell B2 on that sheet. This works great unless that sheet name already exists.
If the sheet name already exists, is there a way to rename it as Cell B2 plus the number 2 after the title so it doesn't cause an error?
Example:
"Smith, Bob" is a sheet name, so the macro would make the new sheet name "Smith, Bob 2"
Alternatively, how could I give a pop-up telling about the error and deleting the current sheet instead of renaming it with a "2"?
Eample:
"Sorry, 'Smith, Bob' already exists. Deleting new sheet." as a popup and have it delete the current sheet.
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 would like to hyperlink the names in sheet 1 to the same names in sheet two. Is there a way I could do this using a formula rather that do each one by one?
I have an excel sheet with about 500 words in 500 cells that I have generated automatically. Separately I have a 'blacklist' of words I have compiled manually that I don't want to appear in the excel list.
How do I ensure that any 'blacklisted' word is deleted from the excel files. Is there any other way than doing it manually?
I've a list (Taken from this website; [URL] of 4900 rows with each row have 4 numbers if I want to add or insert one number( from 1 to 50 ) to each row with no duplicate to make it 5 numbers in each row? Is there a formula or Vb code to do this?
To my calculation Numbers (1:50) have to be use 98 times;(50*98=4900) since each numbers in the list used 392 times(392/ 49 = 8)since there is no duplication when adding numbers (1:50) to rows each numbers must pair with evry other number in that list 8 times.at the moment with out adding the 5th number each number pairs with any other number exactly 24 times but adding process finish it should be 32 (24+8).
I have a spreadsheet that grows rapidly each day of addresses I service. Duplicates are not welcome. When the spreadsheet was smaller, I would type in an address and if it was a duplicate, Excel would auto fill the cell with the duplicate data. Now that the rows are over 2000, I wanted to see if this primitive method for weeding out duplicates was still working, however when I type the first address I have in cell A1, it does not auto fill in A2001, even though it is a duplicate.
Is there any way to have on-the-spot detection of duplicates sans macro?
I have workbook that has several sheets within the workbook that are set up identical. Each of the sheets in the workbook are for a specific company.
As of right now I have been adding a sheet to the workbook that is an overview for what is in each sheet (the individual companies). Currently I am doing the formatting of the heading and column names manually and I pull the data from each sheet with a VLookup. I have been trying to enhance my VBA skills with coding something that will fill in the appropriate cells from worksheet to worksheet.
What I am trying to do is to populate an overview sheet with cells C24, C25, and B36 being static on each row per sheet. Then each row will be populated with cells C(36, 59, 70, 81), D(36, 59, 70, 81), F(36, 59, 70, 81), G, and H(36, 59, 70, 81). The overview sheet will have the diagram below in a ru
I have two sheets, which has data in columns A-J (10) on both sheets and but (sheet2) will have more data records than (Sheet1). I would like to be able to run a vba macro, which can find duplicate rows between the two sheets, by matching up only 8 columns, excluding column's E and G between the two sheets and showing the output duplicates in both sheets in green highlight.
I have attached my sample data for further reference. [URL] .....
The sample file, shows data in sheet1 and sheet2, and the desired output should be sheet1 and sheet2, highlighting (2,3,4,5,6,7,9,19,11,13) rows green in both sheets.
I'm using excel 2007 and windows vista. I have 2 cells, A1 contains (01,) and B1 contains (01, 08). I want the result in cell C1 to be (01, 08) by calling function trim_sort($A1&$B1). The code gives me an error "invalid qualifier" for s.length & more.
I have a worksheet which J3:NJ3 lists the dates for the calendar year. (Example - J3 = 1/1 , K3 = 1/2 , etc. )
A3:C3 are my headers for project information (project name, manager,etc)
In D3 I will be entering employee names. in E3 I will be entering Project start dates, and in H3 will be end dates. These dates are selected via validation lists of the dates listed in J3:NJ3, and it is set to auto fill the adjacent date range with an "X" after the start and end dates have been selected.
Now I also have another worksheet which is a pivot table that will auto update upon any workbook change, and will show each employee and all of the dates in which they are scheduled to work. I am using conditional formatting on this sheet to highlight any dates that the selected employee is double booked on two or more projects.
I want to keep this second worksheet as is, but to avoid having to flip flop back and forth between tabs to find out of someone is double booked, I would like to add a column (I) which will either say "Available" or "Double Booked" . Is there a formula that I can use for this? I thought about Vlookup, but I don't believe I know of a way to write a Vlookup formula that can check a dynamic range of entered dates. I thought perhaps a nested Vlookup/Hlookup, but not quite sure if that will work either.
each month i have to clens a sheet that shows customer name and then the figures per month.
this is pulled from a system that allows for a new entry each time a variation on customer name is used, I.E
Joe Blogs LTD Joe Blogs Limited Joe Blogs ltd.
would all come through as new entries. then i have to manually consolidate the figures from each month. the spreadsheet has over 5000 rows, and maybe only 15% are duplicated. what i want is to be able to select the column (that will be sorted a-z) and highlight cells that have a match for the first 4 or 5 charicters.
once i have identified duplicates i then have to merge the data into one row. is there an easy way to do this? it is monthly, comparing 2008 and 2009, so by december there will be 24 columns + the company name that need to be clensed.
How do I compare 2 columns and then flag where there is duplication? I want to compare Column B against column C and then flag everywhere there is a match. In this case, Column B may have several thousands of entries (inclusive off all entries found in Column C) and I want to have Excel look through the thousands of entries in Column B and then just place an "x" where there was a match to the values found In Column C. For example:
A B C D E F (B+C-D-E) Date Opening Stock Purchase Sales Consumption Closing Stock 17/7/2012 10000 5000 1000 2000 12000 17/7/2012 12000 2000 2000 1000 11000 18/7/2012 11000 0 1000 5000 5000 18/7/2012 5000 10000 3000 0 12000
Note : I want the above result as shown below datewise but no duplication of date but summing up of column C,D and E which is in above.Pls note that this is accounting question and also plz note that Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
Results :
A B C D E F (B+C-D-E) Date Opening Stock Purchase Sales Consumption Closing Stock 17/7/2012 10000 7000 3000 3000 11000 18/7/2012 11000 10000 4000 5000 12000 (B=F)
if I could get the formula in excel ,since this is the sample data as I do have a numerous & huge data like shown above datewise where I need to add C ,D and E but Opening stock(column B) from next date onwards must be equal to previous date closing stock(Column F).
I have an excel sheet which at the moment takes it name from a cell on that sheet, but what I would like to reverse if possible. How can I get the sheet to name itself from a cell. I'm sure I've been on a web page in the past with this info on it, does anyone have the link?
I’ve had a look through the forum and on some of the guides/FAQ but thanks to my ineptitude I have been unable to adapt any of the examples to my specific situation.
What I’m after is this: I need to create a summary sheet for a work book with a variable number of tabs. All I need the summary sheet to do is to make a table of the name of the tab and then cell A17. How would I go about doing this and is it possible to save this macro to my machine/all workbooks instead of just the one?
In Column A row 4 of sheet1 I have an account name, for example Sonic, and I have a sheet named the account name, Sonic. In Column B row 4, of sheet1, I would like to return the value in Column B row 3 in sheet named Sonic, or the account named sheet.
I have about 50 different account names along Column A in sheet1, and the same account name sheets opened in the same workbook. Is there a good formula or vba code that will work for all the values in Column A of sheet1 to look for the same named sheet and return the values off of that? Note: on the account sheets in Column A it says ‘Account’ for all of them.. this wasn’t a mistype
I have got a workbook with about 200 sheets... Sheet1 > Sheet200
i need to delete about 100 sheets... sheet100 > sheet200
i then need to add the sheets back in but when i do the sheet numbers start from Sheet201... how do i get it to start from Sheet101 again or can i use some vba to change them later?
I wrote the following macro to create hyperlinked Table of contents on the first sheet of a workbook. It works great unless the sheet name has a space in it, i.e. "new sheet" as opposed to "newsheet". Any way to get it to work for either/ or?
Originally Posted by Code
Sub Hyperlink() ' ' Hyperlink Macro ' Macro recorded 2/26/2007 by bpillsbu '
' Dim ShName As String Dim ShCt, N As Integer Dim Sht As Worksheet
ShCt = ThisWorkbook.Worksheets.Count Range("A5").Select N = 1
How can I obtain the sheet-level name through VBA? I'm not referring to the sheet (tab) name but the name as defined though Insert/Define or Insert/Create, etc .
I know the way to get the cell level name goes something like this:
MsgBox Sheet1.Range("A1").Name.Name
But i don't know the correct syntax for sheet level.
I have searched the forum and I have seem quite a few referrences to this question but can't find exactly what I'm looking for. I'm using the first sheet in the worksbook as a splash screen which has lots of button macros on there. I want to add one more which will make a list on this sheet from cell b25 down with all the sheet names in the workbook which are also clickable hyperlinks. Does anyone have any idea how to do this, so far I can make a list of the names..
Sub Index() Dim ws As Worksheet Dim I As Long
I = 25 For Each ws In Worksheets If ws.Name "Actions" And ws.Name "summary" And ws.Name "Archive" Then Worksheets("Summary").Range("B" & I) = ws.Name I = I + 1 End If Next ws
End Sub
But have no idea how to make these names clickable which will make the relevant sheet active. If it helps the sheet name is also in cell A2 of every sheet.
I have done a formula for Abbott as shown below however I actually have about 50 sheets with names. I have the names listed in column A. Is there a formula that instead of typing Abbott as shown I can use the cell in column A to generate a code ?
