Phantom Names In Copied Sheets
Mar 26, 2007
When copying sheets within a spreadsheet I receive numerous examples of the attached error message: when I search for the named range referred to in the source sheet it doesn't exist - I can only assume that it is a variable name that has been assigned somehow - but as far as I can tell, there aren't any macros associated with the spreadsheets in question that are generating the names that are displayed .. so unsure how the names are appearing. how to remove these phantom names so the prompt stops appearing?
NB: If I answer "No" to the prompt I must enter a different name - and if I do that, the system creates the new name in the copied sheet so if I then copy the copy on to another sheet the number of "named" ranges includes both the original "phantom names" AND the additional "phantom names" I've created as part of this process.
View 5 Replies
ADVERTISEMENT
Oct 15, 2013
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
View 2 Replies
View Related
Apr 25, 2006
I have created some code that copies a master workbook into a new workbook that then creates new tabs for the sheets relevant to week numbers. In the master workbook there are 8 sheets, most of which reference each other in their formulas at some point. The problem arises in the newly created workbook as the sheettab names are renamed e.g "manager" in the master will become "manager wk1", "manager wk2" and so on. All the sheets are copying over great but they the lose their references to each of the sheets within their week groups because the formulas do not change to the correct week references. I am trying to solve the problem by using the Indirect funtion to change the formulas in the master to reference the correct week number, but i feel this is not the best way?
View 2 Replies
View Related
Apr 19, 2008
One of the sheets in my workbook has 5 rows (Rows 1 - 5) that I need to copy and insert at the top of all but 3 of the other worksheets. I can specify the names of the 3 worksheets that I want to avoid changing. The other worksheets will be named differently each time I use the macro.
View 2 Replies
View Related
Feb 28, 2014
I have copied a data to another sheet. but when i change the data, the copied cell does not change...
View 5 Replies
View Related
Jun 17, 2014
I have a spread sheet with tabs that are for each week of the year. Each tab shows the date on top and times (broken into 30 minute intervals) on the side; the inside has information like an event on the inside.
My goal is to keep that information there and to have a "master sheet" that holds all this information in a list format so I can filter out some information.
View 1 Replies
View Related
Jan 5, 2009
I have a link that is showing up under Edit/Links. I have tried to locate this link by highlighting all sheets and using the find command and using part of the file string however it is not finding anything. I tried this with a link that I know should be there and it takes me right to the sheet/cell its located in...
View 9 Replies
View Related
Oct 20, 2013
with a macro. I am looking to copy row 2 to the last row and past the copied rows directly below the copied contents.
View 9 Replies
View Related
Sep 29, 2009
I've tried to look for a solution on the forum, but nothing seems to come up. I've attached a file to help show what I'm trying to resolve.
Column A of the file shows an amount, when summed, give a total of -3.5725E-09.
Each of the figures in column A only has 2 decimal points and if I manually total up the numbers on a calculator it give me zero.
Does anyone know how I can get rid of the -3.5725E-09 without another formula? I need the balance to be zero.
View 10 Replies
View Related
Jun 21, 2008
I remoted most of my number crunching stuff to a dll.
It work fine except for a few strange things that I observed like from time to time a strange message "Error in loading DLL" even though everything works fine. This message appears only when entering the main formula in the formula, and is no problem at all in usual work.
When I investigated, I noticed in the "windows task manager" that one instance of excel remains open even after I have closed excel. This phantom excel process is maybe related to the other problem I explained above.
Instead of calling a VBA function, it creates a VB6 object from this dll and uses the methods of this object. In this way I have remoted the number crunching functionality.
The main parameter passe in this process is a reference to the original workbook. I need it because the number crunching functions need to read data from the workbook.
I made it sure to terminate the life of the object properly on both sides by ad hoc obj=Nothing statements.
Still I think that the "garbage collection" or the "terminating" is the cause of the problem.
View 9 Replies
View Related
Dec 28, 2006
I have an excel report containing eight or so worksheets. A user can make a number of selections with the report to change its context. E.g. to analyse different types of customers; high spend or low spend etc. Sales projections are then presented with several Excel Charts adapting to the user's preferences.
My problem occurs when the user makes a selection (they choose a ComboBox option.) This then triggers my code, which creates the data tables and graphs depending on their selection. The report is quite long so I have created two points in the same worksheet where the user can change the same customer selection option. (Basically, all I have done is create a second ComboBox which triggers the first). Again, this works perfectly causing no errors (other than the display error belowc)
However, when I change the second instance of the ComboBox a phantom copy of my chart is dumped on the current screen view. If I scroll off screen and then back again the phantom chart disappears. I need a quick solution to fix this bug until I have some more time to look at what's causing it. Therefore I simply want to add a line of code, which will scroll off screen and then return back to the same position. (Not very neat, but at least it will get rid of my duplicate chart). As I have screen updating turned off the user won't notice what is happening.
View 3 Replies
View Related
Jan 9, 2008
I have a dynamic chart that allows the user to select the starting and ending dates for the data range by using a scroll bar.
The dates in the data table are input daily and are generally in sequential order, however there are gaps between dates (eg. 1/4/08, 1/7/08), such as would occur over a weekend or holiday period. The dates are used as labels for the X-axis.
The problem is the chart interpolates the dates in the "gap" and includes them on the X-axis and plots them as zero values.
I would like for the chart to display only the dates and values actually in the dataset and not interpolate and include dates/assign zero values.
Haven't had any success in searching the forum. Can this interpolation be prevented?
View 5 Replies
View Related
Feb 5, 2014
How do I get sheets names automatically? I.E. I want to get in A2 sheet number 2 name. In A3, sheet number 3 name...
View 1 Replies
View Related
Nov 21, 2006
On sheet1 of my workbook I have a table containing the colums : Title, Name, Street+nr, postal code + city of some people, Telephone, email.
I would like to do the following (by clicking a button perhaps?)
- For all the records in my table on sheet1 I would like to insert a new sheet in my workbook named to their names (as in sheet1).
- The new sheet must be an existing sheet that I've already made the content (a kind of expence sheet)
- Somewhere on the top of the sheet I would like to have their names , titles en addresses (fetched from sheet1)
View 9 Replies
View Related
Mar 31, 2014
Is there a way to use formula (Sum in this particular case) across multiple sheets with sheet names having a particular criteria?
The sheet names are 5 digits but not sequential (cost centre numbers). I'm trying to find the total if the sheet name (number) falls within a particular range (e.g. 150xx-152xx, 153xx-168xx), with around 60 or so sheets in total.
I know it's possible to specify the range myself, or to add a blank sheet as a marker/guide to create a "sandwich" of the required sheets (e.g. =SUM(Start:End!A1)), but the cost centre ranges may change if they're changed so I'm trying to find a way to identify them as variables rather than requiring maintenance.
Although the layout of the sheets should not change (and so a macro might work..?) I think I'd prefer to find a formula (if possible).
View 1 Replies
View Related
May 15, 2014
i have a names list in range P2:P100
like
P2 = A
P3 = B
P4 = C
P5 = D
P6 = E
P7 = F
then create the sheets with name A, B, C, D, E, F
View 1 Replies
View Related
Feb 2, 2009
I have written some code which asks the using for a name, selcet's a sheet, copy's the sheet, rename's the copied sheet then hide's the original sheet and makes the copied sheet with new name active.
My problem is that I need some sort of code to check for sheets with duplicate names and if true ask the user to rename the sheet or maybe delete the sheet.
View 4 Replies
View Related
Mar 1, 2012
I have a macro that creates sheets from a list of names. I have a template which is copied.
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Dim MySheetName As String
[Code]...
Sometimes I have to update that name list.
What I need is to have some addition that checks if the sheet does exist, if it does I want to skip and move on to the next name.
View 3 Replies
View Related
May 24, 2013
I have an excel worksheet with TableNames and ColumnNames. I would like to copy the different TableNames to it own sheet with the corresponding ColumnNames. I also would like the sheet to be renamed to the tablename. I have the workbook but I don't know how to attach it.
View 4 Replies
View Related
Jan 19, 2009
Is it possible for a macro to delete any sheet that within a workbook that does not match a specific list of sheets. For example a workbook will always have Sheet1, Sheet2, Sheet3, Sheet4... but it can have many other sheets added to it. The names of the sheets can be completely different however the sheets that I want to keep will always have the same name.
View 9 Replies
View Related
May 7, 2014
I have two sheets. Sheet A (Receiving Log) contains all orders with NAME (D1) and QTY ORDERED(E1). Sheet B(Component Reject Tag) contains all rejects with NAME(D1) and QTY REJECT(E1). Sheet A and B are Many to Many relationship
What I am trying to figure out is:
Match the names from Sheet B to Sheet A and sum up the QTY REJECT from Sheet B divide by the sum of QTY ORDERED for the NAME from Sheet A.
Business would like to get QTY Reject percentage by each NAME (Supplier)
In a final table lets say Sheet C I would like to see NAME, QTY Ordered, QTY Reject, Ratio in which will allow my to perform a Top 10 suppliers of highest reject percentage.
Another note to mention, Sheet C presumably to have all the formulas as in Sheet A and B will be overwritten and pasted with new data.
View 10 Replies
View Related
Feb 12, 2013
I'm trying to retrieve the worksheet names to a specif cell and populate from that point only with the names of the visible sheets, but It keeps returning only the last visible sheet name and not the sheets that are visible. Where the code is failing - I'm self learner and start with VBA macros a couple of months so I'm still learning how to identify when the error show up
Code:
Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
For i = 31 To ultl
[Code] ..........
View 6 Replies
View Related
Aug 25, 2007
Can a macro be provided to show the list of sheet names in a drop down list at cell B2?
View 9 Replies
View Related
Oct 19, 2006
Need a way to generate a list (i.e. array) of tabs available in the current spreadsheet?
View 3 Replies
View Related
Dec 19, 2006
I made one file with 13 sheets.
sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ
2. abc
3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
View 9 Replies
View Related
Dec 4, 2007
I have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.
I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.
The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)
Sub Copy_Visible_Sheets()
Dim arr() As String
Dim i As Integer
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Application. ScreenUpdating = False
'Make sure template is saved as .xls
If Not ThisWorkbook.Saved Then
MsgBox "Please save this workbook before generating a Client Copy."
Else
View 6 Replies
View Related
Jan 11, 2008
I have a long list of tabs listing "projects" which have changing names - on the first sheet, I want to have the table of contents automatically update and link to each tab - I want the user to only have to change the tab name to have the table of contents and link update -
View 5 Replies
View Related
May 2, 2013
I have several sheets with tables that need to contain one identical common column called "product name" while all the other columns are different on each table. The issue is that this list in the "product name" column changes by adding, removing and even name edits and currently I need to edit each every table for each change. How can I only have one uniform list to modify that is represented on all these tables that updates.
I have tried a master list with links but causes issues when a product name row is deleted in the master it does not delete the row in the others as well as adding a new name to the master list requires me to recreate a link in every table which defeats the purpose of it trying to save me time having to modify something different on every table. Also each of these lists are sorted or filtered often which I want to avoid being reflected across all of the other tables and lists.
View 14 Replies
View Related
Jun 27, 2013
I work for schools use data to guide teachers practices in the classroom. My main function is to mine down through data for kids that teachers focus on specific skills with specific children.
I start with a big conference each summer. By hand I combine, by student name, the data collected over the past school year. We then tear down to student levels setting goals based on statistical analysis. I need to combine across all of these csv files in a way that appends the data from each into one line per kid. I thought that 'vlookup' or 'index, match' might do it. I don't know.
View 14 Replies
View Related
Feb 23, 2014
I am writing a macro to consolidate data from different worksheets in more than one Summary Sheets.
My workbook has quite a number of worksheets, from different department e.g. OPS001, OPS002,OPS003, ADMIN001, ADMIN002, ADMIN003 and so on.
I want data from OPS001, OPS002 and OPS003 to go on one sheet e.g. "Summary-OPS" and data from ADMIN001, ADMIN002 and ADMIN003 to go on the other sheet name "Summary-Admin"
When I am working on Summary-Ops sheet I want to copy data from sheets starting with name "OPS" and so on.
View 3 Replies
View Related
