Ensure Code Acts Upon Correct Sheet
Sep 19, 2007
I have a workbook that starts off with the following sheets:
MAIN_PAGE - Where filter criteria is selected via checkbox and PLOT button
RAW_DATA - unfiltered raw data
After running a series of macros tied to the PLOT button, two new sheets are created:
FILTERED_DATA(HIDDEN) - where the filter criteria from the MAIN_PAGE is applied, and the filtered data is stored.
CHART - The plot of the hidden FILTER_DATA sheet.
Every time the PLOT button is pressed, the the macro searches for the FILTERED_DATA and CHART sheets, and if present, clears and overwrittes with the new appropriately filted data. This works and suits the user's needs 95% of the time. However....
QUESTION
If the user decides to keep the old CHART and FILTER_DATA as well as the new FILTER_DATA and CHART, I need something to differentiate the two charts and data sheets. My thought was to have the user rename the CHART. The problem is that because the FILTERED_DATA sheet is hidden (and has to stay that way), how can the name change of the CHART be carried over onto the hidden FILTERED_DATA sheet, therefore keeping the plot and it's data in tact? For example, if I append the name CHART with a 1 to make it CHART1, how do I automatically change FILTERED_DATA to FILTERED_DATA1?
View 7 Replies
ADVERTISEMENT
Mar 21, 2008
I am trying to write some code that is linked to a Command button. The code in the command button is in my workbook called "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" but then I have another workbook called "MF BANK EXPOSURE SUMMARY.xls" that I want to do some work with - namely delete blank columns and it is here that I am having the problem.
My problem is this: my code module is contained in my project "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" and the columns that I want deleted are in the "MF BANK EXPOSURE SUMMARY.xls". Now even though I believe I have activated the "MF BANK EXPOSURE SUMMARY.xls" workbook the action, that of deleting the blank columns is performed on the "MF Consolidated ACTUAL DAILY REPORT - Dev.xls" workbook. Why?? Here is the code that I am using:
Sub Commandbutton()
'ASK FOR DATE AND SET IT IN THE REPORT
Workbooks("MF Consolidated ACTUAL DAILY REPORT - Dev.xls").Worksheets _
("Seg and Non Seg Bank Summary"). Range("I1") = Application.InputBox("PLEASE ENTER REPORT DATE IN THE DD/MM/YYYY FORMAT")
'ACTIVATE MF BANK EXPOSURE SUMMARY
Workbooks("MF BANK EXPOSURE SUMMARY.xls"). Sheets("Seg and Non Seg").Activate
Dim iCol As Integer
Dim Isheet As Integer
With ActiveSheet.UsedRange................
View 3 Replies
View Related
Sep 14, 2007
I would like to know whether its possible to fix a sheet to always be the second sheet in a work book no matter how many other sheets are added. Currently I have set up macros to add subsequent sheets before the last sheet, so that Sheets("Number 2") remain sheets(2). Is there some way I can lock the first two sheets so that when I reference them in my code as sheets(1) and sheets(2) it will reference the right sheets. Right now I have it set up so that Sheets("Number 2") can be renamed by the user, and data inputted on this sheet, but I require that the user not be able to move this sheet and the sheet preceding it.
View 5 Replies
View Related
Aug 14, 2008
I have written some code that copies certain cells from a spreadsheet constructed form back to a master spreadsheet database. The idea being to prevent users of the form from accessing the database and mucking it up. I write it the long way round and am now trying to modify it to not have to open and close the database everytime BUT whenever I change it I get errors that I think relate to the code not understanding the change of object...
' 3. send updated data to the database (all yellow boxes will update)
' set parameters for cells to copy from
Dim r As Long, e4 As Long, e6 As Long, e18 As Long, e20 As Long, e22 As Long, e24 As Long, _
e26 As Long, e28 As Long, e30 As Long, e32 As Long, e34 As Long, e36 As Long, e38 As Long, _
e40 As Long, e42 As Long, e44 As Long, e46 As Long
r = Range("D2")
e4 = Range("B4")
e6 = Range("B6")
e18 = Range("B18")
e20 = Range("B20")
e22 = Range("B22")
e24 = Range("B24")
The little blocks of code go on for 17 open/closes!
View 4 Replies
View Related
Oct 26, 2008
I use excel 2007 and have a s/s running to 350,000+ rows.
Data is in ranges of 2 to 30 rows with blank row between each range.
Column F numbers the rows in a range (if row 120,000 begins new range then F1200,000 = 1, F120001 = 2, etc).
If there are 2 rows of data in a range then, including blank row, there are 3 rows between this and next range.
What I want to achieve is a minimum of 6 rows between each range.
Does anyone know code that can can achieve this end?
View 3 Replies
View Related
Jan 15, 2009
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
View 13 Replies
View Related
Nov 7, 2008
I have a userform with several fields located in it. For the most part, it looks like a user information form, Name, Date,, stuff like that. There is a field that the user enters some information, and using the afterUpdate function for the field, it searches to see if the value is unique. The problem is that when I tab from that field, if the value is not unique, I want the cursor placed back in that field (textbox). SetFocus doesn't seem to do what I want, unless I am using it incorrectly.
View 9 Replies
View Related
Mar 31, 2009
I am having a problem with this script below. I think it is in the way the worksheet is picked. Can someone take a look and offer some advice on how to pick between the 2 sheets? I'm getting an error on this line in red below( object doesnt support this property or method)
View 14 Replies
View Related
Jun 15, 2009
In Excel is there a shortcut key the replaces using your mouse to double click to get into the cell?
View 2 Replies
View Related
Mar 20, 2013
In an excel sheet, I am using a macro to filter. Macro code is as follows. When I run this macro, it returns 4 rows matching the criteria from 560 rows in the excel sheet.
VB:
ActiveSheet.Range("$A$1:$I$566").AutoFilter Field:=1, Criteria1:= _
">=11/11/2012 22:13", Operator:=xlAnd, Criteria2:="<=11/12/2012 6:47"
I want to use the same criteria in the vb.net code, to do same thru vb.net. But, when I run the same code in vb.net, it just returns only 1 row (1st row only).
VB:
[FONT=Consolas][SIZE=2][COLOR=#0000ff]xlsRange1.AutoFilter(Field:=1, Criteria1:=">=11/11/2012 22:13", Operator:=Excel.XlAutoFilterOperator.xlAnd, Criteria1:="<=11/12/2012 6:47")
This returns 1 instead of 4 expected.
View 2 Replies
View Related
Jul 6, 2014
Drop down list containing 5 stages of a typical sales cycle, i.e. client engaged, negotiation, prospect, Short-listed, Win/Close.
These are locate in column B and each cell in column B has a drop down list with these 5 options.
Now, based on which option is selected the corresponding cells in column C and D need to change. This change is defined as follows -
Cells in column C, need to change colour ( i.e. blue, orange or red)
Cells in column D, need to change to a percentage value (i.e. at intervals of 10%, 10%,20%, 30% ...etc to 100%)
Column C (corresponding cell i.e. if B3, implies change to C3 respectively). In this case cell colour needs to change to one of three colours listed above.
i.e. if cell in B2 has client engaged, C2 must show blue, Similarly if B2 has short-listed, C2 must show red.
Column D, Equally, depending on colour in Column C, the percentage must automatically populate.
0-30% = Blue
31-50% = Orange
51-100% = Red
I have used conditional formatting options on a trial and error for testing the above, but not getting much progress. I suspect due to the multiple changes and different simultaneous changes i.e. colour and text, a Macro is most likely needed? (I have limited knowledge of VBA).
In closing there are about 200 rows that need to have this capability. I.e. Contents of column B trigger respective cells in Column C and D.
View 7 Replies
View Related
Jul 14, 2009
I want to be able to have a link that uses cell data as the passed function parameter!
I want to do this so I can simply copy the formula down for new rows and not have to create a command button for each row?
Maybe there is an entirely much easier way to do what I want?
FOR EXAMPLE, the 1st column would be a link to an entry form passing the 2nd column which would be an ID field.
View 13 Replies
View Related
Feb 18, 2014
The code below works correctly on certain sheets. The code is supposed to loop through worksheets in an array, calculate the percent change from 1990 to 2012 and from 2005 to 2012, and put the calculations on the 4th and 5th row from the last non-empty row, respectively. All the sheets are identical except for 3. The sheets that are different only have a different number of years of data. For some reason this causes the macro to put the percent change calculations in random rows below the correct location. Also, the macro doesn't work correctly on one of the identical sheets.
[Code] ......
Example of how the macro runs correctly on an identical worksheet : correct.gif
Example of how the macro runs incorrectly on one of the 3 sheets that are not identical : incorrect.gif
View 6 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Dec 10, 2012
I have a macro which copies one cell into another on a different sheet, provided it has content.
I want to change it so that it copies to the different sheet but puts the value in the correct row of the other sheet.
For instance sheet 2, A1 =TEST, A2= TEST2, A3= TEST3
Sheet1. B7 = TEST2, A15= This is a test
I want the macro to copy the value in A15 and copy it to B2 on Sheet2, as this is the matching row
This is what I had originally:
VB:
If Range("A15") = "" Then
Exit Sub
Else
Range("A15").Select
Selection.Copy
[Code .....
View 2 Replies
View Related
Jun 3, 2014
I have a list of Grades (Job Description). Each of them is allocated a SCH No = SCH1 / SCH2 etc
I need a macro that will go through this list and copy the Grade into the correct SCH Column on Sheet List2.
The columns in List 2 then need to be sorted A-Z.
AddNamestolist.xlsx
View 5 Replies
View Related
May 29, 2009
I wrote my program with several user forms and private subs. All the results was going to my Report sheet. However, now, I want to be able to run those userforms in another sheet. All the results are coming up in this selected sheet. Is there a way that I can program to for all the results to go to the report sheet without going back and fixing every private sub?
View 3 Replies
View Related
Oct 21, 2011
We have a form (an Expense Sheet) that has the date entered in the "Monday" cell using the =Today() formula (the rest of the week uses =Today()+1, =Today()+2, etc).
I am betting you already see the problem...this only works if you open the form on a Monday....before I point out the error to the document owner I thought I better have a solution...
So...what is the best way to self-populate the "Monday" cell with the correct date for the Monday of the week?
View 2 Replies
View Related
Jun 20, 2006
Here is what i want to do. # I have a workbook say 'Template', which has two columns: ColA -> Names , ColB -> Value, his acts as a template, i.e a budget. # Another workbook 'sample' which has similar ColA(may contain additional rows) and corresponding value in ColB.
Here is what i need::
I need to copy the contents of ColA and ColB of 'sample' wholse ColB valules are greater than ColB values of 'Template' for corresponding ColA values in both, into a new workbook. SO basically, template acts as a budget check for the sample workbook, and if any value in ColB goes above the budget in template, it will show up in the new workbook. I have attached both the workbooks. Basically the red highlighted cells in Sample are the ones i want in a new workbook.
View 7 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Mar 14, 2014
The text disappears when I want to correct it (protected sheet). see the discription of the problem in the attachment with figures.
View 3 Replies
View Related
Jul 19, 2012
I have a form with a number of fields that once submitted paste the data in the next blank row in a sheet (as below). A user will input the date of the week commencing in a text box on the form (Calendar1) and a number of days in another box (txtSupp). I need it to submit the value of "txtSupp" in the same row on the sheet under the right date of the week commencing based on date in "Calendar1". Rows L1 to EJ1 of the sheet have the date of the week commencing starting from 02/04/2012. E.g. L1 = 02/04/2012, M1 = 09/04/2012 etc.
Private Sub SubmitForm_Click()
ActiveWorkbook.Sheets("Data").Activate
Range("A1").Select
[Code].....
View 1 Replies
View Related
May 22, 2009
I have two worksheets in one document. On sheet 'M_Admission' there are numbers for each week. can those numbers be automatically copied to the correct cell on sheet 2 'M_Actual' See the attached Excel worksheet to see what I mean. (Excel 2002)
View 3 Replies
View Related
May 17, 2014
Trying to assign this cell's value to the correct sheet tab for the current month.
Trying this:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0),IF(MONTH(A3)=6,'[Wholesales 2014.xlsx]Jun'!$P$1,0)
A3 is todays date. Wholesales 2014.xlsx]May is a seprate sheet for the month of may and so forth for june.
The problem is the p1 value is empty for the Jun sheet so I get an error: #VALUE! (I assume that is why I am getting the error?)
Using:
Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0)
By itself works just fine. I need to set this up for future use, though.
View 3 Replies
View Related
May 13, 2013
Here's the data table being referenced
Rank
Week Ending
Name
Value
1
1/1
Apple
100
[Code] ........
Now on another sheet, I want to return the top two 'Name's and their values like below:
Name
Value
There is a fluctuating amount of rows in the first table, too. So what formula can I use to return the correct names and values on another sheet?
I'm thinking it will use some form of concatenate for the first and use a sumifs function for the value column..
View 1 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Feb 2, 2010
My form has a combobox with three options "Withdrawal" "Deposit" "Fee". I want to make sure that whatever number a user puts into a textbox, if they select "Withdrawal" or "Fee" that number will be converted to a negative number, and if they select "Deposit" it will be positive. I have written the following code and am just wondering if there is some super slick way of doing it other than an if statement.
View 2 Replies
View Related
Jul 15, 2006
I am trying to ensure that only alpha numeric data (one letter then 4 digits) is entered in a cell.
View 9 Replies
View Related
Oct 18, 2007
I enter for example in a cell : 0625-C0/01
sometime 0 is entered as O a letter and not zero.Is there a way to ensure that only 0 (zero) is allowed in that string using data validation rules.
View 6 Replies
View Related
