I've currently got a problem with renaming the ActiveSheet when multiple worksheets are present. The code below, utilizes existing fields on the worksheet and a concat with static text. The code works great if only one sheet is present. However, as soon as the second sheet is inserted in the workbook, I'm now receiving a 1004 error (application-defined or object-defined error).
Code:
Private Sub updateWorksheetName_Click()
Dim fNumber
Dim pCheckNumber
I mean renaming tabs on the sheets, the tabs at the moment are called Output 1 (*****) instead of Sheet 1 etc.. and i would like that changed to contents in cell A9. If possible only the Output 1 will be removed and it will change to something similar "X-Ray (5E4TT)"
i have a workbook with worksheets named Output 1 (*****)
(the stars being a five digit/letter code - the only thing that changes on the workbook)
now the problem is, i pull of reports and sometimes worksheets can be up to one hundred.. now in cell a9 is the name of the report. I have found vba code to rename sheets to cell contents **extract below**
but any chance of renaming contents of cell a9 to a worksheet named Output 1 (*****) etc.. and if the contents of cell a9 can be trimmed so only certain part of a lengthy title
Sub RenameTabs()
For i = 1 To Sheets.Count If Worksheets(i).Range("A1").Value <> "" Then Sheets(i).Name = Worksheets(i).Range("A1").Value End If Next
I've undertaken a project for work with my very very limited knowledge of Excel VB.
What I am trying to do is automate the process of creating a new worksheet in a "Master" Workbook, rename and save that worksheet with the date it was created.
Each worksheet will hold a list of dates for staff members who have attended or attempted courses for that week and the information will come in via email in separate pre-created worksheet templates. The consolidated weekly data will ultimately end up in an MS Access db (but needs to be sorted and validated before upload).
Hope i can get some help here as my vba experience is extremely limited. I'm trying to run a macro from a spreadsheet that will go down a list of file names that i have entered in a worksheet where the macro resides and open those spreadsheets and rename the worksheets in each file according to a list of names that i have entered in the 10 columns next to the file name. It's easier to explain with the layout of my macro spreadsheet: ....
I'm attempting to add a variable number of worksheets (dependant on the number of entries in an existing list), then rename those sheets to the name of the entries in that list. i.e.
LIST A B C With the list above, i'd want 3 new sheets added, one named 'A', one named 'B' and one named 'C'.
The trouble i'm having is that because the number of entries/sheets varies, i don't want to refer to 'Sheet1' 'Sheet2' etc. to rename them.
***Additional Info*** the first record in the list is always "W4" on sheet "Data"
Below is where i've got to so far (however, i'm pretty new to this so it's almost certainly not all that efficient!)
I need to rename a large number of .pdf files. I have a list of the current file names in column A and the desired file names in column B.
Data in excel sheet1:
Current NameDesired Name AAA-BBB-001.PDFAAA-BBB-001-Description.PDF AAA-BBB-002.PDFAAA-BBB-002-Description.PDF AAA-BBB-003.PDFAAA-BBB-003-Description.PDF AAA-BBB-004.PDFAAA-BBB-004-Description.PDF AAA-BBB-005.PDFAAA-BBB-005-Description.PDF
I keep them in the destination folder below:
C:UsersmeDesktopA
I prefer to use an excel macro since I can't install any additional software on my work computer.
I am a teacher who has created a gradebook in Excel. Each class has a student template sheet, individual sheets for each student in the class (maximum 26 students/class) which has the structure of the template and some cell referencing to the template, and a couple of mark-entry sheets and a sheet that shows the progress of all students in the class.
A number of teachers in the school have been using this gradebook and I am constantly looking for ways to make it more user-friendly. Currently, when a new course starts, a teacher opens a blank copy of the file (which has all of the sheets I mentioned above present). To set up a class, the teacher enters the students' names once on the mark entry sheet (called the "Binder Sheet"), and then changes the individual student tabs (right click...rename...) to reflect the names of their students.
I am wondering if there is a way to have the individual sheet tabs (Student 1, Student 2...Student 26) update automatically to match the names that the teacher enters on the Binder Sheet in cells A4:A29.
I would like to be able to embed the update procedure/script into the blank template so that it is user friendly for others.
I would like to rename multiple tabs (12 in all) on a spreadsheet by month only. I highlighted all tabs and then performed a cut and past from the previous year spreadsheet, but when the paste was complete the tab names were missing. I need January through December on the 12 tabs. Does anyone know of a shorter process than renaming each tab individually? I have called several people and asked the same question and they are curious if there is a way to do this also and asked that I let all of the know what I find out, so you would be helping quite a few people in several different companies (If that gives you happy thought, then good for all of us ).
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
All I want to do is copy the active worksheet to then end of another workbook. I have alread set my variables, here is the line that is causing me a problem: ....
I have a WorkBook with each sheet being a calendar month. I want to set the ActiveSheet as the current month so each time I open the WorkBook it opens to the current month WorkSheet not the last WorkSheet I was viewing.
Got a wee problem with some syntax for a copy function. At the moment the code i have copies the range and pastes to another range which is defined by a cell number. But i need it to paste special (values only).
I have looked at previous posts and found some information about ActiveSheet.Paste errors, which appear to be related to having activate, select and paste functions in the wrong order.
I am still getting an error at the ActiveSheet.Paste point of my code which I cannot seem to overcome, although this same code works on another macro doing exactly the same action...
I wrote a macro on mac office 2004 which work perfectly on my pc but not on my mac. The macro is very simple
Sub Delete_Next_Sheet() ActiveSheet.Next.Delete End Sub
It just delete the next sheet after the activesheet. However on the mac, it doesn´t recognize the Next. It display an alert mesage "Invalid or unqualified reference".
I was woundering if there is another way (as simple and as fast if posible) to get a macro avoiding loop and sheet.count to delete the next sheet after the activesheet.
I am trying to write a small sub which will make do an "AutoFit" for all columns of the Active Sheet. For example, the code below does an "AutoFit" for columns A:J of a sheet. How can I make it do the same for all columns (not only for columns A:J) of the ActiveSheet?
I am trying to use ActiveSheet.Name formula to rename the active sheet. It is only working part of the time and I can't figure out why. I do know, however, when it is going to work and when it is not going to work. If cell A2 contains two words, it does not work. If cell A2 contains only one word, it does work. Here is the code. The highlighted part in blue is the code that names the sheet. I have attached sample spreadsheet if needed.
I have a ComboBox. When a user selects an item from the ComboBox and clicks a command button (Add Button) the selection that the user selected writes to a row in the active worksheet and then resets the ComboBox allowing the user to make another choice after clicking the "Add" command button. I am trying to remember how to write the code for row offset so that when the user makes their next selection from the ComboBox and clicks the "Add" command button it puts it the selection in the next row of the active spreadsheet thus creating a running list of the users choices. Here is a line of the Private Sub cmbAdd_Click() ActiveSheet.Cells(2, 3) = cbxSeverity cbxSeverity.Value = "" End Sub
After the tcbxSeverity.Value ="" line I was testing trying to get to the next row by using something like this:
Private Sub cmbAdd_Click() ActiveSheet.Cells(2, 3) = cbxSeverity cbxSeverity.Value = "" ActiveCell.Offset(1,0).Select End Sub
I know that this code is incorrect but I cannot remember how to offset or check Rowsource to move to the next line.
I am working on transfering some code from 2003 to 2007. I have gotten almost everything to work, but am now having an issue with the way it places the pictures into my spreadsheet.
The code asks the user to select a folder containing .jpg files. It then places each of the .jpg photos onto a single spreadsheet. Each picture is suppose to be placed in a different cell. When I step through the code, the active cell changes correctly based on the offsets, but it always pastes each photo in cell B4. Through some research I have found others to see this problem with ActiveSheet.Pictures.Insert in 2007.
Following is the Private Sub BatchProcessThumb2x3() Msg = "Select a file containing the photos you want to insert." Directory = GetDirectory(Msg) If Directory = "" Then Exit Sub If Right(Directory, 1) "" Then Directory = Directory & ""
how to go about or refer me to how exactly you assign a closed workbook to be active. I know this sounds off and its probably the wrong way to word it so here's my problem. I am developing a macro in Visual Basic 2005 using VB. I have a connection string that looks like this:
What I need to do is connect to it and update which I have done successfully, then save it as 2006.xls. Is there some sort of m_sConn4.SaveAs or something of that nature.
Private Sub Workbook_Open() 'Application. ScreenUpdating = False ' Turns off screen updating Dim Filename As String Dim nFam As Integer Dim cRow As Integer Dim i As Integer ws As Worksheet
Filename = "O:BVRMusers_sharedJCIntraday ToolFamily List.xls" Workbooks.Open Filename 'Opens Family List
'Count the number of rows (Families) in Filename (Family List) Set ws = ActiveSheet With ws . Cells(1, 1).Select cRow = .UsedRange.Rows.Count End With End Sub
i cannot get ws to be set as the ActiveSheet. i receive a run time error 91 each time and cannot figure out what is wrong. i've used this code before under subform functions, but this is the first time i've placed it in the "ThisWorkbook" area.
I'm trying to "export" data from a static ws "order" (Sheet2) to a selected (active) worksheet. This will happen with 15 different (random) cells.
I got this far but the copying isn't happening from the correct sheet or going to the active sheet. The data in "G5" on "order" should go to the first unused row on the active sheet. What I got was backwards.
Once I get the first one to work I can finish the code for the other 14 cells. I appreciate getting pointed in the right direction
I have a workbook with 25 sheets containing metric information as part of a performance management model. Fundamental to this is the visual success or failure of each of these 25 sheets which I've highlighted by setting the worksheet tab colour accordingly. The code to achieve this is detailed below. This code is triggered by the Worksheet_Change event at the workbook level and works fine in single user mode. When the workbook is shared however, an error 1004 is generated.
Private Sub Worksheet_Change(ByVal Target As Range) For Each c In Range("PassFail") If c.Value = "Fail" Then ActiveSheet.Tab.ColorIndex = 3 ElseIf c.Value = "Pass" Then ActiveSheet.Tab.ColorIndex = 4 End If Next c End Sub
