I have created a powerpoint that has linked charts from excel. Stupidly, I have saved both of these files (powerpoint and excel) on my desktop.
I need to save them on another drive (so they can be accessed by others) but i am worried that if i move the original excel file, it will break the links for the charts in the powerpoint file....
I have over 150 charts so I don't fancy going through and re-pasting each chart.
How to temporarily disable PowerPoint charts which are linked to Excel. The problem is, whenever I copy a slide and try to paste it elsewhere, Office attempts to update all of the links in the entire PowerPoint. Since the slide deck has so many links, this takes an awful lot of time to do. I don't want to break the links completely as I would lose any ability to keep them dynamic. I've checked all of the advanced options within PowerPoint, but cannot find any way to temporarily disable links from updating.
View 1 Replies View RelatedI have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB()
' Copies VBA modules, Calls
Dim wbNew As Workbook, wbT As Workbook
Set wbT = ActiveWorkbook
On Error Resume Next
Kill ("PATHmod1.bas")
Kill ("PATHmod2.bas")
[code].....
With the above code, is it possible to trasfer all selected charts, on to a new spreadsheet and print them to fit on one page Landscape, no margins?? Or is that stretching it, i tried it but i print them all A4
View 2 Replies View RelatedI need to export serveral charts to several slides within PowerPoint from Excel Using VBA. I use 2002 versions. I added chartobjects (2) under 1 but not sure if that is the correct placement to start adding more charts to slides.
Private Sub CommandButton1_Click()
'Sub PowerPointOLEAutomation()
Dim ppt As Object, pres As Object
'Create a Microsoft PowerPoint session
Set ppt = CreateObject("powerpoint.application")
'Copy the chart on the Chart Labels Demo sheet
Worksheets("sw dr").ChartObjects(1).Copy
'Worksheets("sw dr&ot").ChartObjects(2).Copy
'Create a new document in Microsoft PowerPoint
Set pres = ppt.Presentations.Add.........................
I am trying to export charts to powerpoint. I have a sheet where the user gives the list of excel files and sheet names (charts as sheet) to be imported. I run a loop to create this... but then i get a script out of range error.
Sub Chart2PPT()
Dim objPPT As Object
Dim objPrs As Object
Dim shtTemp As Worksheet
Dim chtTemp As Chart
Dim intSlide As Integer
Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
Set FSO = New FileSystemObject
If FSO.FileExists("\server4meme.ppt") Then
Kill ("\server4meme.ppt")
End If
Set FSO = New FileSystemObject
If FSO.FileExists("\server4me ot.xls") Then
Kill ("\server4me ot.xls").........................
I don't know if this is possible, but is there a way to have a macro in Excel copy and paste charts into a Powerpoint file? I've got a program that creates all the material needed for a Powerpoint slide deck and I want to automate the copy/paste work.
View 2 Replies View RelatedI need to transfer data to powerpoint from my '4 box' reports in excel.
Essentially copying everything in the range A40:U74 inc charts and paste them into a slide as a picture on the click of a macro button.
I have to do alot of reports like this for my project and it takes up alot of time copying and pasting etc.
I have seen different codes for this but they are either for charts only or text... the one i did see for both, didnt work at all.
I am trying to write a macro that goes in an excel file that creates a powerpoint presentation, and puts four (4) charts in each slide.. I currently have a code that is pasting all of the charts in the same slide and I can't figure out why it isn't working (side note: I haven't attempted to resize or relocate the pictures on the powerpoint slides yet)..
Code:
'Add a reference to the Microsoft PowerPoint Library by:
'1. Go to Tools in the VBA menu
'2. Click on Reference
'3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
'keep button in same location
Set btn = ActiveSheet.Shapes("CommandButton17")
With btn
btLeft = .Left
btTop = .Top
End With
[code]....
Right now the code takes charts & tables from all sheets in a workbook. I would like to limit this to only charts on one tab - CHARTS.
I am also looking to tweak this to open an existing presentation, as well as apply a template instead of simply pasting to blank slides.
Sub Chart2PPT()
Dim objPPT As Object
Dim objPrs As Object
Dim objSld As Object
Dim shtTemp As Object
Dim chtTemp As ChartObject
Dim objShape As Shape
Dim objGShape As Shape
Dim intSlide As Integer
Dim blnCopy As Boolean
Set objPPT = CreateObject("Powerpoint.application")
objPPT.Visible = True
objPPT.Presentations.Add
objPPT.ActiveWindow.ViewType = 1 'ppViewSlide
For Each shtTemp In ThisWorkbook. Sheets
blnCopy = False
If shtTemp.Type = xlWorksheet Then
For Each objShape In shtTemp.Shapes 'chtTemp In shtTemp.ChartObjects
blnCopy = False
If objShape.Type = msoGroup Then
' if ANY item in group is a chart................
I've been using a VBA code to look through my spreadsheet and find any graphs in any tab and move it to powerpoint. I have about 70 tabs with 7 graphs each.
I have is that the order of the graphs in the slidepack isn't in the same as found on each excel tab.I also tried renaming them (chart1-chart7) but problem still remains.
I have to prepare a monthly presentation for my company. I have all financial data in multiple tabs in excel that have been linked to many powerpoint slides.
The problem is, I update the same excel sheet every month and save it with a different name. How can I change the source in powerpoint to select and update all the slides with all the corresponding tabs.
At present, I use Edit link (ALT+E+K) > Select each slide on the powerpoint> Change source to new excelfile. When I select all the sheets, the "change source" greys out. Hence I have to do it individually.
I have 3 worksheets, one worksheet is a summary worksheet that gives totals for what the user chooses... like Actual vs Budget or Actual vs Prior... etc. When we are in our meetings a common question is where did these numbers come from?
I would like to create a macro button that when you are on a cell you can click the button and the macro well take you to the destination cell. Is there a way you can write a formula in to the macro that would use the formula in the current cell and take you to where that number comes from?
I have a sheet with about 50-60 charts and I am hoping to have a master list of chart titles on the side, how can I link these titles with the charts themselves?
View 1 Replies View RelatedOn a weekly basis I update a suite of charts in one workbook that are created from several other linked workbooks. Once compiled I send this via email to a colleague who incorporates it into another report by printing the charts as a pdf. For some reason he often ends up with erroneous data (zero's where there shouldn't be on the charts), or often his system doesn't display certain elements like titles and axes etc
We think his system is spending resource looking for the original data and getting itself knotted up, but surely there should be a way of delinking the charts but maintaining their integrity as they are saved?
I'm trying to use VBA to automatically change the data displayed in an excel chart. To do this I count the number of items displayed on the chart using Chart.Seriescollection.Count. I then loop over the Chart.Seriescollection(x) and try to change the Chart.Seriescollection(x).Formula string to link to the cells I want.
Problem is that, sometimes, the cells the curve is currently linked to are empty. Such a curve does show up in the Count, but I can't access the .Formula, even though this can be done manually from within Excel.
If there a way to change the souce data so that it changes based on a variable? For example, if a cell's value equals X then use one range and if it is Y then use a different. As far as I can tell you cannot use the "IF" argument in a chart Source field.
One option I came up with is to change the actual data on a worksheet and always have the source data point to that range in the worksheet. The problem is that the source data is not always the same length (Monthly may be 100 columns long while Daily is 1000 columns long - the chart looks starnge).
for example: ...
I created a spreadsheet with several combo boxes that that is linked to other data.
When this data is sorted, the values in the combo boxes are automatically changed.
Is there any way to prevent the the values to change?
I've inserted a Solidworks drawing into a worksheet & wish to change the displayed image using VB. I suspect this could be done by changing it's source file, but can't get the code to work.
View 3 Replies View RelatedI am wondering if there is a way to take the charts I have created, and save them on a different worksheet in Excel. The issue I have is that when I erase the data source, the charts lose all their data. Is there a way to save the chart as is without it relying on its data source (ex. So I can remove the data for the chart)?
View 2 Replies View RelatedFor some reason, the charts in my excel workbook do not update automatically when the data are changed. I have set Calculation to Automatic, but it still doesn't work. They update only if I close and reopen the workbook. Is it due to a problem with setting? How can I get the charts to automatically update?
View 4 Replies View RelatedI have a userform with a textbox and would like the user to type inside the textbox which in turn send the text typed to a cell on my spread sheet say sheet 1 cell ref A1. I am writing the following into the control source Sheet1!A1 but the control source does not except this. I am using excel 2007 .
View 2 Replies View RelatedSheet1 contains a chart using a dynamic name in Source Data: Values. The dynamic name data is controlled by another cell. When I copy the sheet - i.e. Sheet1 (2) - and change the control cell, the Source Data: Values in the copied sheet still refers to the original Sheet1.
View 3 Replies View Relatedi have code like this to copy certain word in cell and place it at new sheet:
VB:
Sub Foo()Dim i As Long, iMatches As Long
Dim aTokens() As String: aTokens = Split("Accommodation", ",")
For Each cell In Sheets("Sheet1").Range("C:C")
[Code].....
the problem is the cell source is not deleted. what line should i change in code above to move the result to new sheet and delete row source
I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.
I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.
The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.
how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?
I am trying to find the last row of worksheet "A", return the value, then in Worksheet "B", reference Worksheet "A" Column J - last row. It gives me an error for the formula. I don't know if I am close to the solution, or way off. Here is my code:
Dim LastRow As Long
Sheets("NICMap31 Data").Select 'goes to worksheet A
Range("A1").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'finds the last row of worksheet A
Sheets("NIC MAP Data Table").Select 'returns to worksheet B
Range("C7").Select
ActiveCell.formula = "='NICMap31 Data'!(J & LastRow - 1)" 'link to worksheet A with this formula.
I have a worksheet, and I want to place a combobox in a cell, but being able to add values not on the corresponding list. Done almost everything, but cannot find a way to use the position of the cell, so I can dinamicly place a combobox. Ex.- place a combo in a cell, then after you get the value, delete the combo and put it in the cell bellow, etc. In order to do this I need the position of the cell: left, right, height, width.
View 2 Replies View RelatedI have this excel template which i am working on and its a bit kind of complex linking several sheets together.
My problem now is in the Junior STUDENTS INPUT.xls
the 2nd term report has to be replicated 150 times following the patter of the one already done linking all necessary cells as the first one,
I have several Workbooks, say BookA, BookB, BookC (or more). Let BookB and BookC be linked to BookA. I donīt know the actual names of the Workbooks though as the user may change those any time. Let BookA have several Worksheets, one named SheetA1. Let BookC have several Worksheets, one named SheetC1. Now when a specific cell in SheetA1 changes, I want to set the value for a specific cell in SheetC1 (using Worksheet_Change for SheetA1). How can I get at the actual name of that cell in SheetC1? I did imagine to loop through all the Worksheets in all linked Workbooks till I find one named SheetC1, but somehow I canīt manage the right syntax for that.
View 6 Replies View RelatedI am trying to return the value (date) of a construction schedule by searching for a specific construction activity ID number. Is there a method I can use which incorporates a text search so that as the schedule grows (cell locations shift down) the lookup function still follows the unique activity ID?
Below is a sample of row of the ID I must search for, and the date I must return (on a separate excel file):
A
B
C
D
-
Activity ID
Description
Start Date
End Date
1
L3S4C10020
Supporting Walls to UPTS Slab 3
19-Jan-14
25-Jan-14