Excel 2003 :: VBA Code That Sorts And Deletes Data
Jan 19, 2012
Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.
I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?
I am using Excel 2003.
Code:
Sub SaveData_Test()
'---------------------------------------------------------------------------------------
' Procedure : SaveData_Test
' Author : Julie/Cecil
' Date : Fri, 1/13/12
' Purpose : Modified Julies code to capture date entries
' associated with hours logged per category.
' Check notes attached to "sOp" string comment
' for additional details.
[Code] ..........
View 5 Replies
ADVERTISEMENT
Mar 27, 2014
What each piece of this code means and what it is doing? The more details the better
[Code] .....
View 1 Replies
View Related
Aug 11, 2006
I’ve got a macro that automatically starts when the workbook is open. The macro finishes with
Application.Dialogs(xlDialogSaveAs).Show
The document will be always saved with a new name. As I just need the macro once when the workbook is created I’m looking for a code that deletes the macro before the workbook is saved. I tried to record a macro while I was deleting another macro, but apparently it is not working (the recorded macro is empty).
Does anyone know if there exists a code to delete a macro?
View 5 Replies
View Related
Sep 13, 2006
I am trying to parse and remove unwanted rows from a very large text file using At the moment, the application runs rather slow and was wondering if the experts could give some pointers on how to make the code more efficient while still keeping it simple so others may be able to modify later. I am keeping the ScreenUpdating True as the alternative false will just show Excel as Not Responding to the user until the VB is finished.
Sub deleteReplaceRows()
Application.ScreenUpdating = True
Dim DeletedRows As Integer
Dim lastRow As Long
Dim Arr(7)
Arr(1) = "<PUZZLE>"
Arr(2) = "<%"
Arr(3) = "%>"
Arr(4) = "Response."
Arr(5) = "</PUZZLE>"
Arr(6) = "<HINT>"
Arr(7) = "<MESSAGE>"
For i = 1 To 7
Do
Set rng = Columns(1).Find(Arr(i))
If rng Is Nothing Then Exit Do
rng.EntireRow.Delete
DeletedRows = DeletedRows + 1...................
View 2 Replies
View Related
Sep 8, 2007
I have a formula which sorts data so all data that has EUSTON in column C will be shown first and then then all data which has EUSTON in column D once its been sorted.
=IF(C1="EUS",0,1)
What im trying to do is to be able to add BLETCHLEY so it does the same thing.
View 9 Replies
View Related
Mar 22, 2012
I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.
It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".
I'm using Excel 2003.
View 5 Replies
View Related
Apr 24, 2012
Why am I getting a runtime error on the following code:
Code:
If Target.Address = "$E$5" And Target.Value "" Then
Range("E5:G5").Copy
If Sheets("Sheet5").Range("F5").Value = "" Then
Sheets("Sheet5").Range("F5").PasteSpecial xlPasteValues
Else
Sheets("Sheet5").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If
When I select only one cell it's ok but when I select multiple cell to delete it sends me a run-time error '13'.
Excel 2003, sheet is protected.
View 2 Replies
View Related
Feb 3, 2013
How to amend existing code in a workbook that is used by many different users. I am working in Excel 2003. The existing code copies data from certain cells in the active worksheet (which is password protected) into a new worksheet and saves that new worksheet to a temp folder, sends it to a particular email address using outlook and closes it, returning the user to the first workbook. You will see that it also unhides then rehides columns of the first worksheet during this process so that the user never has to see them. Problem here is that none of the users are using outlook anymore and I would prefer that this new spreadsheet that is created just be saved to the users desktop. I have found bits and pieces of what I needed on the web, but I can't seem to put it all together (I usually end up being sent to the debugger just short of having the newly created spreadsheet saved to desktop). Here is the existing code:
Private Sub CommandButton1_Click()
Range("A2:G2").Select
Selection.ClearContents[code]....
Essentially what I need to do is circumvent the whole email bit and save the spreadsheet that is created to the users desktop instead of a temp folder.
View 2 Replies
View Related
Jul 30, 2014
From an Excel 2003 workbook I generate some product labels. Each label has a 5 digit job number and a quantity that are passed to variables. If the quantity is 1, then I have no problems in printing my label. My problem is where the quantity is greater than 1. If a job has more than 1 item (can be up to 40 or 50) I need to add a series of letter (or letters) to the end of the job number.
For example:
Job number 12345
qty 1
number on label 12345
Job number 12346
qty 4
numbers on labels 12346A, 12346B, 12346C, 1234D
so that each printed label has a unique job number
View 3 Replies
View Related
Mar 26, 2012
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click()
Dim iDdSortVal As Integer
Dim CurrSheet As String
Dim lFirstSectionRow As Long
Dim lLastSectionRow As Long
Dim strCol As String
Dim lRptHeaderRow As Long
Dim iRptMeasColumn As Integer
CurrSheet = ActiveSheet.Name
[Code] .......
View 1 Replies
View Related
Nov 13, 2012
I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:
I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.
Branch
S&C Group
77770000583-DOWNERS GROVE BRANCH
MWBF2
77770000585-CHICAGO RIVER BRANCH
MWBF1
77770000587-WEST TOWN BRANCH
MWBF1
[code].....
In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.
View 4 Replies
View Related
May 8, 2013
I have produced a basic search/lookup facility on an Excel workbook that simply allows the user to enter a post code and some details will be returned using a vlookup. The document is going to be rolled out to a number of operational users so I want to basically 'lock down' everything I can in the document (basically everything except the data entry cell) and make it fool proof- I have locked all cells apart from the data entry cell and have made the file read only.
The only issue I am encountering is when the cursor is in the one 'unlocked' cell (i.e. the one the users will enter the post code into); it appears that you can break the document. For example, when the cursor is in the 'unlocked' cell, I am able to go to Tools > Options and change various settings including cosmetic colour changes but also cell calculation which breaks the lookup functionality. This is probably enabled as the cell is unlocked, but if I lock the cell, when I protect the document, it disables data entry!
if there is another way of providing this one cell for users to input data into for the vlookup to work whilst locking down the rest of the document to ensure that no-one can break it?
View 1 Replies
View Related
Aug 29, 2008
I've created a workbook with code to email it to a reviewer then back to the originator when reviewed (along with other editing functions etc.). The problem - The originator is working in Excel 2003, the reviewer in 2007. The macros work great until the reviewer sends it back. I have not been able to figure out which FileFormat:= ???? to use when I SaveAs prior to emailing as an attachment. My Excel 2003 doesn't recognize the .xlsm file and other formats are eliminated the vba/macro coding
View 4 Replies
View Related
May 12, 2012
I have two separate worksheets:
I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)
Referral
A
B
C
[Code]....
way to do the calculation using Excel 2003
View 9 Replies
View Related
Jun 1, 2009
I am trying to do here is select a value from a drop down which is linked to a formula which triggers the macro shape..i have all that down already... but the problem is that I will have multiple values in the same drop down and I needed to figure a way to delete the previous macro shape in that range. And so i have created the delete all shapes code below but it seems whenever I use the code it deletes the data validation drop downs
here is what I have so far...
Function Macro()
'
DELETEALLSHAPES
'
'
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 220.5, 105.75, 92.25, 51#). _
Select
End Function
Function CIRCLES()
'
DELETEALLSHAPES
'
'
View 9 Replies
View Related
Nov 28, 2011
I have five columns of data: A & C are time measures; B & D are values associated with each time measure; and E is a matching value column that I want to populate. I am trying to match times and place in col E the Val 2 that matches times 1 and 2. Though many matches lie on the same row, there are many that don't (the real data uses several thousand rows). For example, Val 1 in cell A5 (100) is associated with cell C7 to find a matching value of 4.5 in cell E5. Note, occasionally, there will be replicate values for Val 2 (like cells C9 & C10). I need to match the one further down the list.
Excel 2003ABCDE1Time 1Val 1Time 2Val 2Match203.902.72.73484.7482.12.14545492.33.351006543.34.562003554.472192.71004.57.782015.592196.6102197.7Sheet1
View 3 Replies
View Related
Jul 1, 2014
I have inherited a number of databases in work (running Office 2003). It has quickly become apparant that a vast amount of work is duplicated and so i am trying to cut down the data input and therefore the possible errors.......
I have narrowed most of the work down and now have a major worksheet (is that what you call a complete Excel file) named "master database" and several over minor files....
Currently what i am trying to do is to get one of the minor files to auto populate an area of the master database. I will try to explain it below...
1. Minor database has 2 columns with data i require to auto populate the master database. (1 column (B) is called 'off', the 2nd column (C) is called 'on').
2. A number will be inputed manually into either 'B' only or 'B and C' columns, depending on the criteria of the job..
3. The criteria of the job is dictated by column (Z) where the text 'A' or 'ATL' is inputed
4. The master database i would like to add up the numbers inputed as a total from columns 'off' and 'on' and place them into seperate columns 'E' and 'G' of the master database.
5. IF column (Z) shows 'A' then only column (B) 'off' is to be calculated and put into the master database at column (E)
6. IF column (Z) shows 'ATL' then BOTH columns (B and C) 'on' AND 'off' are to be added together and column (G) populated on the master database.....
To make mattters more complex. An expiry date is shown on the master database at columns (D) and (F).
IF column (E) does not exceed 12 by the expiry date, i would like the cell (D) to turn red
IF column (G) does not exceed 10 by the expiry date, I would like the cell (F) to turn red
View 14 Replies
View Related
Sep 20, 2013
I am trying to set up a spreadsheet in Excel 2003 that pulls data from a lot of different websites and formats it all for me. I have managed to do this with a few websites but there are two which I'm having problems with.
This is the table I am trying to get, but when I do a manual web query and select the table and click OK, it says that no data has been found.
[URL] ...
View 1 Replies
View Related
Apr 11, 2013
Data in cell A1 - 1) A Q
Data in cell A2 - 12) V
Data in cell A3 - 131) B H L
How do I throw away the #) and the following space in cells A1, A2, and A3 and save the rest?
Excel 2003. ie. no VBA.
View 4 Replies
View Related
Nov 24, 2013
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
View 1 Replies
View Related
Jan 18, 2014
I work in a call centre and I record my daily figures. My target is 82%, and I would like a bar chart in Excel 2003 to colour the chart according to wether I hit target or not. If my daily figure >=82% the bar should be green.
View 9 Replies
View Related
Jan 16, 2014
I have Excel 2003
My spreadsheet has multiple lines of data for multiple items (have a specific item #) and I need to be able to enter an item # on a different sheet so that the requested information can be pulled from the data source. The items have information on multiple rows. I need to have all of the rows pulled into my output file when I specify the specific item. #.
I have attached a spreadsheet showing the type of data is contained and what my desired output would look like.
View 1 Replies
View Related
Mar 20, 2014
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
View 1 Replies
View Related
May 2, 2014
I have two Spreadsheets on Excel 2003, one provided by a service supplier (i), and one from my own system (ii).
I need a macro attached to spreadsheet ii that looks for data matches between the F column of my spreadsheet, and the C column of the suppliers. Where data matches (i.e. if cell C42 in spreadsheet ii is the same as cell F23 in spreadsheet i) it transfers the contents of the A column of spreadhseet i and places it in the corresponding cell in the C column of spreadsheet ii.
Example 1 : In spreadsheet i cell C20=BN213YD. In spreadhseet ii cell F55=BN213YD. Therefore, the macro takes the contents of cell A20 in spreadsheet i and places it in cell C55 of spreadheet ii
Example 2 : In spreadsheet i Cell C75 = TN142XS. In spreadsheet ii this data is not found. The macro therefore takes no further action.
Basically, the suppliers spreadsheet has less information per row, but more entries overall. My spreadsheet is lacking a lot of suppliers references, but has more comprehensive information elsewhere. Until now i have been doing a manual search of the postcodes between the spreadsheets and rectifying them manually, but this is taking longer and longer as the spreadsheets grow.
View 1 Replies
View Related
Jun 10, 2014
So essentially I need a VBA Macro code for Excel 2003 that can take raw data that is input every month via a text document and move it to the next sheet in the workbook. This data is essentially broken down by a "platform" in the raw data section ("P") with monthly totals for each month but it is broken down by platform in total which goes in descending monthly order on the "dat" tabs (which is the final place for the data).
So what needs to happen is the data for the last two years must go from the "P" tab to the "PDat" tab under the correct platform and the correct spot for the newest month. The data that is input each month is in the same format month to month, just starting two rows down from the last month.
At the end of two years, the oldest year of data must be deleted and the other data moved up to the vacant area so that the sheet never grows too large (i.e., at the end of this year 2012 will be deleted and all the data will need to get moved up).
Attached is a sample data workbook.
View 3 Replies
View Related
Jan 30, 2010
I have a notepad with lot of information's but when i try loading those info's to excel I am getting message "File Not loaded properly"
Is there a VB code which downloads it to excel and automatically moves to next tab and paste the rest.
View 8 Replies
View Related
Mar 31, 2010
I'm using Excel 2003
Cell D7 - data validation reads as such: It is a List and the source is I6:I7. I have In-Cell dropdown selected. Ignore Blank is not checked.
I now password protect the sheet and only D7 is unlocked. real simple, the person using it can only use D7 and select one of two in the drop down menu.
The problem is, if the user clicks on D7 and hits delete, the cell goes blank. How can I avoid this?
View 8 Replies
View Related
Dec 30, 2011
I have a worksheet with data in it. The data could be in any column or row. I am looking for data which contains the numbers 01. If I find such data, I want to move all the data in that particular cell it to column A.
My code(which is not working) is below. I'm using Excel 2003.
Code:
Sub delete_oldads()
'the code to find 01
Dim cel As Range
[Code].....
View 2 Replies
View Related
Jun 8, 2013
I'm using Microsoft Office 365 Home Premium. Although this file is saved with Excel 97-2003 - because the computers at work only use 2003.
OK - Here is my dilemma. I've been creating a spread(work)sheet for work. One of the fields that needs to be entered by the manager doing the papwerwork is 'Date'. I currently have the Data-Validation set as follows because the paperwork being done is for the yesterday:
Code:
=TODAY()-1
I have the Error Alert set to Warning prompting the manager to ensure the date is correct (sometimes a mid-shift audit is being done, so the actual current date has to be used). Now, what I am wanting to do is to also set it that if the manager set's the date for a future date, I would like Excel to also either do a Warning or a Critical displaying a message that future dates cannot be used.
I know this may sound trivial, but with this paperwork, dates are extremely critical and vital in the event of an investigation for cash shortages etc.
I have uploaded the excel sheet to my domain, which I can provide a link to - either here or in a private message - but I do not want to do so unless I know that it's ok to do. If so, I will reply with a link to the location of the Excel file.
View 9 Replies
View Related
Jun 26, 2014
Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.
Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.
On the front sheet, in each cell I need information to be displayed I use the following formula;
"=IF(ISERROR(INDEX(Data!$C$56:$BF$60,Data!$BI56,COLUMNS('Front Sheet'!I13:J13)))"
As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.
View 1 Replies
View Related