Refresh Without Lossing Original Information
Jan 6, 2007
i am setting up a competency matrix and record for number of employees and when i complete the recor the information is transferred to the matrix which is in the same workbook but just on a different sheet and depending on the status of the information the cell changes colour....this is done....background info.....an employee has planned training, then they need practise then they become competent....but the problem i have is that if that employee doesnt do that task for a year then they will need a refresher but i dont want to lose the original information...so i am trying to lookup in the table and determine wwhether the task has been entered more than once and if so use the latest information if not then just use that information
View 14 Replies
ADVERTISEMENT
Sep 5, 2012
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
View 4 Replies
View Related
Jan 4, 2008
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.
I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)
View 9 Replies
View Related
Jul 27, 2006
i have an excel spreadsheet with 27 or so workeets. it contains sales figures in it.
I want to be able to link mutiple cells of this workbook to another workbook so that it retreives that data, So that when I hit the refresh (!) button it will automatically put the data in. I will recieve new sales figures (new files) on a monthly basis so i want it to be able to update the figures to the new figures.
View 4 Replies
View Related
Jan 13, 2009
I've got a spreadsheet with a few queries to extract data into my sheet. Whenever I open the spreadsheet, I get a "query refresh" dialog box, asking if I want to "Enable automatic refresh". I keep clicking on the "Enable" button but I have to keep answering the question for each open. Isn't there a way to set "yean - ok - refresh the data" so that I don't have to keep saying "yes"?
View 2 Replies
View Related
Nov 27, 2007
Will someone please tell me the difference (if there is a difference) between the following 2 lines of ....
View 6 Replies
View Related
Feb 11, 2010
I have created a userform that allows input of information and then deposits the information on a specific sheet. I am looking for a way to have that information not only deposited on the specific sheet it is already set to but also to another sheet based on a selection made from a combo box.
here is my current
View 5 Replies
View Related
Apr 6, 2013
Basically I am trying to create a worksheet in which everytime I input information into Sheet 1, it is copied into Sheet 2. I want to have each entry in succession on Sheet 2 such that my first entry would be on Row 2, second on Row 3, third on Row 4, etc. However, everytime I put something new in Sheet 1, it just overrides the information in Sheet 2.
Basically I type in ticket sales in sheet 1, it calculates the prices and keeps a transaction log in sheet 2. But everytime I do a new ticket sale, it just overwrites the previous transaction witht he new transaction information. I've pasted my VBA below:
Sheet 1 VBA:
Code:
Option Explicit
'Form level variables - used in more than one event
Dim intAdult As Integer
Dim intStudentSenior As Integer
Dim intBalcony As Integer
Dim intChild As Integer
Dim sngAmountDue As Single
[Code] .......
Sheet 2 code:
Private Sub cmdSummary_Click()
'Declare Variables
Dim intCount As Integer
Dim i As Integer
Dim intAdult As Integer
Dim intStudentSenior As Integer
[Code] ......
View 3 Replies
View Related
Aug 21, 2014
I'm setting up a payroll for several employees in one workbook. I start from the particular employee's worksheet and then I need to go to another sheet where various information is stored and then come back to the original employee's sheet. The current macro works fine if the various information is stored on each employee's worksheet but I can't figure out how to go to another sheet and then come back to my active cell in employee sheet.
[Code] .....
View 14 Replies
View Related
Apr 22, 2009
I have a tracking log, stored on a network drive, that tracks open purchase orders on about 13 different buyers. Each buyer has access to this data, and would be interested in looking at it in different ways. I have an idea for protecting my original data in this workbook, but allowing others to view and edit the data anyway they want. I thought I'd run it past the pros first, to see if they knew a better way, before I started trying to code it.
My idea was to put a macro in the workbook open event that opens an input box, "Enter password or select okay." If they enter the correct password, the macro exits, and they are in the workbook. Any changes made after entering the correct password will be made to the original data. If they enter anything OTHER than the correct password, the code would save the workbook AS another name, so now any changes made would be only made to a COPY of the original data.
This way, when I do my daily updates, I can open the file using the password, and save the file after editing. Anybody else would only get to edit a copy of my original. The other question is..., is it possible to put a macro in the workbook exit event that requires a password to overwrite the original? Otherwise, the user could edit the data in the "saved as" version, then just overwrite the original file, either accidentally or maliciously. I know they could still overwrite it by editing the macro, but I don't think they're that savvy.
View 5 Replies
View Related
Jan 19, 2010
I'm using the function Proper(A1) to clean up some columns that have some entries in all CAPS. It works fine. What I'd like to do now is get rid of the original column and just keep the altered one. Of course since the original is the source for the new one, when I delete it the new one goes nuts.
View 3 Replies
View Related
Apr 12, 2012
I have two issues:
1, How do I calculate the original value of something if it has dropped say 9.3%
Ex Stock value 5.56 down 7.4%. How do I calculate its original value.
2, I have copied some numerical data from a website, on one computer when I drop that data into an excel sheet it falls into separate cells allowing me to manipulate it, on another computer I drop it into an excel spreadsheet and all the data goes into one cell not allowing me to manipulate it.
I was wondering is there a setting on one excel spreadsheet that I am inadvertantly not using on the other.
View 4 Replies
View Related
Apr 27, 2012
How do I work out the parts scanned value from a percentage. Example below.
Overall99.75%Total Stock 27194Parts scanned??
View 2 Replies
View Related
Dec 2, 2008
I have a range b2:g37 filled with numbers. I use max function to get the max number in this range in cell C41. For example, the number is 20400. Now I want to know where the 20400 is originally located in the spreadsheet (ie. in column B, C, D, ...? in row 2, 3, 4, ...?).
View 9 Replies
View Related
Jun 21, 2006
I am writing a macro that copies a pdf from a temporary directory to a permanent directory. After the copy is complete, I want to delete the original file. Looking in Visual Basic Help, it appears that I need to create a FileSystemObject, but I have no idea what that means or how to do it. sDirectory is a string variable of the path of the file and sBatch is a string variable of the name of file. This isn't working.
Set fs = CreateObject("Scripting.FileSystemObject")
sDeleteFile = (sDirectory & sBatch)
Set a = fs.CreateTextFile(sDeleteFile, True)
a.DeleteFile.sDeleteFile
View 4 Replies
View Related
Mar 16, 2007
I recorded a macro in a workbook called masterschedule. The macro works only if I run it when I am using the masterschedule. Each week I open the masterschedule workbook and name it the current week, for example 3-26-07 schedule is the name of the most current schedule. I then open 7 other spreadsheets and paste information from the current schedule 3-26-07 (this week) onto 3 different sheets in all of the other spreadsheets. It works fine if I am using the masterschedule. Others have access to this workbook once it is named something else. I do not want to allow others to have access to the master workbook. Can someone help me with this? I've attached part of the macro below. I need it to work in whatever the masterschedule is renamed to.
Windows("MASTERschedule.xls").Activate
View 9 Replies
View Related
Sep 18, 2009
When i cut a row and paste it to another sheet, that row on the original sheet is shrinked. I don't know if my vba code has something to do with that
so just in case will put it here:
View 2 Replies
View Related
May 7, 2013
How does one do a save with a new name while keeping the original file intact - with the original file name? Would a macro be required?
View 1 Replies
View Related
Jun 26, 2013
I would like my hyperlink destination to move with the original cell (like links do) if I insert or delete a row above. The destination is on a separate sheet within the same work book.
View 3 Replies
View Related
May 25, 2014
My worksheet that will have duplicate data inserted into it. Once inserted I need to delete the new duplicate row and the row it duplicated. The attachment is a copy of what the data looks like with the first tab showing what it will look like with inserted data and the second tab is what I want it to look like at the end. I will not need the deleted data again so it does not need to be on a new sheet, that is just for an example. The name of the tabs will also be different so I want to be able to run it on any worksheet. This is the code I am using to find and delete the duplicate but I can't get it to delete the original. I used "First, MI, Last, and DOB" because those are the ones that won't be duplicated where others will. This is a list of about 15,000 entries. There should never be an incident where there is triplicate data but I can't say for sure.
[Code] .....
Attached File : Practice.xlsx
View 2 Replies
View Related
May 26, 2014
I am having problems with conditional formatting,on sheet1 ,if column "n" is yes then it is coloured red,when I search database say f124 I would like all records for f124 to appear and keep column "n" red or even better the whole row red,it seems just to locate all records with "n" =no and all orange /brown colour
View 6 Replies
View Related
May 1, 2008
If I ask in a macro to open sheet containing a fax header, copy it, then how do I tell Vb to return to the original sheet to paste. There many sheets that require this from a button click on the sheet and instead of storing the graphic in all sheets I just want 1 copy so file closes faster. I would normally just name the original sheet, but this will change with every sheet.
View 13 Replies
View Related
Oct 15, 2012
I need to copy the sheet being worked on, and place it behind the original sheet.
This is going to be in a Macro enabled template that other users will be rename when they save it.
It will be activated by a button on the original worksheet other users may need to rename the worksheet before copying so I am using ActiveSheet.Copy I don't want to put the sheet after a counted sheet, because other sheets may be inserted before the one being worked on. I am not proficient at VB, I basically search for a macro that does what I need and copy it.
This is what I am working on.
Sub CopySSR()
'
' CopySSR Macro
'
'
ActiveSheet.Copy After:=Sheets(7)
End Sub
View 2 Replies
View Related
Aug 27, 2008
I have VBA program that collect the data from database and make a report.(I will mention I open the browser to look for database )
Here is a problem:
My original file is " Daily Report.xlt(template) " .At the begin when I open this file ,the file name appear as a " Daily Report1 "...This is not big deal
When I run macros I need to keep some info on the original file.
After I done, I have following code to save as " Daily Report.xlt " .
but the actual problem is ;the code save this file on the database path that I don't want it.Actually I want to overwrite on original file to keep that file up to date.Here is code
ActiveWorkbook.SaveAs Filename:= _
"Daily Report.XLT", _
FileFormat:=xlTemplate, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Note:I know I can add the path in front of the file name and save it in the that directory .Different customer different path I can not use fix directory.
View 9 Replies
View Related
Sep 18, 2008
Within Worksheet_BeforeDoubleClick, I have the following simple code extract that sets the appropriate autofilter to the correct value (and highlights a couple of cells for clarity) when a particular cell is double-clicked...
If Not Intersect(Target, Range("C10:AQ11")) Is Nothing Then
If Not Intersect(Target, Range("C10:D11")) Is Nothing Then
Selection.AutoFilter Field:=10, Criteria1:="C"
Range("C10:D11").Interior.ColorIndex = 36
End If
You'll be happy to hear that the event works as expected.
However I would like to add the following enhancement: after Worksheet_BeforeDoubleClick completes the "double-clicked cell" is selected, is it possible for the "original" cell to be re-selected once the event completes?
View 9 Replies
View Related
Oct 2, 2008
I need to know how to select the sheet the user was on at the time they ran the macro. The macro has to select (because I'm not yet smart enough to avoid all the selecting...) cells on other sheets, but I want to return to the sheet they started from at the end of the macro.
Unfortunately, they create these sheets themselves, and I have no idea what they will be named, or where they will be, or what their code numbers will be. They could delete them at any time and rearrange.
Sorry if this is a really dumb question. I suspect it is, but I can't find out how to fix it, and most people are smart enough to avoid the selects so I suspect it isn't an issue for them.
View 9 Replies
View Related
Dec 21, 2008
I am looking to create a macro button which will reset the sheet to its original state.
I have locked the cells users should not imput data into, and unlocked where they add their data.
At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.
I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.
I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.
As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.
View 9 Replies
View Related
Mar 21, 2008
I don't believe this is possible but I've been wrong before.. Is it at all possible to Kill the current workbook using VBA? I " saved as" in another location and do not wish to keep the current workbook.
View 3 Replies
View Related
Apr 20, 2008
I was wondering if it is possible to have a macro running that will automatically save a .csv file to specific location and close the file. I would like to have the macro running so that as the files are opened they will be automatically saved to this location.
View 2 Replies
View Related
Mar 18, 2014
Our Finance office created a spreadsheet with pivot tables. Attached is the file. In the Presentation tab, using the filters, values will be changed. Once changed, they want a way to reset the filters to their original settings. They, and I, are having no luck with this.
View 5 Replies
View Related
