Rename Embedded Active Chart Sheet Name
For example, a cell on the screen is blank. When the page is printed, the "blank cell" has printed text.
I checked "format cells" and it shows word wrap, merge cells, and left to right.
I was told to "un-merge" the cells. I did that and I still have the problem.
The color on all the cells is OK at a blue shade. When I look at print preview the cells are blank, but when the sheet is printed, the text shows.
Also, it looks like there are 2-3 cells inside the main cell.
I hope I have explained this properly, it is not easy to use the correct language to describe since I am not very good at using Excel.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Sheet Names As Embedded Chart Titles
I have a spreadsheet that has multiple sheets and each sheet has a graph, at oresent if i have to add a new sheet and graph i copy the last sheet, rename the tab then off we go, but i alway have to remember to also change the sheets graph title. is a mechanism so that if I change the Sheet Tab name then the chart will use the sheet tab as a chart title.
View Replies!
View Related
Macro Copy Of The Active Sheet And Then Rename The New Copied Sheet
I'm trying to create a copy of the active sheet and then rename the new copied sheet to what's in cell O4, which is a formula (see below) and then paste value cell O4 in B3 of the copied sheet. However, when I run this macro it doesn't seem to like the second line where I am renaming the sheet (run time error '1004'). "O4" =DATE(YEAR($B$3),MONTH($B$3)+1,DAY($B$3)) Sub NewMonth() ActiveSheet.Copy Before:=Sheets(Sheets.Count) ActiveSheet.Name = Range("O4").Value ActiveSheet.Range("O4").Copy ActiveSheet.Range("B3").PasteSpecial Paste:=xlPasteValues End Sub
View Replies!
View Related
Reference Chart By Name That Is Not On Active Sheet
I am trying to select cells, tables and charts by just knowing their name. Thus if possible I would like to only refer to a chart by its name i.e. not know what worksheet they are on. This seems to work for named ranges of cells/tables, but not charts (I can't get my named charts to appear in the "Define Name" dialog). I found this code that loops through charts on the activesheet and shows the names. However, I would much prefer not to have to specify the sheet or to loop through all the sheets trying to locate the one with the named chart. Public Sub GetEmbeddedCharts() Dim myChart As ChartObject Dim myCharts As ChartObjects Set myCharts = ActiveSheet.ChartObjects For Each myChart In myCharts Debug.Print myChart.Chart.Name Next End Sub
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Moving Embedded Chart Series Via Mouse
I have an embedded chart with a data series and a horizontal line that changes position (y-intercept) by values entered into a textbox. To make the process more user friendly, I would like to allow the user to select this horizontal line and reposition it inside the chart with the mouse. The new y-intercept of the line should then be displayable (textbox, cell, whatever). From what I have read on Peltier's site, I will definitely need to write a new class. Classes in VBA is not something I am particularly familiar with..
View Replies!
View Related
Hide Text Box On Embedded Chart
I have a workbook with two worksheets. On the second worksheet I have placed a chart, in which several shapes (text boxes) have been added manually. With the help of a TickBox in a UserForm on the first worksheet one can hide / unhide one of those shapes (on the second worksheet). By using the Macro recorder I finally came to this code fragment: Application.ScreenUpdating = False Worksheets("Second One").ChartObjects("Chart 41").Activate ActiveChart.Shapes("Text Box 4").Visible = False Worksheets("First One").Activate Application.ScreenUpdating = True Obviously another part of the code is used to make the shape visible again. roblem: The screen flickers. And I want to be able to do this without flickering. Searching Ozgrid revealed that the use of "Activate" makes the screen flicker. Hence, my question: How can I hide / unhide a shape (textbox) in a chart on another worksheet without of using "activate"? r: Any other way to hide / unhide a shape (textbox) in a chart on another worksheet without flicker?
View Replies!
View Related
Identify Correct Embedded Chart With VBA Macro
I have several charts embedded in a worksheet, and I would like to use the ChartObjects Method in VBA on some of them. That requires knowing the index number of the charts I want to work with. Where/how do I get that? (For example, in the VBA Project Explorer window the only listed objects are sheets. Is there an object explorer with a chart list?)
View Replies!
View Related
Runtime Error 1004 :: Cannot Rename A Sheet To The Same Name As Another Sheet
Why do I get the error: "Runtime error 1004: Cannot rename a sheet to the same name as another sheet, a reference object library, or a workbook referenced by Visual Basic"? And how do I fix it? I have a macros that someone else made (thank you) and I need to make the macros create anywhere from 5-125 sheets based on the information added in sheet 1. How can I do this whe it stops me after 5 or so with the error above.
View Replies!
View Related
Rename Sheets - But Not Every Sheet
I have 20 sheets in the workbook. 10 sheets are named YR1, YR2, etc. Once the user enters information on sheet "Assumptions" - names are setup and linked onto each YR page. So user enters CY10, which links to cell C5 on YR1 to show CY10. I would like a macro that renames just the YR1, YR2, ....sheets, to CY10, CY11, .... I do not want any of the other sheets to change names. (FYi - Using Excel 2003)
View Replies!
View Related
Automatically Rename Sheet Tabs
I 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?
View Replies!
View Related
Rename Sheet With ActiveSheet.Name Formula
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.
View Replies!
View Related
Copy Sheet Rename With A List
Sheet1 has the list of names (cells A2:A315). Sheet2 is hidden and has related formulas. Sheet7 is the sheet i want to copy What i want to do: 1) I want to copy sheet7 for each name on the list 2) Rename each sheet with the next name on the list 3) In each of the copied sheets in cell B1=newsheetname
View Replies!
View Related
Rename A Sheet Based On Cell Data
I'm looking for a solution that will rename the sheet to whatever is enterd into particular cell. Ie: cell A1 = "Joe Bloggs", then the sheet is renamed to Joe Bloggs Also, on the same sheet, is it possible to change the sheet tab colour based a data in a different cell? Ie: cell B1 = "Parts", then the sheet tab colour changes to pink.
View Replies!
View Related
Copy Sheet And Rename Based On Date
I have a spreadsheet that I enter daily totals into. The sheet is named by date. I take totals from a number of catagories from the prior day's sheet (ending totals) and enter them on the current sheet (beginning totals), then enter the current day's totals to wind up with new ending totals. I want to generate a new sheet in the same workbook based on the date of the prior sheet, copy my formatting, and copy the data from the old ending sheet totals to the new sheet beginning totals.
View Replies!
View Related
Macro - Using Sheet Template Need It To Auto-Rename
*Inserts an existing worksheet template and places it at the end (or after all) the existing worksheets. What I would like the macro to also do is: *Automatically rename the inserted worksheet (via my worksheet template) in sequence to the existing worksheets Example: So let's say I have 3 existing worksheets titled Sheet1, Sheet2, Sheet3 (that's basically the default naming Excel uses). But would when I insert a new worksheet (via my worksheet template) it is shown as Sheet. And if I insert another one, it would be Sheet (2). So basically it is showing as such: Sheet1, Sheet2, Sheet3, Sheet, Sheet (2) What I would like the macro to do is automatically rename those last 2 worksheets (and any subsequent worksheets thereafter) in sequential order. Basically displaying it as such: Sheet1, Sheet2, Sheet3, Sheet4, Sheet5...(and so on and so forth)
View Replies!
View Related
Rename Sheet Using Multiple Cell Values
The macro below renames the current sheet to the cell value of D4: Sub Rename_Sheet() With ActiveSheet .Name = Range("D4") End With End Sub What can i do to the code above so that it will rename the sheet based on 3 cell values instead on only 1 value? I want the end result to look like this with x's between the values: D4xD5xD6
View Replies!
View Related
Copy To Another Sheet, Rename And Paste Special Values
i would like to copy a sheet to another sheet, rename, copy and paste special values. but after the sheet is copied to another, the macro stops working...? Sheets("Proposal").Copy After:=Sheets("Proposal") 'rename... ActiveSheet. Name = "ProposalEmail" ActiveSheet.Copy ActiveSheet.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _ :=False, Transpose:=False
View Replies!
View Related
Click Button To Rename Sheet Tabs Based On Range
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).
View Replies!
View Related
Keep Hidden Sheet Made Visible As Active Sheet
I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'. Sub UnhideSheet1() Sheet2.Visible = True End Sub
View Replies!
View Related
Copy/Paste From Active Sheet To Another Sheet
I want to copy data from the current sheet, and past it in the "montly" sheet. 'i and j were defined above, with a Case statement Range(" Ai:Cj").Select Selection.Copy Sheets("monthly").Select ' I want the upper left cell of the pasted area to be A11 Range("A11").Select ActiveSheet.Paste I get an error saying that I should select one cell and then paste. But I thought that that is what I did...
View Replies!
View Related
Check Only Active Sheet
I have this macro that looks in all sheets in column A, except the invoice sheet. How do I alter it to only check the active sheet? Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name ("Invoice") Then On Error Resume Next With ws .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True With .Columns("A").CurrentRegion .Offset(2, 0).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible).Copy _ Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp)(2) End With .Cells.Rows.Hidden = False End With Err.Clear End If Next ws
View Replies!
View Related
Jump To Last Active Sheet
Is there a way to create a macro to jump to the last active sheet? For example, the last sheet I was on was Sheet1 and now I'm on Sheet20 and I want to jump back to Sheet1 without scrolling through the tabs at the bottom of the screen.
View Replies!
View Related
Return To The Last Active Sheet
I have a spreadsheet that is nearly 100 worksheets. I am new to VBA and would like to put a button on each tab to return me to the last active sheet. I've tried other codes I've found on here but with no luck. I'm not sure if i'm posting them to the right area within VBA.
View Replies!
View Related
Macro - Rename Only & Sheet... & Tabs With Last 8 Characters From Cell
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.
View Replies!
View Related
Sorting A Very Hidden Sheet That Is Not Active
I have tried to use absolute references (perhaps I didn't do it right) but it didn't work. Below is the code that does work but I need to first make the sheet visible then activate it. Sub Row_Sort(tmpsheet As String) ' Sort the first 4 columns of the tmpsheet sheet Dim tmprng As Range Dim OldActiveSheet As Worksheet ' Activate the desired workbook (in this case Linewkbk) Workbooks(LineWkbk.Name).Activate ' Save old active sheet Set OldActiveSheet = ActiveSheet.............
View Replies!
View Related
How To Run Macro From Active Sheet
I have written the below macro to copy data from a sheet called "Fixit Example 1" to a sheet called "Fixit Summary Example". It works fine, but I'd like to be able to run it from any "Fixit Example XX" worksheet. As written, the data is always copied from "Fixit Example 1" regardless of what worksheet I have open. I tried replacing Windows("Fixit Example 1.xls").Activate with Windows(Active.Worksheet).Activate but got an error "Run time error 424. Object required." Sub MoveData() ' ' MoveData Macro ' Macro recorded 18/03/2009
View Replies!
View Related
Vlookup Code For Non Active Sheet
I wrote this code today and it works - sometimes. Really it only seems to work when I have one of the sheets(zvur013) is in focus (ie, it was the last sheet to have been tabbed to). I dont understand what I'm missing, please help. Sub lookforitems() Dim items() Dim groupnums() Application. ScreenUpdating = False Set allitems = Workbooks("zvur013.dbf").Sheets("zvur013") Set allgnums = Workbooks("Groupnumbers.xls").Sheets("sheet1") Countgroups = Application.WorksheetFunction. CountA(allgnums.Columns(1)) countitems = Application.WorksheetFunction.CountA(allgnums.Rows(1)) ........................... allgnums.Cells(j + 1, i + 2).Value = x Debug.Print x Next j Next i End Sub
View Replies!
View Related
Find Active Sheet In A Workbook
I am creating a macro that will open another workbook and take all the contents off a page in it and paste the contents to itself. The problem is that sometimes those workbooks will only have one page and it will have the data I need, but sometimes there will be two or three pages in the workbook, but again, with only one sheet that has data on it. How can I have the macro find the one page with data on it?
View Replies!
View Related
Save Active Sheet To Another Workbook
Copy the active sheet that is open in a work book and save it to another file with the date in the name? For example Report_11-03-08.xls The only problem I see with this. How could I also make it append a letter to report if the file already exits Example saved as Report1_11-03-08.xls. Then just continue incrementing. Sub CopySave() Application.ScreenUpdating = False ActiveSheet.Copy Application.DisplayAlerts = False ActiveSheet.SaveAs Filename:=ThisWorkbook.Path & "/" & "Report_" & Format(Date, "mm_dd_yy") & ".xls" ActiveWorkbook.Close Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
View Replies!
View Related
Find Active Cell Value On Another Sheet
I am having problems getting a VBA search function to work... this is the code I am currently using with very little success: Sub FindApp() Selection.Copy Sheets("Applications").Select Cells.Find(What:="ADT32109", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate End Sub What I am trying to achieve is to copy the contents of the active cell in the first sheet and find it within another sheet but I am unable to get the What:= section of the code to take the value of the copied cell.
View Replies!
View Related
Sort Range On Non Active Sheet
I'm trying to sort data on sheet1 and sheet2. I'm running all macros from sheet one and I need it to stay active all of the time. I'm using the following code to sort sheet2. When I sort the column on sheet2 with the code, I can not get back to sheet one without an error. Worksheets("Sheet2").Activate Columns("a:a").Select Selection.sort Key1:= Range("a1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I've tried Worksheets("Sheet1").Activate Columns("a:a").Select..............
View Replies!
View Related
Disable Right Click When A Sheet Is Active
I need to display Excel Sheet in a Browser and I want to disable the Right Click and Menu of Excel(File, Edit). So in the method Workbook_Open i coded following Private Sub Workbook_Open() Application. CommandBars("Worksheet Menu Bar").Enabled = False Application.CommandBars(" Cell").Enabled = False Application.CommandBars("Sheet").Enabled = False Application.CommandBars("Ply").Enabled = False Application.CommandBars("Row").Enabled = False Application.CommandBars("Column").Enabled = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("Worksheet Menu Bar").Enabled = True Application.CommandBars("Cell").Enabled = True Application.CommandBars("Sheet").Enabled = True Application.CommandBars("Ply").Enabled = True Application.CommandBars("Row").Enabled = True Application.CommandBars("Column").Enabled = True End Sub It works fine, but if I open Excel application before viewing this HTML page(which contains XLS) and then the option are also disabled in Stand alone Excel application. I want to disable option when for this Sheet which is opened.
View Replies!
View Related
UDF #VALUE! Error On Non-Active Sheet
I have data that is laid out as follows: Date, amount1, amount2 and a value in $ There are many rows, and there will be more than one row for each date. see attached worksheet for an example. I want to create a table that will provide me totals of the amounts and the value for each date. I have managed to do this, using subtotals, but this gets quite slow, and in any case I want to be able to reference each of the subtotals with another sheet, so I tried to use pivot tables. I managed to do the above by using three pivot tables Date/Amount1 then Date/Amount2 and finally Date/Value, and hiding the extra two date columns. However my next table of data contains 10,000 rows and over 20 columns, so I am wondering if the above can be done within one pivot table. I know very little about pivot tables, and when I try to drop a second data item in, it always puts it below the first rather than in a separate column, and I get a table looking like the one in the attached worksheet. Am I doing something very basic, wrong? Is it possible to get a single pivot table, to look like my first summary chart in the attached worksheet. If I can do it with three columns, then I should be able to extend it to my much larger table of data.
View Replies!
View Related
|
TRACKER
