Can I get a macro to insert a new worksheet each time it runs and rename it to a value held in a cell on another tab eg Sheet1, cell A1 - this value will change each time the macro runs so there wont be any duplicated tab names ?
The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.
I can't explain why I have to press debug twice for in order for the code to work properly.
Code: Sub renameColumns() With Sheets("byPosition")
I renamed a sheet and for some odd reason excel added .xls] before the name of the sheet. So the name was now .xls]Sheet 1. I tried to rename the sheet again to get rig of the .xls], but the program didn't allow it and said that I "entered an invalid name". Now when I'm trying to make links to cells on this sheet, excel says that my "formula contains ans ivalid external reference to a worksheet". The worksheet is now unusable and copying the sheet and updating all the links on other worksheets is a lot of work.
I have renamed worksheets according to a cell value in the same worksheet, but now I need to rename the worksheet according to a cell value in another worksheet.
Here is the code I tried:
Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sheets("Set-Up Page").Range("B2").Value "" Then 'rename the worksheet to the contents of cell B2 Sh.Name = Sheets("Set-up Page").Range("B2").Value End If
I have pasted that code into the worksheet that needs to be renamed, but it doesn't work. I have also tried:
Code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Address = Sheets("Set-up Page").Range("B2") Then Sh.Name = Target End Sub
I like the first one because it checks for a blank value. There will be blank values depending on how many worksheets each teacher needs, so it will just rename the ones that have values filled in. Each worksheet will have the code referencing to a different cell on the Set-Up Page worksheet.
I have what should be a simple question. I have a button in a form that needs to duplicate a sheet and rename it. It will duplicate but i am having problems with the renaming. The name of the sheet is coming from a userform text box. Here is the code i am trying.
I currently download a report and it has a different worksheet name everytime. Is there any way using a macro to change the worksheet name? (My macro extracts data from worksheet a and copies into worksheet b) Hope I've provided enough info.
When I press "commandbutton1", I need "userform1" to view and insert a name in the text box. When I press OK in "userform1" I need a macro to copy worksheet "s0" into a new worksheet and rename this worksheet with the name typeed in "userform1". Also I need to view the name of all worksheet copied using "userform1" in column H in worksheet final.
I need to have many users press a button within the excel sheet that duplicates the form from the current page, opening a new tab (copying the form) that also prompts to input a specific name e.g. "Enter date and day or night shift" to name the tab itself.
I have just worked out how to put a Command Button in and used the above code to create the new page assigning the code to it.
I need the "my sheet" to be prompted and customisable.
Dim fexport1 As String ' variables for the exported file Dim fexport2 As String Dim wb1 As String 'variables to change between the opened workbooks Dim wb2 As String strTemp = "Please Choose The Exported File" MsgBox strTemp fexport1 = Application.GetOpenFilename("Excel Files (*.xls), *.xls)") If InStr(fexport1, "False") = 0 Then Workbooks.Open fexport1 wb1 = ActiveWorkbook.Name
Else strTemp = "Operation Canceled" End If
The problem is that Crystal Reports generates the file with an invalid worksheet name (it contains a backslash) and I do not have access to modify the Crystal Report. Althought the file can be automatically repaired by Excel when manually opened, the macro chokes and the "Application.DisplayAlerts = False" does not fix it. The only solution I can think of is renaming the worksheet without opening the file.
I have the following code in my vb app. It creates the new worksheet, gives it the corect name, copies all of the existing data from an existing worksheet and pastes it in the new worksheet, but I can not get it to refresh the formulas. When I look at the formulas in the new sheet they reference back to the MasterSheet worksheet(which is my template I copy and paste from when making a new worksheet)
I asked for a macro that copy an worksheet and rename it using a userfom! I received the xls file attached to this mail ! In this xls when I click "Click me to copy s0 sheet and rename it" from final sheet, a userform appear, I type a name there and when I press ok I will have a copy of worksheet "s0" with the name typed in the userform. If I click on "Click me to copy s0 sheet and rename it" I can create as many copies as I need.
But if I hide worksheet "s0" (using Format /Sheet/Hide) the macro will not work. I modified the code from module 1 adding the red
I am having trouble renaming an active sheet from a Macro I stored in the personal.xls file. I want the active sheet to be renamed to "Data_Source" and then the rest of the code can kick in. Instead of renaming the current worksheet it creates a new one.
The problem I'm having is that the first sheet to be copied is the "Old Data" worksheet and the copy is renamed Oct-01-2009. After that the correct Worksheet is copied and renamed Oct-02-2009 and so on.
I can't figure out why it's copying the "Old Data" worksheet first.
I have about 200 excel workbooks in one folder, each with a different workbook name, and each workbook has one sheet, but that sheet has a different name in each workbook. I want to make the sheet name the same (sheet 1) in all the workbooks.
I am working with a workbook that has data automatically entered each time a new child is enrolled to the program. The child's data is automatically entered to the sheet named "Intake". What I need to do now is rename that sheet using the child's name as the sheet name, but also keep the sheet named "Intake" for the next entry. I would then like to sort the sheets alphabetically but leaving the "Intake" sheet either as the first sheet or the last sheet. I have attached an example of the workbook I am working with.
I'd like to split up the rows in a worksheet based on the values in one of the columns. Also, I'd like the sheets to be named after the values in the column. I have attached example excel sheets to explain this better. I think the vlookup and Sheets.Add and ActiveSheet.Name formulas can be used but I'm not quite sure how to put them together. The actual data has about 20 columns and about 500 rows.
I am building a workbook. The data we are tracking is all in one xml file, which i have mapped to 3 different worksheets(customers, invoices, inventory). On the customers and inventory sheet I have an interface for creating a new customer/product/invoice. I used the macro recorder to make the macro's to do these three things, but could use help on a couple of other functions as I don't actually know VBA. I need to be able to automate editing of existing records by having a button to press on the each sheet that will open an input box that asks the user which invoice number, customer number or product number he would like to edit, then copy that record to the interface for editing, then another macro to replace the existing data with the newly edited data. It would also be wonderful if when creating or editing an invoice it could create a copy of the interface worksheet and rename it as the invoice number. The first row of each datasheet is blank, and each of the interfaces have formula's for importing the information copied to row 1 from the data tables. the second row of each datasheet contains formulas for importing data from the interface. Any help would be terrific...I know you guys are excel gods and I will forever be in your debt if you can help me out....thanks in advance, I'll be studying my butt off until I figure this out