I have "inherited" an Excel workbook from someone that is really unwieldy in presentation and difficult to read. I want to break it down and reorder some of the tables and information. To do this I need to copy/ cut parts of different sheets into others. When I do this, I'm finding that I get reference errors even though the source documents are the same.
View 3 RepliesI have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View 11 Replies View RelatedI have developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54....
I found a topic here that wanted to know how to remove links, I wanted the same thing.
I found an add-in that identified and could remove any links in the workbook depending on the response to the dialog box. So far, so good.
But it found a link that was located on another page, this link was linked to a third workbook. But what the link is doing or what its association is w/ the other workbook is a mystery. How do I see what this link's purpose/function is?
How can I break the link between a chart and a worksheet? If I change the name of the worksheet and try to use the chart I got an error that the current worksheet is the one that the chart is related to. I tried changing the source from the links option in the bar - no effect. I checked conditional formatting - also no effect. How can I break all existing links between the sheet and the macro?
View 2 Replies View Related[url]
I was wondering if it is possible to update links from one worksheet to another that contains a running tally - even when another worksheet is added and needs to be linked to the running tally as well?
I have attached a sample workbook. What I have is a summary sheet ( Sum Hrs CMS) that has SUMIF calculations for a series of rows in the Estimate Sheet.
The problem that I have run into is - when you press the Add New Sheet button on the Estimate sheet, it copies hidden sheet and inserts it at the end of the workbook, however I then need the hours in the CMS portion to link to the summary sheet. What I was wondering is how do the links on the 'Sum Hrs CMS' sheet include this new sheet and the data that is entered on it?
When i save the worksheet cells are loosing links to other cells. what is even more surprising it happens only for 6 cells in 6 sheets.
scheme of links
Basic data
P1
P2
P3
P4
P5
P6
On the sheets P1-P6, each cell A1 has a link to Basic data.
I am try to copy a worksheet "exactly" from one workbook to another, i.e. all formulas and range names reference the new workbook and not link back to to the workbook they were copied from. The code below brings the formulas and named ranges over still referencing the source workbook: SourceWB.Sheets(sht).Copy after:=TargetWB.Sheets(sht)
View 3 Replies View RelatedI have a template file which has a reference to a cell on a sheet in another workbook.
I need to copy this template to 250 workbooks. However, everytime I copy this template sheet into a workbook, it updates the reference to the template name!
Is there an absolute reference I can use for the sheetname?
Example: Workbook Template - Sheet1 - Cell A1 = Value
Workbook Template - Sheet2 - Cell A1 = Sheet1!$A$1
So for so good, but when I copy Sheet2 to a new workbook, I need to have the exact same reference; ie. Sheet1$A$1 and NOT = ['C:Workbook template']Sheet1!$a$1
I have a Excel workbook with two sheets - 'MainDataSheet' and 'ArchiveSheet' .
The 'MainDataSheet' has 5 columns and one of the column is 'status'.
The 'MainDataSheet' will have a command button 'MoveData' which will trigger the VBA macro to move data rows from 'MainDataSheet' to 'ArchiveSheet' . Only the rows having value set to "MOVE" in the 'status' column have to be moved.
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
how to protect a worksheet from editing and at the same time allow all data source links within the sheet to be refreshed? I need my users to be able to refresh all data links as required but not be able to change the worksheet in any other way.
View 2 Replies View RelatedIs there a way I can write a formula to get names from a different worksheet if I type part of that name in the active sheet.
Say Sheet1 contains cells like
1026 Wild hard nuts fruits -Named Fgl1026
1028 Wild Soft nuts fruits -Named Fgl1028
In active sheet, when I type Fgl1026, I want the next cell to left of the activecell to show Wild hard nuts fruits.
[img][/img]
It's difficult to explain, the attached shows the situation better. Basically there's a picture that is somehow linked to a cell. But, you can update the picture or the cell and the other one gets updated.
View 3 Replies View Relatedbuilding a worksheet to list people, payrates and hours worked etc. the header needs to have fill in areas that change with the job so I entered them in the rows rather than in a proper header. what I want to do is get rid of gridlines in the first 6 rows but
leave them intact on the rest of the document. I see templates that this was done somehow.
I have a macro to loop through the files in a folder. The macro will then open up the files and copy over to a new workbook.
As I want to rename the worksheets created in the new workbook as part of the file name. Any idea how to extract the file name out and stored in a string?
Currently it will be named after the Cell A1 in the opened file. However I would like to rename as below.
Example, the file name : 030309_Mary Sales.xls, Worksheet Name to Create : Mary Sales.
File name : 030309_John King, Worksheet Name : John King
(in the code, 030309 taken from my menu sheet,D4 is the keyword , to search in files containing D4, where D3 is the directory to look in)
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
How to change part of an array value?.If i try to change array value "Microsoft Excel" popups a dialog stating "You cannot change part of an array."
Somebody have mention to use "Ctrl+Shift+Enter" but this will change entire array value.So I need a change only a specific cell value.
I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.
View 9 Replies View RelatedCreating a macro that updates and copies part of a worksheet in Excel 2003 and pastes it in MS Word. The sheet I have is a sheet that updates some prices etc and after it updates I usually select a square (part of the sheet), press CTRL+C to copy it and paste it as a picture (paste special ) in word. Is there a way to automate the process?
I am thinking a macro that updates the sheet, copies and pastes iit on word??
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
Move a row of work to resolved tab once column M is changed to resolved This needs to be moved to the first available line on the resolve tab The row that has just been moved from the orginating agents tab needs to be deleted and shifted up When moved to the resolved tab I want it to prepopulate where the row originated from in column N When moved to resolved tab I want it to prepopulate the date it was resolved Also the spreadsheet will be shared
I have attached a test version of the spreadsheet.
I have whats going to be a very large "Input" worksheet that has several headings of columns. One of these columns is "category" as in parts of a vehicle, i.e. steering, wheels, rod-ends and so on. Now what I would like to do, is once I type a new row of information in, to have the filter go through and copy said category row into another worksheet where I can have all of them separated on there own.
Here is a picture of my current spreadsheet. So basically I want to be able once I save the file, that it goes through and where it detects Rear-End in "B18" I would like it to select row 18, copy all of its cells and move them into the worksheet Rear-End
I created a outstanding task worksheet in excel and I would like to move the completed jobs from this sheet to the second worksheet titled 'Completed Tasks'. The first worksheet is called 'Outstanding Tasks'.
I came up with a code to do this (see below). As you can see when I type "Y" into column G it moves the row into the second worksheet. It does this; however it puts it to the bottom of the table on the second worksheet where I would like it to be at the top. I also would like it to delete the row once it has moved it does this but then deletes the other row of information above it leaving blank rows on either side.
I simply want a code that moves the row of information to the second worksheet when I type Y into column G and then delete the row without messing with other information around it.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rng As Range
Set rng = Target.Parent.Range("G5:G1000")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case Target.Text
Case "Y"
Target.EntireRow.Cut Sheets("Completed Tasks").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Selection.ListObject.ListRows(1).Delete
End Select
End Sub
I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.
View 9 Replies View Relatedi need to get certain info from one worksheet to another is there any way to do this automatically
View 9 Replies View Relatedi have this at the moment:
in a sheet named CURRENT is all our data.
when the jobs done the Satus Column "F" is changed to INVOICE
and then once a day or whenever they are all moved to a new sheet named the month and year, in this case JUN 09 this works fine as it is but at the moment i have to delete the lines myself from where it took the data from in CURRENT or AtoZ then remove them in 1 go.
Simply put, i want the code to remove these lines itself....
I have a set of worksheets that need to be moved to their own individual master sheets. i.e. FW32Lightning.csv needs to be moved to Lightning.xls which has all of the fiscal weeks in it as seperate. I know the generic way of doing this, but I need to know how do I write the macro that when I run it every week I don't have to go in and change the fiscal week.
View 5 Replies View RelatedI've included bits of my code and some debug output. Whenever I attempt to add a worksheet, either BEFORE or AFTER I get the following ERROR MESSAGE: Method 'Add' of object 'Sheets' Failed. if I use the .add without before and after a worksheet is added. The problem is that it is always added before the active sheet. I need to replace a single sheet in the correct position of possible 10 sheets. I know the names of the sheets and which one I want to replace, but I can't get EXCEL to move sheets, add sheets by position Number or name, without getting the ERROR.
Public xlApp As Excel.Application
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Public xlRange As Excel.Range
.....
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
....
xlBook.Worksheets.Add After:="Accounts Receivable" *****
?xlsheet.Name
Accounts Receivable
?xlbook.Worksheets.Count
4
?xlbook.Worksheets(4).name
Accounts Receivable
How can I move data from one worksheet and place in another worksheet in the appropriate column? I exported data from Access to Excel in order but I need to group the data in excel.
View 3 Replies View Related