I copied a worksheet with a pivot table as a new worksheet. I wanted to group each pivot table differently on each worksheet, but the changes I make on one worksheet is reflected on the other. Is there a way to break the "link" between these tables?
View 2 RepliesI've recently aquired a spreadsheet previously maintained by someone else. After my initial review the speradsheet has many links that need to be updated, which I do via Edit-Links, but there are also some links that no longer need to exist. I have reviewed all sheets and some links just no longer exist, however when I try to break the link or change the source nothing happens. I have had the original owner also check and confirm and they have the same issue. Finally I tried copying all the tabs into a new workbook, but the link shows up again.
View 3 Replies View RelatedI inherited a spreadsheet that contains a link to another spreadsheet. I want to remove the link and I can not find a way to do this. I have checked under Edit/Links but there is not a delete or break links options. The linked spreadsheet does not exist.
View 4 Replies View RelatedThis is the code I use to start a bash session and run a .sh:
[Code]....
It works well but it is a long-running program and I would like to close down Excel, or run different sessions of Excel, while it is running. Right now, if I close Excel while the bash is running, the external script stops.
Is there a modification to this command line that would safely sever the link between Excel and the bash session?
I have simple Countif and Averageif formulas that I would like to have linked to variable data in cell E6 on the Assumptions (2) tab however, the formula is not taking the cell as the criteria and only taking a hard number when I am using >=.
The example would be: I3 needs to be able to use the data changed in E6 for the >= number instead of the hardkey 29...
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
I have a spreadsheet in which I have several sheets, each of which has its own table populated by an SQL query that displays the data per sheet by customer name and then sorts by a status column that is either "open" or "closed" (Open in Descending order) and then sorts by created time (also in descending order). This table is 17 columns wide. Right next to this table, I have another 2 column table that is to be populated by hand. The problem is that the 2 tables don't talk to each other. For example:
If row 3 of table 1 contains an open action, then row 3 of table 2 will contain data pertaining to that action. If the action is closed via the database refresh, then it may move the action to row 10 now (as open is always shown at top) - however the data that was entered by hand in table 2 is still stuck in row 3.
find a way to make the 2 tables communicate and work together?
I cannot make it only 1 table as far as I can tell because the database refresh wipes out my additional manual columns every time.
I have problems in linking my date picker to the pivot tables
For example: I have 6 pivot tables, all of it has date fields, and then I have a button that will show date picker to select start and end date. After I selected dates, I want to auto-filter the pivot tables using that selected dates.
Is it possible to somehow link sheets in excel to different tables in Access so when I update the sheets in excel it can automatically update the tables in access?
Doesn't have to be on line but I want to avoid the continous copy paste or exporting and trying to automate this process.
Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
The code which you provided works fine no problem for a page break. I need to run the macro for the page break by asking the input file for page break to be done.
For Example, If excel filename "A" contain the code which you have given need to ask to input the filename "B" and process need to be done in file "B".
I have added some code to your code which you provided but it gives error message "1004" "Method 'Range' of object '_Application' failed" at following line :
Set rng = oExcel.Range(Cells(2, 2), Cells(Rows.Count, 2).End(xlUp))
I have two worksheets, one with detail monthly information and one with YTD information. So let's say the three numbers I want to capture in the YTD sheet are in columns B, G and I on the monthly sheet. January's data might be in B5, G5 and I5. February's data is in B12, G12 and I12 and so on.
On the YTD sheet in cell C2 I link to Monthly!B5 and in C3 I link to Monthly!B12 so cells C2 to C13 on the YTD sheet show the monthly totals from column B on the detail sheet. On the YTD sheet, cells C20 to C31 show the monthly totals from column G on the monthly sheet, so cells G5, G12, etc. And finally, cells C40 to C51 on the YTD sheet show monthly totals form column I on the monthly sheet.
In the past I've always created all these links manually. After creating the links in C2 to C13 on the YTD sheet, is there a way to use a formula in C21 that uses the link in C2 to create a link for G5?
how to break the string into 3 variables.
Example: Given string is : -4.98e-005x^2+0.368x+0.0588
All the string there will be two + symbols.
I have to break the above string into 3 parts and store it into variables like
a=-4.98e-005x^2
b=0.368x
c=0.0588
I need a macro that can take a sheet containing million rows and break it down into multiple sheets within the same workbork beginning on sheet 2 (source data on sheet 1). I would like the macro to include a input box that appears when the macro is run that asks "how many rows per sheet?"
View 2 Replies View RelatedI am herewith enclosing the sample data, which is one row, containing the branch name, branch code, itemcode, rate, qty, amount . Each branch has several items with different quantity of various rates, which has come in the horizontal line. Now I want that in one by one, for converting that data into oracle.
BR.NAME BR.CODEITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTITEM CODEITEM RATE QTYAMOUNTxx1104.5100450153250750263.25158513.5394.5180810506.55003250result should be like thisxx1104.5100450xx2153250750xx3263.25158513.5xx4394.5180810xx5506.55003250
Is there a simple code I can add to the end of my macro that will break all existing links in the active workbook?
View 9 Replies View Relatedbreaking apart a string in a cell.
in cells A6:A112 my trial balance export from QB Online is account # [SPACE] Account name:Sub Account:Sub Account
eg. 70160 Administrative Expense:Travel & Entertainment:Travel & Ent.-Travel & Lodging
I would like to break the string apart so I have only account # in column "a" and the right most sub account name in column "b". So if I use my example from above, I would have 70160 in "a" & Travel & Ent.-Travel & Lodging in "b". Note all the account name & sub accounts are separated by ":"
I have managed to capture a table from a website and currently have it going to range"a1"
Set mytable = IeApp.document.getElementById("maindetail")
Sheets("sheet1").Activate
Sheets("sheet1").Range("b1").Value = IeApp.document.getElementById("maindetail").innerHTML
Application. ScreenUpdating = True
What Id like to do is breakapart this data, remove the html tags, and place each element of the table in its own cell (a1, a2,a3 etc.). Anybody have any ideas, I'm just not familiar enough with the IE DOM.
in COLUMN A i have text in one cell i need break this text in two cell COLUMN B and COLUMN C
[Code] ......
I have a workbook that is not showing everything that I would like it to on one page (but I know that all the information would fit onto one page (width) but I cannot seem to be able to change the page break. I tried following the directions in the "excel help" but just can't seem to get it to work. I have attached a sample workbook so that you can see what I mean.
View 2 Replies View RelatedBottom border each page,
View 9 Replies View RelatedI have a worksheet where the print area is 16 columns and ~90 rows. have it set to print on one page in page set-up, which generally scales to about 80%, and this has worked fine for years. A couple of months ago I was traveling for work, but got a call that the worksheet was printing each cell as a separate page. My co-worker messed around with it and it seemed to fix when she cleared and reset the print area, and has been working fine ever since.
Today it popped up again. It is setting automatic page breaks every few cells so page 1 is A1:A7, page 2 is A8:A11, etc...Both Print Preview and actually printing a page show me my few cells in the lower right hand corner of the page. Viewing the page break preview shows that they are all automatic page breaks, and if I try to move them I get the error that Microsoft Office Excel cannot move the page break because doing so would reduce the scale below the minimum 10%.
As I mentioned I have it set to "fit to" 1 page high and 1 page wide, which it right now is saying sets the scale to 10%. If I instead choose the scaling to be "adjust to" and set to 100% it only wants to take up 4 pages, but if I try to manually drag the break over afew columns it reverts back to wanting to print every few cells.
to have a page break by grouping id together, Heading need to be printed on each page. I have attached the sample file.
eg. the ID.
AAAAW0250H
AAAAW0250H
AAAAW0250H
AAAAW0250H
AAAAW0250H
AAAAW0250H
---- need page break ---
AAACA0314L
AAACA0314L
AAACA0314L
AAACA0314L
................
................
................
I have this code that is to long for the vba window so I read around and found I had to add a space and _ to extend the line.For some reason I get an error in the code when I do that?
However I copied the formula right from the working cell?
I have a spreadsheet that I have been using for some time.
Suddenly, in just the Worksheet_change event procedure, break points will not work.
In the same sheet break points do work for other events.
I've tried everything.
I have is a start rent date and an end rent date. They want to break down how many days in each month the item was on rent so they have a column for each month. For example say an item was rented on 12/14/08 and returned on 1/12/09. It was rented for a total of 30days 18 days in December and 12 days in January. So I would need a function in each column that would return a 14 in the December Column and a 12 in the January Column. there are over 350 rows and they are doing the process manually right now and taking several days.
Also I just noticed that the date range is currently entered as a text in the format 2008-12-14 so YYYY-MM-DD. Don't know if the text will be a problem.
Just attached an expample. In it I am trying to find a function to fill in the green cells.
I need to set the page break in my excel file. Basically what I need is for vba to check if the page breaks are a certain value (in this case Range("A150"). If they are not, then the code will set them to what I need.
The problem is the code is not working.
Code:
If ActiveSheet.HPageBreaks(3).Location Range("A150") Then
Set ActiveSheet.HPageBreaks(3).Location = Range("A150")
End If
I have copied from an email a very long list of emails but they all end up in one single cell
sample: eyee@gruposhahani.com; laroyeimport@gmail.com; sjimenez@vsjimenez.com; yhwh_shekhinah@hotmail.com; sparcells@formatec.com.pa
and would like to know if there is a way I can quickly create a list separating each email into a different cell. I was thinking of using a MID formula or maybe an INDEX or MATCH, but cannot figure out how to make it work.
I'm looking for syntax to break all links in a workbook using VBA. I've found the below through independent research but I'm receiving a type mismatch error.
Code:
Sub BreakLinks()
'Macro Purpose: Break all links in the active workbook
Dim vLinks As Variant
[Code].....
Taking the following as an example, where you find yourself in an infinite loop. How can you interrupt the sub and keep the code. All because you where to stupid to save it before running it.
Sub TEST()
LabelA:
****MsgBox "YOU IDIOT"
****GoTo LabelA
End Sub