Im looking for a way to check all charts in a workbook, and resize them.
There are two charts per tab, and i would like the first one to sit on cell A1, and the second to start from a25.
So each chart about 25 rows, by 15 columns if possible.
I've prepared an excel file (excel 2007) with some tabs that contains several charts (4 to 6 charts). When I want to print this file, the sheets with the charts on it doesn't print properly. I have set the print area to cover the charts only and then set it to fit on 1 page but the charts will not scale to fit.
I read somewhere that this is an excel bug - is there a workaround for this?
I could resize all my charts smaller but only as a last resort.
I have an excel which creates PPT from the excel. but the graph pasted in PPT are not aligned and formatted.
By aligned, i mean re-size the graph and paste in a formatted fashion (currently they are overlapping each other)
My entire PPT created is off alignment and formatting. E.g heading are not properly aligned (they are touching the line below) and so on.
I have a Userform that when opened shows the Excel spreadsheet sitting behind. Is it possible to write code such that when opened the Excel workbook has been sized and re-positioned such that it is effectively hidden under the Userform?
View 2 Replies View RelatedI have a 40+ page workbook that I'm working and I need to print every sheet in the workbook on legal size paper, and each sheet can not be > one legal piece of paper
The macro will cycle thru all the sheets in the workbook, yet it will only adjust the current active sheet. I highlighted where I think my error is in red....
I was just wondering if it possible to create a macro that permanently increases the height of a workbook by a set increment
I have several workbooks that i have the code below assigned to
[Code] ......
So when they open they resize to that set size and position. and what ive been doing if i need to resize (which is often) is just adjust the values in the above code.
Not that the way ive been doing it is difficult but i was thinking how great it would be if i could have a macro i could run that permanently adds 9 to the .Height and subtracts 9 from the .Top
I have this workbook of an AR summary.
I would like Charts in there that pull from 'Sheet 1' and what i am trying to achieve is the chart always shows the most current data, as the data is entered weekly. Is it possible to have the graph constantly showing the data that is the most current?
The X & Y are
Total AR
10/22/06 10/29/06 ect.. 13 weeks of most recent data.
There are about 15 different graphs, and it is time consuming to have to go into each one of them and update all the series.
I've been updating charts from different workbooks to a destination workbook manually & it's getting a little too much recently. Need to writing a macro which prompts for the source & destination workbooks and to copy charts from source to destination workbooks?
View 5 Replies View RelatedWe have a worksheet, which contains charts and data which requires updating by different department.
However, when I have this work- book as a Shared workbook (Tools - share workbook), The charting and macro has all been deactivated.
Would anyone have any idea on how do I share a workbook - while being able to also update the Excel Charts within it ?
(Different user will be updating different charts, and there will be no conflict in between them)
I'm trying to go through a workbook and, based on some user inputs, hide certain sheets and charts (these charts are their own tab, i.e. a chart object). Here's a sample code I wrote for a dummy workbook I have. Unfortunately it doesn't work, I think because the loops are nested. However if I can figure out how to fix this code I can easily adapt it to my other real problem.
View 2 Replies View RelatedI will attempt to describe my problem the best I can. Thank you in advance:
I have a series of charts (and the tables in which the data for the charts is stored) located in a worksheet. I copied and pasted each one of these charts next to its original. I then moved each one of the new, pasted charts into a single chart sheet at the end of the workbook. Is there a way, via macro or otherwise, to update the charts now located in chart sheet if any changes are made to the original charts?
I have a workbook that has 52 weeks representing all the weeks in the year for a trainning plan. I plug in various stats like heart rate, energy expended, power, time, ect. Then, some other fitness markers are calculated from these values. So i want to take the data from each week (each sheet) and plot it in a chart on another sheet dedicated to just charts. I'm not really sure if i should reference the different cells into the sheet then make a chart from those, or can i grab the data from the same cells in all 52 sheets at once?
View 9 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].....
Consider two instances of Excel - one with a workbook containing a chart and some VBA code, the other containing a blank workbook. When I paste a chart from the first instance of Excel to the other, all the VBA code from the original workbook is also pasted into a new third hidden workbook in the second instance of Excel! If that weren't weird enough ON WINDOWS XP (but not Windows 2000) if the VBA code included WorkbookOpen or AutoOpen this code is executed in the second instance of Excel, which of course fails if it references sheets that only existed in the workbook open in the first instance of Excel! This is the first time I've seen the same version of Excel do different things on different versions of Windows! Example attached - What's going on!? Auto Merged Post Until 24 Hrs Passes;...note: this does not happen when pasting charts between workbooks open in the SAME instance of Excel
View 6 Replies View RelatedMy DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers
Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average
When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.
I have a workbook where there are around 10 worksheets. All of them are build the same, but only different sheet name (country name)
What I would like to do is use the column A to check if no one did put a duplicate in a differnt sheet. Now I have a formula in each sheet, but it would be great if it would also look in the rest of the sheets if there is no duplicate.
I have a code that close a csv workbook after use. But sometimes the book is not opened from the start of, and then I get an error trying to close it.
How can I easily determine if the workbook wbOBX is open or not?
Code:
If Not wbOBX Is Nothing Then
wbOBX.Close False
End If
I have to files and a macro which copies data from one file to the other. The code goes as follows:
Sub test()
Range("A2").Activate
ActiveCell.Copy
ChDir "D:"
Workbooks.Open Filename:="D: est2.xls"
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Everything is ok as long as the second file (test2) - the one data is copied to, is closed the moment the macro starts acting. The problem is: when test2.xls was opened before the macro was activated a window pops up saying that all unsaved data in test2.xls would be lost.
The best thing to do is to write a piece of code stating that : if the target file is closed then the macro should open it and do its stuff, but if the target file is already opened than the macro should simply switch to the already opened workbook without trying to reopen it. All I need is a piece of code regulating that
Y have files that are stored as .xla files because they are standalone applications that just contain the code.
Y need to test if one .xla file is open and if so close it, so i'am able to open another one. Otherwise the APP withevents from both APP get all mixed up.
I'am actually doing it from vb.net 2005 and i 'am using latebinding.
But the code would be similar y guess.
I have a small group of excel files that I've tried to code to update a master file. I have borrowed code snippets and adapted the rest (still trying to learn). My appologies to those I borrowed from! I would give you full credit if I could remember who did what. Anyway, The files are on a network share folder and everything works perfectly for me, but when one of the people who this was disigned for tries to update it always says "File already open, please try again later." This message is built in to the code.
All the files are in the same shared folder (to make it easier for me) along with the master file.
Here is the code:
Private Sub CommandButton1_Click()
Const strMaster As String = "PMT_Master.xls"
Dim wbMaster As Workbook
Dim srcrng As Range
Dim destrng As Range
How do you check to see if this is the only workbook open. Basically, I want something like:
If only workbook open
application.quit
else
activeworkbook.close
I just noticed that if you already have a workbook protected and then run code that will protect it, it will become unprotected! (I have a line of code to protect the workbook in the workbook_open event; incase the last user unprotected the book on the last use). Apparently it doesn't work like protected worksheets, where if the sheet is password protected and you have code that tries to reprotect it with a different password nothing will happen. I can not find a way to check if the workbook is protected (and if so skip, but if not then protect). Worksheets have "activesheet.protectcontents" to tell you True/False.
View 3 Replies View Relatedhow to check if a workbook is open or not, and then do something on the resualt. What I would like to know is, how can I make the program check if the file is open on another on the server where others can access it? I'm reading this back, not sure im explaining it right... If i have a macro that checks if a file is open, is it checking if its open on just my PC, or is it checking if its open else where?
This is the function Roy did for me: [code].........
I have some code setup to create hyperlinks to all the excel files relevant to the current project a user is working on - it details when each file was last modified so it's possible to see how the project is progressing - and whether or not they need to review any changes.
I could do with including a flag detailing if someone else currently has the file open (prior to them attempting to open the file - as they're more than likely to open as read only and end up saving under a new filename - which I don't want happening) - as many of the projects are shared (one user might be in Edinburgh, the other in London for Example). It would be handy to display the user's excel User Name (as I've persuaded everyone to update this). I've added messageboxes identifying Last modified by user x , dd/mm/yyyy hr:min to auto_open's in the past, but am experiencing brain-freeze today
Private Sub Workbook_Open()
Dim vFileName As String
Dim sLastSaveTime As String
Dim sLastAuthor As String
vFileName = ThisWorkbook.Name
Application.Volatile
sLastSaveTime = _
ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
Application.Volatile
sLastAuthor = _
ThisWorkbook.BuiltinDocumentProperties("Last Author")................
I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.
Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select
[code]....
I have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?
View 9 Replies View RelatedI am trying to ensure that m work book keeps the same name even when I email it to others.
I have been told on this forum that it is possible for a workbook to check its own name once opened?
I have two different names that this file can be called 1st is the one that is in use all the time and the 2nd is a archive version where the name changes each month.
The file name I wish to keep the same for the no archived version is Toolbox and a predetermined number I have a workings sheet called workings and in a cell i have a formula =TEXT("Toolbox Application " & Cover!E10,) this will give me the file name that i need to check for example it could be Toolbox Application 00089 or Toolbox Application 00967.
I also have a cmd that archives the workbook and saves it as Toolbox and the Month it was for Toolbox for Jan for example
I also need this to open if it is an archive
if neither of the names match i need the workbook to close and a msgbox to tell user to rename the workbook correctly.
I have written a routine that automatically saves the file to a specified folder as the name of a cell from one of the worksheets. I am trying to use the following code to see if that filename already exists and stop the routine if it does. If I manually enter an existing filename the routine works well, however I cannot get it to recognise the automatically created filename, here is my .....
View 9 Replies View Relatedhow to design a piece of code that would allow me to check whether a workbook which is not currently opened and shall not be opened at all, contains a certain value. Here my developement which failed to prodeuce the desired result:
Sub test()
Dim wBook As Workbook
Set wBook = Workbooks(Filename:="D: arget.xls")
If wBook. Sheets("AAA"). Range("A1").Value = 123 Then
Range("C3").Value = "Good stuff"
End If
End Sub
Is there any way this can be done without opening the target file??
I am looking to check if its open, if it is, then copy the row, if not, then close it.
I am still new to vb, so learning as I go, but looked at many other posts, and there are so many ways i have seen to open one, I'm not sure which is the best, and none that I have seen show me if they check if its open.
