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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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
Copy Chart To Different Sheet & Change Reference
I am copying the entire contents of a worksheet onto a blank worksheet multiple times. The problem occurs with the charts which need updating once copied to reference the new worksheet name. I have tried stepping through each chart and then each SeriesCollection but it seems to fall over part way through.
View Replies!
View Related
Reference To Active Row
I want to arrange a dataset in blocks of 8 lines and for this I need to refer to the actual row of the active cell. Below code does not work - the "Row" does not change as the active cell moves down through the rows
View Replies!
View Related
Reference Active Cell
I've got 3 columns say A,B,C. Column C (row1) = Column A (row1) * Column B (row1). I'm trying to apply this formula to all the cells in Column C. I've tried typing the following formula in all of column C. =OFFSET(ActiveCell,0,-1) * OFFSET(ActiveCell,0,-2) Obviously the ActiveCell reference doesn't work. This would be simple in VBA but here on excel how would I reference to the cell itself where the formula is contained?
View Replies!
View Related
Active Cell As Reference
I would like to fill data in table on another worksheet, lets call it Sheet2 that starts with the activecell from Sheet1 and then use offsets. The activecell changes, but the remaining offsets for rows and cells always remain the same. I am pretty sure , I can figure it out this out using VBA, but is there a formula way to do this? Either looking at the activecell from Sheet1 as a start or using formatting like bold the start cell?
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
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
Reference Last Chart Series
I am trying to create a chart that will allow the user to add/remove series to compare with each other. When adding a series, Excel gives it a name (e.g Series1) the macro is then set to rename it to a particular cell, however the name Excel gives the series varies depending on the order in which the series are added. ActiveChart.SeriesCollection(SeriesCollection.Count) instead of ActiveChart.SeriesCollection(1) would work collect the last series added, which is what I want, but when I apply this I get an run-time '424' error.
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
Format Chart Bars Based On Cell Reference
I'm trying to get the bars of a chart to change colour depending on the selection from a drop down in a cell. The drop down is validated to be one of four options which changes the figures displayed in the graph using index matching.I would really like the colour to change to represent what the option being selected is. I used some VBA code before for changing the cell colour depending on the selection made to get over the 3 condition limit. Could this be easily edited to change a bar graph colour? All of the references I can see on line refer to changing the colours of bars based on their value, I don't want to do this, more an independant cell value! My previously used code is below: Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Integer If Not Intersect(Target, Range("d9:d129")) Is Nothing Then Select Case Target.Cells(1) Case "Commercial Final State" iColor = 49 Case "Commercial Interim" iColor = 31
View Replies!
View Related
Gradient Colorize Chart Points Based On Cell Reference
Conditional formatting is lacking. I need to colorize each data point on the graph according to a characteristic cell (not an x-axis or y-axis variable). I have done something similar by simply graphing a group of points as a series and changing the series color. I'm looking for a more graceful solution. From the example picture attached (i would attach a workbook, but the data is sensitive), a point is charted based on two variables and colorization is attempted based on the third variable. i would find the range of the third value, assign a gradient based on the spread, and colorize each point. My VBA syntax is lacking for chart functions
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
Cross Reference Information From One Sheet With Another Sheet
I have a spreadsheet that shows a large number of folks we had working in a particular division from Jan. 1st until now, 1800 +. Some of the folks are on the sheet twice due to having more than one role. The sheet lists last name, first name, skill description, pay rate, company they worked at...etc. Most of the folks on the list are not currently working but some are. I have another spread sheet that lists the folks that ARE currently working. I'm trying to see if there is a way to compare the two sheets, via a formula, that will be able to identify when the first name, last name, company name, and skill description are the same and then have those identified deleted from the first sheet.
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
Referencing Active Cell's Value From Another Sheet
I'd like for users to click in a cell in col A in a sheet named "Period7", have the cell value placed in cell C11 in a sheet named "Per7Report," and change the focus or active sheet to sheet Per7Report. Can this be done simply by clicking in a cell, or must a command button be inserted into each cell (note - odd rows only) in col A in Period7. on the code to do this? Note that there is a formula in col A in sheet Period7
View Replies!
View Related
Insert 2 Lines Under Line 6 In The Active Sheet
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job. First of all I would like to insert 2 lines under line 6 in the active sheet. I then need to drag the formulas found in line 6 down over the two new lines. Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"
View Replies!
View Related
Set TextBox ControlSource To Non Active Sheet
I need to use the Textbox ControlSource in some forms to read specific data from worksheets, different source sheet for each form. How do I tell the ControlSource to look at a specific cell in a specific sheet, rather than just the active sheet? I want to do this using the properties of the Textbox rather than VBA.
View Replies!
View Related
Macro To Update Links In Active Sheet Only
I have a workbook which has links to another excel workbook (source). This workbook has many sheets -all exact copy- with different names. I changed startup prompt (edit->links) to "dont display the alert and dont update automatic links". Then I recorded a macro for updating links (from source) and assigned this macro to a button. What I want to do is when I click this update button I want links to be updated only on the active sheet that Im on. My problem is, when I press this button, it updates all links including other sheets (basically whole workbook). My current macro is (I dont know coding but I tried changing ActiveWorkbook to ActiveSheet but it didnt work): Sub update() ActiveWorkbook.UpdateLink Name:= _ "C:Documents and SettingsAlperDesktopExcelsource.xls", Type _ :=xlExcelLinks End Sub
View Replies!
View Related
Named Range That Refers To Active Sheet
is there a way to have the same named range cover a range of cells regardless of which sheet you are on? They seem to want to default to the page you are on. I want to make some VBA subroutines that will go over each sheet, and perform the same functions on each sheet. All sheets have the same format, but different data. I guess worst case scenario I can just break down and use multiple workbooks, but this seems a little less desirable. Should I be dividing this into seperate posts?
View Replies!
View Related
Copy Range Of Cells To Active Row In Second Sheet
Sheet 1 has data entered into it, it is then printed out as a jobsheet, saved and the data cleared. There are certain fields on this sheet that are eventually manually replicated onto sheet 2. The row in which they must go on sheet 2 will always be the 'activerow' on that sheet from a previous operation. It would make life so much easier and save lots of time if I could incorporate copying cells C10,C12,K8,K12,M2,C27 and C29 from sheet 1 to respective cells H,I,J,M,N,R,S of the active row on sheet 2 before I carry out the clear data process.
View Replies!
View Related
Advanced Filter Active Sheet Macro Code
the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code? Sub myfilt() Application. ScreenUpdating = False If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If If Not (Range("indsignal")) Or Not (Range("countsignal")) Then Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _ Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False End If Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
View Replies!
View Related
Indirect: Reflect The Data In The Active Sheet Or Give Me A #VALUE Error
I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it. The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?
View Replies!
View Related
Save Active Sheet As New Workbook, Formats & Values Only
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal. If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
View Replies!
View Related
|
TRACKER
