Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Macro: Convert Cell Value Without Being A Circular Reference
if this is possible without becoming a circular reference. Aim: to have a Macro that takes user selected cell and converts it the result and place it back in the same cell. Example: could have a typed value in A10 say 500, and when the cell i selected and the Macro is run is converts the value by B10 (.5) and places the value back into A10. ie A10= A10*B10 (250) I know this is a circular reference but could the macro say take the Value A10 and hold it in a temporary string of some sort before calculating and then returning the new value in there?
View Replies!
View Related
Circular Reference Due To Formula Already In Cell
Workbook attached. Workbook contains 53 sheets. 52 sheets are identical and represent one sheet for each week of the year. At the bottom of each column is a SUMIF function that member JimFuller1 showed me how to use. The 53rd sheet is where I would like to calculate all of the totals (per item and per first-aid kit) I tried using the same SUMIF principal, applying it to all sheets but I keep getting a circular reference. I asked a coworker of mine and he said that it was due to the fact that the cells I am trying to put in the SUMIF function allready are part of a different SUMIF formula.
View Replies!
View Related
Automated Entry In Cell Instead Of Manual (without Causing Circular Reference)
I need to make cell A1 = cell D1. Cell A1 is calculated by entering a number in cell E1[COLOR=blue ! important][COLOR=blue ! important][/COLOR][/COLOR]. Due to the various formulas used, when cell E1 goes up, the value in cell A1 goes down (and vice-versa - When E1 goes down, value in A1 goes up). Cell D1 is calculated using formulas UNASSOCIATED with A1 or E1. I can't enter a formula for cell E1 to do the calculating due to the circular reference created. I need some type of code that will automatically figure out what number needs to be in cell E1 to make cell A1 equal Cell D1 without creating the circular reference.
View Replies!
View Related
Interpolation Given A Variable Table Location & Location Of Data Within
I am trying to develop a spreadsheet that will calculate a cost based on a matrix. I am attaching a sample of the calculation created so far. The end result is in cell M13 and is highlighted in yellow. I kind of layed the formula out in a few different cells, so hopefully it would be easy to follow. simplify this process with maybe another formula that I might not be aware of, or maybe show me how to get this done in VB code. I think VB code would be the correct way to go just not sure.
View Replies!
View Related
Circular Reference With Formula But No Reference To It
=INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5&"!$A:$DC"),1,),0)) Where A1= "M16" and B2= "185%RPIT630" 'Quote Detail IP'!$C$10&'Quote Detail IP'!$C$5=QxTermAge63 Can some on tell me why this is raising a Circular Reference!!
View Replies!
View Related
Store Cell Location In Variable
way to store a cell's location to variables. Something like: int a, b Cell(a, b) = ActiveCell I'm currently working with a fairly large worksheet, and I'm using Cells. Find to look for a specific cell. Then I want to Filter that column, but I can't figure out what column Selection.AutoFilter Field:=? should be.
View Replies!
View Related
Circular Reference ..
I'm trying to update a cell with today's date based on the choice from a drop-down list in the neighboring cell, but only if the cell is empty. Source list for cell B2:Rcvd Passed Returned Flagged If Rcvd is chosen from the drop-down list, put today's date in A1, but only if A1 is not already populated. Don't do anything if any of the other 3 options are chosen. I can't seem to use the A1 cell in a conditional statement to check it's status without creating a circular reference. Trying to do it without resorting to VB.
View Replies!
View Related
Locate Circular Reference
I've often lurked in the background here and have found great solutions to my problems from the ozgrid FAQ's and email outs. I have a custom VBA formula that has a heap of ranges as inputs. I loop through one range then perform calculation on the other ranges in the formula. The formula is to work on 2 sets of data, 1 with more columns than the other. I've set Optional variables for the unnecessary columns. For some reason I keep getting circular references on the 2nd set of Data. I've attached the spreadsheet (stripped down) to show both examples. I'm absolutely confused why the "teaching" one calculates correctly and "support" gives a Circular reference error. I've set the Iteration in options and this corrects it, however I'd like to know why one works perfect and the other fails?
View Replies!
View Related
Formulas (Circular Reference)
I have 2 cells which I will select on a work book; (say A1 and A2). A1 is the input cell and the formula and the result generated is in A2. Suppose I input 3 in A1 and A2 shows 60, what I also want to do is input 60 in A2, which should show me 3 in A1, without destroying the formulas. In short both cells will have formula and both cells will be input cells for each other.
View Replies!
View Related
Avoiding Circular Reference
Let's say I have a sheet called Category. Column A contains code; column B contains description. Now I have another sheet in which either column F contains a category code or column G contains a category description. I want to look up the code in column F if the description is given or the description in column G if the code is given. I thought I might be able to do something like: in F2: IF($G2<>"",INDEX(Category!A:A,MATCH($G2,Category!$B:$B,0)),"") in G2: IF($F2<>"",INDEX(Category!B:B,MATCH($F2,Category!$A:$A,0)),"") (and then copy the formulae down the rest of the columns of course). In other words, if the cells were both empty then there would be no common dependency; it would just set them both to empty. However, if I provided either value then it would overwrite the formula in that cell so there would be only one formula left and nothing to be circular dependent about. However, it must be looking at the condition itself and seeing the two columns in common because it is coming up with a circular dependency. I thought I might be able to trick it by having IF(INDEX($G:$G,ROW())<>"",... but it still doesn't work. This sort of situation must crop up from time to time. How do we get around it?
View Replies!
View Related
Change Cell Reference Depending On Date
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below. NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
View Replies!
View Related
How To Get Circular Reference To Work In 2007
I have these cells: http://img194.imageshack.us/img194/427/excelj.jpg The two center cells are the average of the top, bottom, left, and right cells. Of course the two center cells will therefore reference each other. I enabled iteration in Excel options but it will not calculate the answer. I am getting a #value error. I checked the cells, the equation is right and they are all numbers. What am i doing wrong? See the work book. I had to rewrite everything manually to get it to work (But I have a HUUUGE matrix and rewriting everything will take forever) Please look at the work book and tell me why one group of cells is returning answers and hte other is returning a #value error.
View Replies!
View Related
If Function & Circular Reference
i got three cells namely; M7, AC7 and BA7. Initially, an input value is entered in cell M7 which will be processed to give AC7. An IF function is entered in cell BA7. This IF function is really simple. It states that IF the difference between the values of M7 and AC7 is greater than 1 then, decrease 0.01 from M7, otherwise, retain the value of M7. The syntax is: =IF((M7-AC7)>1, M7-0.01, M7) As I told you, it is really simple and looks fine. I have no problem with it so far. The problem came about when I tried to make the circular reference. I am trying to make M7 to equal BA7. Well, before I made the circular reference, I made a manual entering of the values of cell M7. What I have done was that I put a value like 29.94 in M7 then I let the calculation to proceed. The the value that I get in BA7 (i.e. 29.93), I re-entered it in M7 then I repeated the process until I got a value (i.e. 29.90) that was not changing due to the use of the IF function. The manual calculations are shown in Table2 of the attached file. I said to myself instead of entering the values manually, I can use a circular reference so that the cell M7 = cell BA7. Unfortunatelly, the circulation of the values instead of giving me 29.90 which is the right answer, it gave me 29.89 which ment that Excel has done one more step further of the IF function. In other words, Excel should have stopped decreasing 0.01 from M7 when M7-AC7 was less than one. This is achieved at M7=29.90 (as clearly seen in Table2). However, Excel went one step further and done: BA7=29.90-0.01=29.89 then it stopped subtraction at 29.89 which is not correct.
View Replies!
View Related
Circular Reference When Code Runs
I'm at my wits end trying to work out why I'm getting a circular reference when this code runs: Private Sub TextBox1_Change() Cells(Rows.Count, "K").End(xlUp).Offset(1).Value = Range(TextBox1.LinkedCell).Value With Range("K5").Resize(40, 1) .FormulaR1C1 = "=OFFSET(" & Cells(Rows.Count, "K").End(xlUp).Address(True, True, xlR1C1) & ",-(ROW(RC)-ROW(R5C)+1),0,1,1)" .Value = .Value End With End Sub The circular reference cell is K29. I have an "X" in K46 after which all the values in TextBox1 are copied.
View Replies!
View Related
Circular Reference In Status Bar
How do I add the "Circular Reference" permanently to my excel tool bar? Every time i need to use this tool I have to do the following: -tools -customize -check the "circular reference" -close ..it appears and I can use it. Then after i type something in another cell it disappears again! I would like it to ALWAYS be in the tool bar as a standard item even when opening a new book/ sheet or whatever. I want it to be a standard tool.
View Replies!
View Related
Tracing A Circular Reference In Custom Formula
I'm working on creating a custom formula that loops through each row of data and performs a range of calculations when a common number appears in a field (payroll number). The formula is being applied to 2 sets of data, both are similar however 1 has a few more fields. When I use the custom formula in the 2nd dataset I keep getting a Circular Reference. I have attached a stripped down version of the whole spreadsheet, which just relates to the part causing the problems. On the sheet 'Teaching (Yr1)', the calculation works fine. On the sheet 'Support (Yr1)', the calculation doesn't and Excel prompts with a Circular Reference. I've tried using the Circular Reference toolbar to trace the predecessors, however it doesn't highlight what they are. All the inputs are blue, and none of them perform any calculations on the cell/column with the formula in it (unless I'm being exceedingly blind!) Excel gives the following message ... Originally Posted by Excel Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you. I've spent many hours (probably 10+!) debugging the formula trying to ascertain why it's happening and I'm stumped! I've run through all the named ranges, I've changed the formula and still no success. What I have noticed though, that for some reason the same formula is 'called' multiple times, in fact 3 to be precise. When I enable Tools -> Options -> Calculation -> Iteration, the formula calculates, however whenever the spreadsheet does an full calculation, it reverts back to #VALUE!
View Replies!
View Related
Circular Reference & Code Iterations
I'm looking for a macro that changes Iterations from 100 to 10. I have a circular reference that I can't seem to completely remove, so my endusers complain about the pop-up warning. I know I need to resolve the circular ref issue, but in the meantime I would like to create a button that makes it easy for an enduser to change the Iterations with one click. A secondary question is, "Am I sacrificing significant accuracy by limiting Iterations from 100 to 10? I'm dealing with $'s, so the nearest penny is good enough.
View Replies!
View Related
Circular Reference With Dynamic Named Range Used In Formula
I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem. I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column. Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.
View Replies!
View Related
Identify Circular Reference (Changing Ranges, Vlookup(), If() And Sum())
Changing Ranges, Vlookup(), If() And Sum() This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol I am building on the same topic, though. Hi All! I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!). All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end). On a seperate sheet I calculate the Income Tax payable as follows: Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned. This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15. VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) - BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out! Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook.
View Replies!
View Related
Reference Cell Based On Button Location & Determine Button Used
I received this code from an example I found once upon a time that was originally submitted by someone else. Right now the code enters the current date in a cell of the same row as the checkbox when it's checked. I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro. First of all, I don't know how to reference a Forms button in VBA. Sub Process_CheckBox() Dim cBox As CheckBox Dim LRow As Integer Dim LRange As String LName = Application.Caller Set cBox = ActiveSheet.CheckBoxes(LName) ' Find row that checkbox resides in LRow = cBox.TopLeftCell.Row LRange = "B" & CStr(LRow) 'Change date in column B, if checkbox is checked If cBox.Value > 0 Then ActiveSheet.Range(LRange).Value = Date 'Clear date in column B, if checkbox is unchecked Else ActiveSheet.Range(LRange).Value = Null End If End Sub Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:.............
View Replies!
View Related
Variable In Cell Reference
I have counted the number of rows using i. Now I need to pass that variable to the ActiveCell referances shown. Do While Not Selection. Offset(i, -6) = "" ActiveCell.FormulaR1C1 = "= round(R[i]C[-8],2)&""±""&round(R[i]C[-7],2)" ActiveCell.Offset(0, 1) = "=round(R[i]C[-7],2)&""±""&round(R[i]C[-6],2)" ActiveCell.Offset(1, 0).Select Loop End Sub
View Replies!
View Related
Use A Cell/variable As A Sheet Reference?
I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells. For example... instead of LEMONS!a1 and LIMES!B2 I would like to use REF1!a1 and REF2!B2 where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes' How can I do this?
View Replies!
View Related
Sum Across Sheets With Variable Cell Reference
i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1) Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up. However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?
View Replies!
View Related
Variable Cell Reference Based On Minimum Difference
I have a monitoring system that records a data point with a date/time stamp several times a day at random intervals. For each reading I want to calculate the change compared to the first reading that was more than 24 hours ago, which could be anywhere from 1 to 20 rows above the current one. Hence with the timestamp in col A and the value in col B, the formula in col C, for example cell C20, needs to read something like =B20-Bxyz, where xyz is the row number of the first reading that is more than 24 hours, i.e the first row xyz where A20-Axyz >1.
View Replies!
View Related
Assign External Cell Reference To Variable In VBA?
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
View Replies!
View Related
Variable Cell Row Reference In Formula/Function
I will show with an example of what I would like to do. I italized and underscored the part of the formula. ---A----B 1 24 formulaB formulaB = "= CELL("contents",DifferentSheet!G(A1))" I want B1 to contain the value from the cell G24 in sheet "DifferentSheet". I want to use the value in A1 to determine what row on "DifferentSheet" I should use.
View Replies!
View Related
Reference A File Location In A Formula
if this is possible, but is there anyway to reference a file location in a formula using another cell? EG: Cell A2 (Text) = C:Folder1Folder2Folder3Sheet v1.2.xls Cell B2 = =A2'tab1'!$A$1 So, in cell B2 it will use the file location in cell A2. I need this because the values will be updated in Sheet v1.2 and version controlled (so if it changes to 1.3 all you have to do is change cell A2).
View Replies!
View Related
Reference HPageBreak Location Address
I am trying to adjust HPageBreaks so that blocks of information are not printed on different pages. I would like to start at the pagebreak and count blank rows before the pagebreak to determine if there is enough space to print without moving to the next page. I have tried the information on the posted threads with no success.
View Replies!
View Related
Insert Row At Variable Location
I had a quick question for something that should be simple but I can't figure it out. I have a column of sorted values, it goes A A A A A B B B B B and I want to insert a row to separate the A's from the B's. But there will be a random number of A's. I can figure out how to count where the A's end. But I can't figure out how to insert a row. Any suggestions? I tried the two below but they don't seem to work. The for section is counting the A's, and it works, it's what comes next that's giving me a headache!
View Replies!
View Related
Create New Folders In Variable Location
I would like to create new empty folders from the list in column B. The number of folders will vary depending on how many entry in column B. I need the folders to be created in an existing folder in the "current directory" called "Shop_Drawings". The following code may be able to be modified. Sub CreateFolders() Dim MyFile As String Dim sDir As String Dim rng As Range Set rng = Sheets("Matdata").Range("B2") While rng.Value <> "" MyFile = rng.Text sDir = "CurDirShop_Drawings" & MyFile ''above is where I am having trouble...don't know the correct syntax'' MkDir sDir Set rng = rng.Offset(1) Wend End Sub I have attached a sample workbook. I have been getting by with code which requires changing the destination in the module whenever making folders in different diectories or drives.
View Replies!
View Related
Auto Generate A Comment Box Based On A Cell Value That Has Variable Reference Cells
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B". If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C". Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View Replies!
View Related
Creating A Reference To A Workbook With A Fixed Location But With A Changeable File Name
I'm trying take a reference from some workbooks (e.g. WB1.xls,WB2.xls,WB3.xls,...etc) They are all placed in one folder but there filename is subject to change, I need to get the result of Cells A1 from WB1.xls+A1 from WB2.xls + A1 from WB3...etc to shown on AA1 in another workbook(e.g. Collected.XLS) Is this possible knowing that workbooks filenames are changeable? Moreover Can the value of A1 from any new workbook added to the same folder be added automatically?... Can the formula typed in AA1 from Collected.xls be draged down?(So as to show the sum of Cells B1 and C1.....etc) from each workbook?
View Replies!
View Related
Multiply The Sallary To One Of The 3 Variable Depending On The Category
I'm trying to use IF with other variable but is not working. What I have: Colum G12 - NET Sallary Colum I12 - 3 possibilities (MRM, ME, AB) Colum J12 where I will have my formula. I have another table with 3 datas: MRM = 1,709 (P3) ME = 1,544 (P4) AB = 1,630 (P5) What I need to do is the following: J12 = IF I12 = "MRM" THAN G12*P3; IF I12 = "ME" THAN G12*P4; IF I12 = "AB" THAN G12*P5 I need to multiply the sallary to one of the 3 variable depending on the category: MRM, ME and AB.
View Replies!
View Related
How To Reference A Variable Stored Within Another Variable
how to achieve what I'll call "parameter driven" code where the "parameters" are variable names. For example, in the following code... I'm looking for an answer to the question posed... Sub Test_Sub() Dim vX, vY, z vX = 10 vY = "vX" For z = 1 To 3 'This next line generates a Run-time error... But... '===================================== MsgBox z * vY '
View Replies!
View Related
Variable Reference
I have a list of employees in a spread sheet. I am trying to add a macro that will add to the bottom of the list. I have managers that are not familiar with excel so I do not want them to just add lines. I have written a macro that will add lines or subtract, but I want the macro to always add at the bottom of the speadsheet.
View Replies!
View Related
Reference Variable With Value From Another
Is there any way of referring to a variable name based on the value of another variable? I have the following code: (where the value for "aux" is defined somewhere else) Dim aux As Integer Dim number1 As Integer Dim number2 As Integer Dim number3 As Integer If aux = 1 Then number1 = 1 If aux = 2 Then number2 = 1 If aux = 3 Then number3 = 1 The variables "number1", "number2" and "number3" are always filled with the same value ("1"). Is there any way of writing the "If-part" of the code in only one line? I was thinking that maybe there is a way of calling "number1", "number2" or "number3" depending on the variable "aux". For instance writing something similar to: number + aux = 1
View Replies!
View Related
Public Variable Loosing Reference
I've got the following code, setting up and initializing a Public variable for a sheet, but I've found that it's has no value after the Auto_Open is run, though it works fine in the procedure Auto_Open calls. I can get around it by reSetting it in the module I need it in, but that kind of defeats the purpose of having a Public variable: code in Module1 Public parSheet As Worksheet Sub Auto_Open() Set parSheet = Worksheets("Params") Call Initialization.BuySellInit End Sub
View Replies!
View Related
VBA Workbook Reference Via Variable
Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly. Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.
View Replies!
View Related
Reference Row With Variable As Part Of
Dim vCriteria3 As String vCriteria3 = ActiveCell.Text Sheets("CUSTOMER OVR REVIEW").Select Rows("25:25").Select Selection.Copy Rows("26:" & vCriteria3).Select ActiveSheet.Paste it hits the Rows("26:" & vCriteria3).Select and crasses with a "incorrect type" mesage. am i storing this into memory incorrectly? how do i fix it? the function is intended to use a variable to paste over x amount of rows.
View Replies!
View Related
Reference Sheets With Variable Sheet Names
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 Replies!
View Related
|
TRACKER
