I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.
Is there a way to delete the last couple of lines of data of a spreadsheet?
I'm trying to merge all the "C" types in Column E into one row like this
Sheet1 *ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After1508/01/201218:36:2008/01/201218:40:00T**1608/01/201218:45:1208/01/201218:50:44T**1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08T**1909/01/201208:45:1109/01/201208:49:55T**
So basically, I need to use the 'start date' and 'start time' from the first row and the 'start date' and 'start time' from the last row. Also use the first 'SOC before' and the last 'SOC' After'.
I have more data in column h - column z, so I need to delete the entire row of the additional "C" rows. Otherwise the data will be misaligned.
Column A = Date Column B = Single Distance Column C = Total Distance for that date
Sheet1 Â ABC1DateIndividual Distance Total distance 204/01/2011919304/01/20111019405/01/2011 212505/01/20111012606/01/2011926706/01/2011326806/01/20111026906/01/20112261006/01/2011226
It would be really useful if I could format it like this
Sheet1 Â FGHIJ1Total Individual 219910Â Â 312210Â Â 42631022
So basically, it puts all the information on one row for a particular date.
I have cells which contain dates in the typical Excel format. IE: Jan 3rd, 2000 in unformatted form shows up as 36528 in Excel in Cell A2. What I would like to do, in VB, is take the contents of that cell A2 (36528), and turn it into a date in the form of:
Year,Month,Day (For instance, Jan 5th 2001 would be 2001,1,5 And then assign it to a variable
I'm looking for a macro to select data from a larger data set, and then put it in another worksheet. Basically, I want to select any cell in column A that contains "SO5" and move those to the column A of a separate worksheet. Then, for each cell that contains "SO5" would like to move to the right 5 columns and down 3 rows and select from that cell all the way to the end of the table. There may or may not be data in all of the table cells, so the selection should be based off of the column headers which will always have values. I would like to take that data and put it with the corresponding "SO5" in the new sheet.
Here's a sample:
MacroSample.xlsm
This is a simplified version, but I'm trying to get the data from sheet1 into the format in sheet2. Keep in mind that the number of columns and rows may change so it needs to be as dynamic as possible.
I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.
I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.
I am having a problem with a macro which has only started occuring on Excel 2000. It worked fine on Excel 1997. Basically, the Macro converts a text file (with comma delimiter) to an excel format. The problem I am having is with some of the date fields in the text file.
All dates in the Text file are in the format dd/mm/yyyy (i.e. 01/02/1990)
If I convert the Text file to Excel format manually through the wizard it correctly converts the date in Excel to dd/mm/yyyy (01/02/1990). However, If I run the Macro I have created, it converts the date to mm/dd/yyyy (02/01/1990).
This only seems to happen if the dd field is less than 12.
I attach a sample of the text file and Excel file (with incorrect dates) and also the Macro.
Basically I need the Macro to convert the dates in dd/mm/yyyy always....
I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through: 1. Clear the first 3 rows and the last 5 rows 2. Copy the totals in the last row of the data and paste them in the first row 3. Subtotal the different categories in row 2 and add a validity check 4. Add a new column A with a formula to add customer numbers on each line =IF(ISERROR(FIND("00000",B5)),A4,B5 5. Copy the formulas and paste values over them 6. Filter the data to show blanks under the "Doc Date" column, delete all rows 7. Filter the data to show blanks under the "Type" column, delete all rows 8. Filter the data to show "Total:" under the "Apply No" column, delete all rows 9. Turn off filters, format all numbers in accounting format 10. Check the validation at the top to ensure no transactions were deleted
We have a system that normally dumps our data in the format of column K, with all of the data in one column and the same spacing you see below in red. The problem is when the system is slow, we have to manually dump it, and when we manually dump it, it comes out spread out from column A to column I. For the macro to work without bugging out on me, it has to be in the same format as column K, with exactly the same spacing.
I have tried text to columns while importing, amongst other things, but have not had any luck. So in a nutshell I need to be able to make column A thru I, look just like column K with exactly the same spacing. The data dumps are different every day.
I have a strange feeling I will not get a reply on this question, because it is so strange, or that I failed to articulate it correctly....
Transaction #Transaction DescriptionAccountDate (XX/XX/XX)DesciptionABCDEFG10/24/2015Cash Held(3404.00)3404.00 0.00 0.00 50.00 0.00 (50.00)10/25/20152Cash not Held0.00 (9707.00)9707.00 0.00 (60.00)60.00 0.00 10/26/2015Cash Held0.00 (7402.00)0.00 7402.00 0.00 0.00 0.00 10/27/20154Transfer to Bank 0.04 0.00 (50.04)0.00 50.00 0.00 0.00 10/28/20155Transfer of Cash0.00 4208.00 0.00 (4208.00)0.00 0.00 0.00
I am trying to make a macro that will turn transpose the above data into the below data.
DateDescriptionAccountAmount10/24/2015Cash HeldA-340410/24/2015Cash HeldB340410/24/2015Cash HeldE5010/24/2015Cash HeldG-5010/25/2015#2 Cash not HeldB-970710/25/2015#2 Cash not HeldC970710/25/2015#2 Cash not HeldE-6010/25/2015#2 Cash not HeldF6010/26/2015Cash HeldE-740210/26/2015Cash HeldG7402
Basically each non-zero number in the accounts (A through G) need to have their own row with the account name from their appropriate column and the date and description in their appropriate row. So as you can see each account A,B,E,and G get the amount -3404, 3404, 50, and -50 in their own row and get the appropriate descipion of 'Cash Held' and Date '10/24/2015'. Then the macro would move onto the next row and do the same thing.
Also if there is a way to tack in front of the description the transaction # (if there is one) ie. #2 Cash not Held. I really don't even know where to start.
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 time format in one excel sheet, I need a macro or a formula to change it into a specific format, this is to be able to import it to another software.
I have attached a file here you can see an example, this I manually, I want Row D and E into the format in A, I s it possible to make it in a macro or formula, manually it takes a long time.
I have two columns where there are dates (e.g. 11/05/2009) in a date format; I would like to change the format to `general`; but without losing the original values, i.e. I still want to see 11/05/2009, but just in `general`format. Is it possible to create a macro that will do that? I want these values to stay in the same place, i.e. if it is in cell K10, I still want to have it there - just in other format.
I have tried to write code to format numbers to zero decimal places as well as to justify the format as the zeroes appears as 00000000 when imported.
I need the macro to do this on the first 7 sheets.
I also need ------- lines and ) to be cleared on the first 7 sheets.
I have attached sample data and my code below
Code: Sub Format_Data() Dim Cnt As Long, i As Long Cnt = Sheets.Count Application.DisplayAlerts = False For i = Cnt To 7 Range("F:H").Select With Sheets(i)
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.
I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.
Is there any macro that can be used to convert data which have been converted from PDF to Excel. Currently I'm using a traditional way by pressing F2 and enter to convert the data to excel format. It's tedious ( since the data range is quite big) and crammed my finger.
how to interrupt VBA code, allow user modifications to a chart, and then continue the code where it left off with a click of a button. I currently have the code in place where a UserForm pops up at a specific point and tells the user to make adjustments and press OK when done. It works fine but the UserForm displays and the macro resumes when OK is clicked, but I can't figure out how to activate the chart or the spreadsheet to allow editing while that UserForm is on the screen. If only I can get excel to allow me to sleect the spreadsheet and make changes while the UserForm is on the screen, I would be set. Can anyone please help me with any options to allow editing while the UserForm is on the screen so I can edit and then click the OK when done?
I posted something similar to this over two years ago. The way I got around it at the time was to break the code into two separate subs. This time around, the code is making 17 graphs and I want to ask for editing for each one before continuing. That would be a lot of individual sections of code.
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j. I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
I have a sheet with data 7 columns wide and up to 1500 rows long. Below are a few lines from the sheet. The data from the sheet is grouped into "months" as shown in the header down the first column, "Show June, 2013..." and "Show July, 2013...", and so on. Is there a way I can make Excel re-align the data into ONE long row of data for each month? Each new month row must be "triggered" by the text (like the word "Show") because the actual cell number of where the new month begins may change with new updates. I want to do this because I plan to refresh the data once at the end of each day from the web and apply the new data to the "next" row of data. So eventually, I can have a history of "Last" values I can chart for each "strike" for each month.
In a macro, I calculate fees (Bold Blue format) on a sheet "Fees" then copy one value to sheet "All Vertical". The values come out regular and black. How can I copy the format aswell?
I am recording a macro to help me with formatting a document. I know do this manually, but realized that if I record a macro and run it the process would be faster. I have tried this and it works really well. However the problem is that although the columns are always the same, the rows increase and decrease everytime I download the raw report.
When I record the macro and then run it, it only selects the number of rows that I choose when I record the macro. I am sure there is a way to have the macro look for date and then stop after there is no more rows, but I can't find it in help.
I have tried selecting the whole document, but that slows the automated formatting down significantly.
I'm watching an educational D V D on my computer, and take notes in excel. I repeatedly use the print screen command, then go to excel and paste in the picture, though it is too large. I resize it to 50% and I'm done. What kind of code do I need to use to paste and shrink the picture to 50%? Also, how do I attach a shortcut key to this action (for example Ctrl+M)
i am getting is it only formats H11:AE12 & H14:AE15, It does not format the first range or any others. Am i doing something wrong? I recorded the first one H8:AE9 then just added the ranges for the others, have i missed something?
