I have a thoroughly outdated system to keep a log of call centre stats. In order to facilitate a marginally easier workflow, I have rearranged a few things so that a user simply has to copy and paste data from reports into the worksheet.
The worksheet is set up with all the number formats like time and percentage which are extremely important in totaling and averaging hours and availabilities, however the pasted data doesn't match the destination format and it therefore affects the total.
I have tried many different special pasting options to no avail.
The only way i seem to be able to do it is to go into each line and retype my percentages (as 100 becomes 10000% ) and click the tick next to the formula bar so 00:00:16 is calculated as a quantity of time instead of text. With 30 people and 15 stats each day that exercise becomes a little bit tedious and counterproductive.
We have a macro that formats a report from our accounting program quite extensively (mostly moving text around). The last step is to select the entire worksheet and set the cell format to a custom setting #,##0.00_);(#,##0.00). We haven't been able to include this in the macro - we end up manually doing this step after the macro has completed. Is there any way to set this custom format as part of the macro?
I'm currently using some pretty extensive conditional formatting macros on a worksheet (this functionality is working fine when data is directly entered into the sheet). But when i paste data from another worksheet into my "formatted" sheet, the formatting does not take affect. is there any way to "refresh" a macro?
I have a problem where i am just trying to do a simple copy of dates from one spreadsheet to another
18/03/2008 00:00 however when the its copied to the other spreassheet is changes to the US date formate 3/18/2008 0:00 I need to keep the format as date as the date needs to be the current day. How do i get this to remain UK
I can select an individual cell or range, select Format->Cells->" date" format, hit okay, and nothing actually changes unless I click on a cell so that it brings up the edit cursor. I can then hit enter (not changing the content of the cell in any way) and then Excel recognizes it as a date. The cell values are all in the standard "3/14/2001" format, with the exception that single digits are 0-padded, and there are no spaces preceding or following the date string. The issue here is that without the cells being recognized as actual dates, they cannot be properly sorted, eg: the original range:
01/04/2005 01/01/2005 01/02/2004 01/03/2005
is sorted, incorrectly, to:
01/01/2005 01/02/2004 01/03/2005 01/04/2005
...obviously for ascending order, the single 2004 date should be placed at top. Any ideas other than a custom date-text parsing macro? I could do that, but the size of each sheet, the number of sheets per batch, and the frequency of individual batches makes that idea completely impractical. ...Technically, my macro couldn't sort the above ranges correctly, but on further investigation, I noticed I couldn't do it manually either.
i have put a small extract from list of 50000 values. I have Figures which are pasted as values on top and on A column but they do not agree unless i F2+enter on each figure on Column A that would mean 50000+ enteries.. i have tried copying column A and row 1 and pasting it as value but still no change. Row 1 header figures are typed manually.
Using 2010 - When I paste a chart I can initially retain the format of chart bar colours (Paste Special - source formatting). But when I select a new data source for the chart the formatting of the colours is not retained.
I work with a team of users that are continually publishing reports in Word that contain charts and graphs copy and pasted from Excel into Word 2010.
We have a custom script that leverages a PDF engine to automatically convert .doc files to .PDF files that we distribute electronically to our clients. This all works great, but only if all my users select 'paste special' and Enhanced Metafile Format when adding their excel charts into our reports. Most of these people aren't tech savvy, and I'm havin ga hell of a time getting them to follow this workflow and am hoping there's a way in Office 2010 to select the default paste from excel into word when the content is a chart.
It seems like the default paste from excel is an embedded chart/graph that you can then further manipulate each component of the chart in Word; the default doesnt' paste an actual image. I am assuming the pdf renderer is using a lower resolution .PNG version of the image and when these are scaled for print and or pdf, they look like crap.
Is htere anyway I can automatically change the default paste format for the chart from excel into word to be an EMF/EMV (enhanced metafile?) Either thorugh the registry or some other saveable setting?
I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
macro that will parse data that has been pasted in to Excel from a text document. Once the macro is executed the pasted data will be sent to a separate spreadsheet and parsed horizontally with the data residing in is respective cell.
Here is an example of my data from text: FIRST: Frank MIDDLE: N. LAST: Stien ADDRESS: APT14 STREET: Halloween ZIP CODE: 123456 CITY: Scaryville STATE: Scary Ya COUNTRY: Transilvania COMMENT: N/A
Here is an example of my desired results: A2:Frank, B2:N, C2:Stien, D2:APT14, E2:Halloween, F2:123456, G2:Scaryville, H2:ScaryYa, I2: Transilvania, J2:N/A
The data is always pasted in A2 on the first sheet, and varies somewhat in length but the format is always the same.
I am starting a data sheet that I will be pasting from the internet into my data sheet daily, the problem I have is that the content data is pasting with blank rows between data and also there is one piece of data that is situated on the row below which i need moving to the end of the main data row, the idea is to have single data rows with no blank rows.
I have attached an example sheet showing the layout it repeats with only the amount of data that changes.
I have colored the data as follows:
Grey= Blank Rows Yellow= Data Move to end of main row Green= Unwanted Data
This has a macro assigned that copies the data and pastes it into another worksheet at the next available row.
Now this works fine, but I want to be able to identify if the data already exists in the other worksheet then prevent it from being pasted and give an error message that the person already exists in the database.
I have rxd a pdf file that I need to manipulate I have copied the data into excel and now need it in individual cells:? The data looks like this: 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03. this is:
phone number calling | date called | time called | number called | duration in minutes | cost of call. If you're out there I'd be more than happy to hear from you. the cells to cut the data into will be formatted to take the data in the correct form. 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03 phone number calling | date called | time called | number called | duration in minutes | cost of call.
Whenever I copy and paste dates from the internet I can't get Excel to recognize the info as dates, thus making it impossible to sort by date. It doesn't matter which site I use, I have the problem with all of them.
[URL]
Using any of the links above, how I can paste those dates and get Excel to sort the date?
I have a powerpoint presentation which has a bunch of Excel charts embedded as Microsoft Office Graphic Objects, but I don't have access to the source data (plus the links are broken).
how to get the underlying data. I vaguely recall once doing this with some vba that I had found somewhere the net, but I don't have the code anymore.
I have some code that works within Excel however:
Code: Sub GetChartValues() ' this macro will recover the data from a disconnected Microsoft Graphic Object (such as we used to paste ' into PowerPoint). You will need to insert a Worksheet entitled "ChartData" to store the values. Dim NumberOfRows As Integer
I have to enter patient's SSN's into my database. The fastest and most error free way to do this is simply copy the SSN from the electronic chart and paste into the appropriate cell in the database. I don't want duplicates. So, I need the database to alert me when I paste in a duplicate SSN. I know you can set data validation to do prohibit duplicates by using a formula. In the attached spreadsheet the SSN is in column B, so:
I have a set of 50 or so large worksheets that need updating each month. My problem is the figures I need to update these sheets with are formatted differently (different alignment of blank rows) to the destination.
I'll try to illustrate, this is the sheet I need to add data to on a monthly basis... (you'll need to imagine the numbers in column form as I'm no HTML expert)
May June July 23 34 45 65 34 45 34 23 23
[Code] .......
The blank rows need to remain uniform. When I need to paste in August's data the blank rows in the raw data extract are aligned differently and it's a laborious job deleting and inserting cells to get them to align (the sheet has hundreds of rows).
I'm capable enough of recording a macro to replicate the delting/inserting of cells BUT of the 50 or so sheets I need to update there's no uniformity of where the blank rows occur. To further exacerbate, there's no uniformity over where the blank rows occur in the data that needs to be pasted in, either. The blank rows already in the worksheets HAVE to remain and can't be deleted or altered.
Below is code I have attempted. I am learning VBA and have gathered this code from the internet and this forum. I have data that is in columns B to E and from row 7 down. I want this VBA code to run when data is copied/pasted in these cells. Where the last values stop will vary so I just want to be sure this code is executed after all the data is pasted in these cells. The data will be pasted at one time. When the user removes the data from these cells, I do not want the code to run.
Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if less than four cells are changed or content deleted If Target.Cells.Count < 4 Or IsEmpty(Target) Then Exit Sub
The macro firstly formats the data that is pasted into it from an access database, then it does some calculations to determine when 10 working days from a specific date is, and when 20 working days from the date is (these go in new columns at the end of the data). The macro will also add new columns which say "Not resolved" if there is no date in the Resolution column, "Yes" in the "10 working days met?" column if the condition is met and the same for 20 workings days in a different column. As the colouring etc takes a long time I really wanted to add as conditional formatting to the macro!
how to attach a spreadsheet here then I can show you what the outcome we want is. A point to note is that there is not a set number of rows each time we do this, and I don't just want 1 cell to be coloured - I want to specify which cells in the row are coloured depending on the results in another cell on that row.
I am trying to export a range of data from excel to xml. I have a module that puts the range of data into one cell:
Code:
Function Concat(myRng As Range) Dim myStr As String Dim c As Range myStr = "" For Each c In myRng If c.Value "" Then myStr = myStr & ", " & Chr(34) & c.Value & Chr(34) Next Concat = Mid(myStr, 2, 9999) End Function
How to all data to come in with one decimal place (eg. "4.5","5.0",7.0")
I am trying to find the last cell with data in a spreadsheet once this has been found select all up upto cell A4 and then format these cells into number format to zero decimal places so far I have the following which finds the last cell:
LR = Cells(Rows.Count, "A").End(xlUp).Row Range(Cells(LR, 1), Cells(LR, "A")).Select
I want to return a value based on once off time related data which is captured at 4 time periods throughout a day. I have another set of data which does not line up with this data but I want it to return the value closest earliest value.
Given this data: 27-6-14 3:00 12 27-6-14 7:00 18 27-6-14 11:00 19
If I have a time like 27-6-14 5:00 I want to return a value of 12 (Previous time). How would this be possible?
I copy and paste large amounts of data using macros. Excel leaves some kind of trace, placeholder or something in the cells that are blank on the original sheet. So all my math calculations are messed up on the destination sheet.
Using GoTo special blanks does not work to select these cells in mass. I know how to loop through all the cells one at a time and check to see if they are "" or have a length of zero and clear them, but these methods take forever.
