Copy Lines Depending On Agent Selected And Date Range
I am having issues with this Macro since I have to run it on computers only supporting Excel 2003 and computers with Excel 2007. The Macro in use is as follows:
--------------------------------------------------------------------------------------------------------------------------------
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula To Copy Lines Depending On Date Range And Agent Selected
What I would like to do with the information that I have put together on a different workbook is to reference an Agent (column A), and a time range (Date is in column D) and then copy the row from A to I for each occurrence during that range of dates so we can see how many and what errors have occurred during any range of dates selected. (I will have different people inputting errors at different times so the dates will not be sorted unless they have to be). I want to be able to select different agents/date ranges to be able to get a glance of how that particular agent did during this time frame. Here is an example of the work sheet where the data is originaly. [Removed] Here is an example (in a different workbook) of what the sheet should look like if I was searching errors for Albert Ozier between 1/25/2010 and 1/28/2010. [Removed]
View Replies!
View Related
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
Copy Selected Lines
I have two sheets. sheet 1 has a list of invoices. on sheet 2 I just want to list certain invoices. so sheet 1 may be like this: 001 rmb $25.00 1356 002 ght $35.02 4568 003 ght $45.23 5689 004 rmb $12.25 4568 005 bnj $1.25 4568 006 sdr $12.54 4568 007 rmb $65.25 4568 009 bnj $56.54 4568 On sheet 2 I need it to pull out all the rmb lines. 001 rmb $25.00 1356 004 rmb $12.25 4568 007 rmb $65.25 4568 I could make sheet 2 in to a list and sort it that way. but what I need to do is pull the information into sheet 2, it's no good being able to sort it on sheet 1.
View Replies!
View Related
Copy Rows To New Sheet Based On Date Range Selected On A Form
This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet: With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too. Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well. I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.
View Replies!
View Related
Copy Data From One Sheet To Another Depending On Date.
"Sheet1" of Book1.xls contains the daily efforts of each members in the team. The detail changes automatically everyday like: Date Name Task1 Task2 Total 1/1/08 Mark Design-2 Analysis-2 4 1/1/08 James Design-3 Analysis-2 5 1/1/08 Paul Implement-4 4 On the very next day the details will be automatically changed as: Date Name Task1 Task2 Total 1/2/08 Mark Design-2 Analysis-4 6 1/2/08 James Design-3 Analysis-2 5 1/2/08 Paul Analysis--8 8 i.e. "Sheet1" will contain only the details of current date. I have managed to do it in "Sheet1"... Here I need to add all the details automatically for each and every day in a separate worksheet ("Sheet2") from "Sheet1" of the same excel. As soon as the date changed, then it will be reflected automatically in "Sheet2" in the next blank row for each member of the team... The details of "Sheet2" will be: Date Name Task1 Task2 Total 1/1/08 Mark Design-2 Analysis-2 4 1/1/08 James Design-3 Analysis-2 5 1/1/08 Paul Implement-4 4 1/2/08 Mark Design-2 Analysis-4 6 1/2/08 James Design-3 Analysis-2 5 1/2/08 Paul Analysis--8 8
View Replies!
View Related
Print By Date Depending Range
i have a excel sheet which i use to schedule my service team. Basically i have horizontally the dates ((a colum for every day of the year) and vertically the guys names (about 25 rows). I would like to add a function so i can push a button and i automatically print the past, current and next week of my shedule on one page. In addition it should print my usage chart which is on a different work sheet in the same file.
View Replies!
View Related
VLookup Date Range And Return Multi Lines
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ). The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me The problems I need solving are: I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this? I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
View Replies!
View Related
Lookup Date In Range Depending On A Number
I have a Master workbook with 4 sheets named WEEK 1 to WEEK 4. At work we work around 13 4 week periods and need this workbook to be dated accordingly, that is Period 1 Week 1 and date of first day (Sunday) of that period. The ideal solution would be for the user to enter 1 to 13 into a cell and via a button all dates to be populated and a new workbook created for that period. I have attached a sample workbook with the 4 weekly sheets and a date range on another sheet to show the range I need the dates from. I hope the workbook explains what I need better than my explanation here!
View Replies!
View Related
Copy Selected Range To A New Workbook
I have a code that will allow me to copy three worksheets from an open workbook to a new workbook. That works great, but I only need only a selected range from each of these worksheets to be copied (Range A1:AV60). Here is the code I use to select and copy the worksheets, how do I add a range within this code for each worksheet. Sheets(Array("Req Page 1", "Req Ext 1", "Req Ext 2")).Copy
View Replies!
View Related
Copy Selected Range To New Workbook
I am trying to create a method to select the values of contiguous range selections on excel worksheets. Users will be making new worksheets in new workbooks out of the data from the old sheets. Currently users make a selection is and copy it to a new worksheet. Is it possible to retrieve from the 'clipboard', the values from the associated cells? And then to parse them into columns in a new worksheet? Earlier I tried this bit of ( it didnt work ) Sub AddNew() ' for passing highlighted text into the form so that I can pass it 'to a new workbook. Dim PassData1 As String Selection.Value = PassData1 Set NewBook = Workbooks.Add With NewBook .Title = "xxx" . SaveAs Filename:="xxx.xls" End With Range("A1").Value = PassData1 End Sub
View Replies!
View Related
Why Won't This Selected Range Copy To Another Sheet?
I have a button on the Players worksheet that has this Why would it not work? Range("NewData").Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Sheets("Players").Range("A3").PasteSpecial Paste:=xlValues I am trying to copy Column B starting at B5 down to the last used cell in column B. into the players sheet starting at cell a3. I try it and it highlights 4 cells and that is it??? Michael
View Replies!
View Related
Copy Selected Range & Save To Text File
I also use VBA in a terminal emulation program that I use the following command to 'screen scrape' the current display memory and quickly Save it to a .txt file: .SaveDisplayMemory "C:File1.txt", rcOverwrite What I want to do in Excel is, Copy the current selected Range() and Save it to a .txt file. I know how Save the current Sheet to a .txt file, but can't figure out how to Save just a Range() of cells.
View Replies!
View Related
Userform To Enter Data In Different Locations Depending On Selected Options
MONDAY xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc. Hours State1 - Hours State2 - Hours State3 - etc. TUESDAY xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc. Hours State1 - Hours State2 - Hours State3 - etc. WEDNESDAY xxxxxxxxxxxxxTeam1 | Team2 | Team3 | Team4 etc. Hours State1 - Hours State2 - Hours State3 - etc. Each team leader would then input hours in each state each day on their column. Make sense? Easy Peasy... My question........... is it posible to have a userform where a TL would select their name (Column) and Day (Monday=Row 3, Tuesday=Row23 etc) from a dropdown and then input figures in txt boxes to submit them in the correct location?
View Replies!
View Related
Counting Transactions Per Agent
I have a group of users in cell C1 and i wanted to count how many times they have process a payment as long as its value in Cell D1 is more then or equal to 1. I tried sumif, but its totalling the amount. but i wanted is the number of transaction.
View Replies!
View Related
Macro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected
This may not be the best way to do this, but I don't know Macros or Pivot Tables. I am looking for a way with formulas to do the following: Within a workbook the 1st sheet is the data entry. In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column: Example: Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry. G2:G2999 in the same sheet is where the data is. Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?
View Replies!
View Related
Copy Selected Rows & Insert As Many Times As Rows Selected
The following code inserts a row below the selected row, and copies the formula of the row above into it. Dim Rw As Integer Rw = ActiveCell.Row Selection.Insert Shift:=xlDown Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy Rows("" & Rw & ":" & Rw & "").Paste However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
View Replies!
View Related
Copy Macro With Many Lines
I'm having an excel sheet with addresses. I need to copy some lines of the first sheet to the other sheet. Between the lines I want to copy there are 7 lines which shouldn't be copied. I created the following macro which does the job: ...
View Replies!
View Related
Find Begining And End Of A Date Range To Copy
I want to save myself some time at work, that's why I want to create an automatic sheet in Excel using VBA. I need to create files with monthly reports of data. My project looks like this: 1. Gather information from user (Month & Year) 2. Create a file which name is based on Month & Year 3. Select data sheet 4. Find date range to copy (RowBegin, RowEnd) 5. Copy nad Paste required data into new file (point 2.) 6. Delete rows, formated in certain way 7. Find bottom of all columns and insert row with all formulas 8. Format all sheet acording to requirements I was looking over MrExcel.com and I know how to do all the points besides point -> 4. Find date range to copy (RowBegin, RowEnd) I got a column "D" with about 5000 cells for now and the sheet is updated daily. In those cells I got dates formatted like this: D 4012 14/02/2006 4013 21/02/2006 4014 15/03/2006 4015 15/03/2006 4016 17/03/2006 4017 07/04/2006 4018 07/04/2006 4019 20/04/2006 What I don’t know is how get in VBA two variables – range of row numbers describing selected month (for example March) - from (RowBegin) & to (RowEnd), in this case RowBegin=4014 and RowEnd=4016, which I can later put into: RowSub = RowEnd - RowBegin Range("A”&RowBegin&”:A”&RowEnd).Copy Sheets("March").Range("A1:A”&RowSub)
View Replies!
View Related
2-dimensional Date Range With Dynamic Copy And Paste
Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note. The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating. I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent. What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question. The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report. What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.
View Replies!
View Related
Calculating Several Data Depending On Date
I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week. What would be the code to.... In K column, calculate the milage that is in row F; which is A3 - A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell. Does anyone have any ideas how to do that?
View Replies!
View Related
Open A Sheet Depending On Date.
I have a WB with 31 sheets, each dated 1st to 31st and currently hidden. I have been trying to write a VBA code that would open a sheet based on todays date... so if today is the 2nd of Nov it would unhide the sheet 2nd, I would also like it to check if a sheet dated 1st is visable, and if it is, hide it. The date for the sheet to hide would I think need to be relative... but I don't know how to get it right.
View Replies!
View Related
Displaying Cell Depending On Date
My head hurts from working on this all day. I have a large spreadsheet and in the top left corner I need to show the percent of vehicles that are not working out of our whole pool. I have a function that detects the color of the cell (Red is broke) and counts the total of red cells and then divides it by the total giving the percent and it is in G118 for Jan 1/2007, H118 for Jan 2/2007 etc. I made a function that will count Julian days from Jan 1, so for today I get 114. I know I need to display G + 114 columns but have no idea how to get that column name from this and always display the current one in the corner.
View Replies!
View Related
Sum Data Depending On A Date
I have a workbook full of a bunch of sheets with the same formatting. Each sheet has data for a 2 week period. I am making a “master” type sheet and I’m hoping to be able to pull some data from each sheet. On each sheet: Row 1 has dates starting on E1 and ending on AF1 (several blank/combined columns) Row 22 has the data I want to pull, and it is one column to the right of the date. So, F22 corresponds with E1… Ex. One sheet looks like this: E1 = 8/24/09 AF1 = 9/6/09 I got single amounts to work by using: =IF((MONTH(E1)=8),F22," ") However, I’m hoping to find a formula that would look at the entire two week period and sum the row 22 amounts for each day that falls within a certain month. I tried: =IF((MONTH(E1:AG1)=8),F22:AG22," ") Which comes back false since not all days in the two week period fall within August (8). Does anyone have any ideas for a formula that would be easily changeable, and that would pull & sum row 22 data for each row 1 date that falls within a certain month?
View Replies!
View Related
To Import Several Files Depending On Date
I am trying to import a several files depending on date. The first part of the name of the files that I am importing are always the same, the end changes as the date changes. I have a user form that comes up and I pick the dates that I am looking at. What would be the code to import those depending on the date that I pick? When my UF comes up, I pick whatever dates, I need to import all the files in between those dates.
View Replies!
View Related
Display Test String Depending On Date
I need a forumla that will dispay a set text message in a cell based on the date in another cell. I need cell A1 to display the text message "Forwards Booking Date, No Action Required" Where cell A2 has a date entered in it that is the same as todays date or greater - i.e. in the future. And if the date is in the past for no text message to appear.
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
Copy Columns Depending On Criteria
I have attached a spreadsheet. I have a dump of about 4000 calls (I have minimised the attachment for size sake) that I need to do certain reports on. The second sheet (dump) has the data in its raw form from our call reporting tool. The 1st sheet (Report) is a broken down version the powers that be wish to see. My formula needs to meet certain criteria, as you can see I only have 6 columns on the (Report) sheet where as the dump has lots ! The (report) sheet needs to list the Parent Case ID only if the Summary column has BSR at the beginning, if it meets this criteria then it lists the other relevant columns in the (report) sheet.
View Replies!
View Related
Drop Down List Which Displays A Different Set Of Values Depending Upon The Value Selected By A Previous Drop Down List
I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .
View Replies!
View Related
Copy Data Based On Date Range Put In By User For Report
I have looked through the forum and found pits and peices but can not put the puzzle together. Found the VBA code in my example from a earlier post but there was no final answer to the post. Trying to have the user put in a date range via command button. Fro mthis date range the data thats falls within that range is copied to a report sheet. Will also need to have all the old data from a earlier querry removed. Have attached example sheet.
View Replies!
View Related
Find Date Chosen From Calendar & Copy Range And Paste As Values
I've attached a workbook which contains two sheets: PAYM DEVICE_PAYM Data is updated on DEVICE_PAYM each day with the figures I need located in column "AN". I need to be able to copy the data from "AN" and paste it into a column on the PAYM sheet. The destination column on the PAYM sheet needs to be specified by inputting a specific date - ideally, I would like to use the calendar Add-In as part of this process. I've already input some code for the calendar but am unable to develop this further so that it finds the correct date column and pastes the data across from the other sheet.
View Replies!
View Related
Copy Data On New Lines From A Source Sheet Based On Criteria
I have a source sheet which contains data. Within this data there is a column (column 3) called Name and I would like to take the data from this sheet and paste it onto a new sheet but in grouped format. So take all instances where it finds criteria 1, then 2 and so on and just paste it on a new line each time it finds a criteria. So far I have the following code to search Sub loopy() Dim rngTemp As Range ' the range variable declaration Dim intCounter As Integer 'counter as integer ' the following loop goes through all the cells in column a up until the last used cell in the column For Each rngTemp In Range("a1", Range("a" & Rows.Count).End(xlUp)) 'increment the counter if the cell has value fridge If rngTemp = "Fridge" Then intCounter = intCounter + 1 Next rngTemp MsgBox "There were " & intCounter & " cells equal to Fridge" End Sub
View Replies!
View Related
Macro To Copy To Specific Sheet Depending On Cell Value
This is the code I have: Sub Auto_Open() Sheets("Data").Select Range("A2:E32").Select Selection.Copy Sheets("May").Select ActiveWindow.SmallScroll Down:=-9 Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Sheets("Data").Select Range("A1").Select ActiveSheet.Paste Range("A1").Select Sheets("Sheet1").Select Range("A2:E32").Select Selection.ClearContents Range("A1").Select End Sub 1) How do I set this up so that if the cell Z1 reads 1, it will paste the data to the "January" Sheet, if Z1 reads 2, it will paste to "February" Sheet, if Z1 is 3 it will paste to "March"......
View Replies!
View Related
Copy Rows To Other Sheets Depending On Values In Column
I’ve done a search of the forum for a macro and found some post that seems to be about what I want to do, but unfortunately my minimal vb experience prevents me from adapting them to my requirements. I‘ve got a workbook with three sheets; say Sheet A, Sheet B and Sheet C. I want the info in Sheet C copied to either Sheet A or B depending on the info in cells in Column A of Sheet C. Sheet C contains customer info, there are about 9 column headings and up to 30 000 rows (Individual customers). The cells in Column A will contain a number between 0 - 23. What I want the macro to do is, if a cell in Column A contains any of these numbers, 0, 1, 2, 6,7,8,9,10,17,19,20,21,22,23, I want that whole row copied to Sheet A and if it contains 3,4,5,11,12,13,14,15,16,18, I want it copied to Sheet B. The cells will only contain one number, never a combination.
View Replies!
View Related
Set Range Depending On Value In Column D
I have a list of data in colum A and I want to group them together. In column D I number the group with the same number. A1 = Tony D1 = 1 A2 = Tom D2 = 1 A3 = Bill D3 = 1 A4 = Helen D4 = 2 A5 = Marg D5 = 2 A6 = Kathy D6 = 2 A7 = Catherine D7 = 2 And so on..... The list up to 100 names As you can see group 1 = 3 names, group 2 = 4 names, group 3 = 6 or maybe 7 and so on. I'm trying to set ranges for them Eg: set rng1 = A1:A3 set rng2 = A4:A7 but it not fixed to set ranges like that. It's variable and changes every week. Next week maybe rng1 = A1:A5, rng2 = A6:A9. can I have the VB code to determine these variable ranges.
View Replies!
View Related
Assignment Of Number Depending On Range It Falls Into
The number in cell M21 falls into one of the ranges from C4 to C15. A matrix number needs to be printed in each cell from N21 to N30. The selection of the number to be printed in column N (under Matrix Assignment) depends on the range it is next to in array A4:C15. For example (as shown), the correct number for cell N21 is 122 because 0.2626 falls between 0.24 - 0.2699, and Matrix 122 corresponds to that range.
View Replies!
View Related
Set Range From Selected Cells/Range
I have a macro which I manually have to change the range in order to run the macro, e.g. Set rngData = Range("B4:I12") rather than having to change the range for each macro, I was wondering if I could run the macro for the highlighted area. I have tried this, but doesn't seem to work. Set rngData = Range. CurrentRegion.Select
View Replies!
View Related
Copy Selected Sheets
I use the command ActiveWindow.SelectedSheets.Copy to copy selected sheets into a new workbook. As a result of this line is a new workbook created and a the selected sheets are pasted into the new workbook. Is it possible to paste only values by using a single line like this?
View Replies!
View Related
List Box Depending On Combobox And Add Or Delete Items To Range
i had a user form with a Combo box,list box,text box and a command button. I need the code that works upon selecting: 1)An Item from the combo box should display the list in the list box. eg: If Country is selected from the combobox then the list box should contain all the names of the countries from the country Column 2)An item should be added to the list in the worksheet when an item is entered in the textbox. Like wise, when an item is selected from the combobox from "Delete Items List" all the items relating to the item selected from the combobox should be displayed in the list box and a choice to delete the items relating to the combobox item should be provided.
View Replies!
View Related
Changing Range Of Sumproduct Function Depending On Length Of Data Series
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View Replies!
View Related
|
TRACKER
