after importing data (data >> get external data >> import text file) and running a macro that contains
Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp
i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before? example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. After i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?
I have a TXT file that is properly format for a nice print out but not in any fashion to work in an excel or access file.
My txt file looks something like this Header info Row 1 Header info Row 2 Header info Row 3 ACCT# Value Descripton ~ Part number Customer Name ~ Vendor Name Customer add1 ~ Vend add1 Customer add2 ~ Vend add2 Custom city, st, zip ~ vend city, st, zip
Repeat about 5 or 6 more entries do a page bread and repeat the headers and then 5 or 6 more entries
I want to automatically copy the entries and place the data into Row 1 of Excel starting in Column 1 through Column 12
I am trying to use a dynamic VBA code to import data into a xcel sheet from another sheet. I currently have it working but in the code I have the directory and the file name listed for the file to be imported. I would like to change this to a cell referance inside my sheet so that I can make changes to the name of the file or the directory location and the VBA code would use the cell reference as the source data.
how to make a button(s) which will import data. I have to make a report and import some cells from 350 other excel files. Report is attached to this post. In the yellow cells should be buttons. The code for first one is :
I've got a spreadsheet listing applications on PCs (assets), one line per application, and need to import that info into another document for each PC and save it with a file name which = the PC name (asset number). I've attached a couple of sample files to try to explain what i mean.For asset number PC037014 I need the corresponding fields filled and the list of applications pasted under where is says Additional Software List (inserting more rows if there are too many applications to fit in). Once the information has been populated, i'd like the file to be saved in a specific folder then move onto the next asset number in the list.
I've created an Auto_Run macro that imports a CSV file into a specified worksheet. That worksheet is referenced by VlookUp in another worksheet within the workbook. Everything works perfectly EXCEPT, in the midst of running the macro, the user is prompted to (RE)choose the csv file already specified in the macro. Neither the folder nor the filename ever changes, so I'm not sure why I have to keep indicating the file every time the macro runs.
PHP Code:
 'Sub Auto_Run() ' ' Auto_Run Macro ' '    Sheets("CSVData").Select    Cells.Select    Selection.Delete Shift:=xlUp    With ActiveSheet.QueryTables.Add(Connection:= _  "TEXT; G:TestProjectsWorkOrderTest.csv", Destination:=Range("$A$1"))        .Name = "WorkOrderTest"        .FieldNames = True        .RowNumbers = False       Â
I've never actually had a reason or need to use macros for data import or manipulation. At the moment I'm trying to work on a modeling problem which will output the data in the form of a large text file. The data is divided up using 1000 energy bins, and 1000 timing bins for each tally (unique set of output data) like in this file [URL].....Unfortunately because of the applications for the data I can't reduce the number of bins, and the software being used for the modeling will not provide a more convenient form for the output file.
How to create a macro that can extract this data from the output file and import it into a designated excel spreadsheet. It would be preferable to import each set of tally data from the initial output file into it's own spreadsheet, but as I said I have no experience using macros and I'm not even sure where to begin in making something that would work. or even a basic source for examples
I am attempting to modify VBA code from [URL] .....
The section of the code I want to modify is below dealing with importing selective text. The original function returned a "False" if the line contained a keyword specified in the Array function. I have reworked it so that it shows a "True" if the line contains a keyword specified in the Array function.
My only problem is that it only does it when the keyword appears in the beginning of the line, as it uses the "Left" operator in looking at line. How do I configure the function so that it returns a "True" if the keywords appears anywhere in the line?
I'm having difficulty trying to get the second file appended to the bottom of the 1st imported file. I get "run-time error '13' type mismatch". There is no difference between the two files. I'm thinking there is a problem with my range statement in the second file import, but this range works fine in other macros. Here's my code so far:
For a weekly import into Excel, I need to delete the last row each time. I'm using Excel 2010. My first thought is to start with the first cell, A1, and use .End(xlDown) to find the last row. Do I have to assign a variable to hold the row value then delete it? Or can it all be done in one statement without a variable?
i am importing a large number of text files from a folder into an excel worksheet (into column A) and found the following code from searching the forum.
Sub Import_Text_Files()
Const PATH = "C:Documents and SettingsSilent BazMy DocumentsElectronic Gift Card WorkTestTextFolder"
Dim My_Filenumber As Integer Dim My_File As String Dim My_Data As String
My_File = Trim(Dir(PATH))
If My_File = "" Then MsgBox "No Files found matching " & PATH & My_Extension Exit Sub End If.......
I have around 50 text files with similar design per attached file. I need to import the text files with criteria below:-
1. include file name 2. let user choose the folder 3. exclude data from "work in process summary" to "work in process cost totals" 4. only have one title in the excel files which all text files is combined "Item, Line ....." 5. If the text files do not have title like "Item, Line, ..." do not import
Is it possible to have all criteria listed above by running a macro?
I am working with Excel 2003. I have a series of spreadsheets generated by my companies database program. For a special project I am trying to work on, I would be able to get my results much much quicker if I could get a macro that would do the following:
The first row is a list of headers. I need the macro to look at each cell below the header, and if any data exists in that column, I need it to delete the entire column. The results I will get will tell me quickly which headers are in certain jobs but are constantly not being used. They are trimming the database at work and this type of macro would help me tremendously.
I have a large dataset with 21 columns and 3000 rows, but to keep it simple, the data looks more or less like the first dataset in the attached excel file.
This results in 3 curves (time vs force). However, there is excess data in there, I only need data as soon as the Force becomes positive (>0) untill the Force drops below 0.02 again (I consider that the end-point of my curve). So, this dataset would look like the second set in the excel file after the macro-manipulation.
I think one of the difficulties is making sure that the macro doesn't cut of my data right in the beginning, when the curve first crosses 0.02. In that case, you get the last dataset. And this is not what I want...
I hope I explained my problem well, if not, please let me know. So, is there anyone who knows how to do this?
By the way, is there anyway to insert small excel tables in this post? Cause I wasn't planning on having an attachment, but if I copy-paste the data in this post, than the formatting gets undone and it looks like there's no way to fix it...
In the sample below I need a macro that will delete the entire rows where the data is repeated in a given column---that is, that it would delete the repeat ones, leaving one of them. In this example, in the first set, all that should be left are two rows which contain ME22N and MR8M in column E. ME22N is repeated two more times and those rows need to be deleted.
If I could get a macro that would ask for the column to operate in, that would be great for other spreadsheets where it would not necessarily be column E.
ABC DE MACLEOLLynn MacLeodP0040104ME22N MACLEOLLynn MacLeodP0040104ME22N MACLEOLLynn MacLeodP0040104ME22N MACLEOLLynn MacLeodP0040104MR8M
I need a macro to delete rows that contain specific data in column A. For example, in the attached spreadsheet below,I need the macro to look for data in rows A1 - A7 (highlighted in yellow) and then delete the rows. The same data occurrs several times in the spreadsheet. The macro will need to delete all rows where this data occurs in column A.
Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD9= ABCDEFGHI1
Once data is copied to a tab labeled "data", it will sort on column H and delete all rows of data that do not have a AI or an RI code on it. Is this difficult to do?
I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.
I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet can the entire row be deleted from the Outstanding sheet.
i am in need of a macro to delete the rows with data only which then leave the sheets cleared and ready to use the next day. if i create a macro to clear say fifty rows of data and then tomorrow there could be 75 rows of data and the macro will fail clearing the sheet. if there is a solution can i just say i can create macros but never stepped into them in visual basic to change them, i guess your thinking what do i do if the macro doesn't work. and the answer is indeed yes i delete it and start it from the begining again and again till i get it right.
I receive spreadsheets each month in the same format. It is effectively 6 rows of titles, 7 rows of weekly data, 2 rows of weekly summary, 3 blank rows then it repeats itself with the 6 rows of titles etc... down the page for 8 or so weeks.
I wish to create a (looped) macro that will leave the top set of titles and the 7 rows of weekly data (Mon to Sun) then delete the next 11 rows and so on and so forth to the end. I will then be left with the intro to the dataset followed by a whole months worth of data in continuous format as opposed to weekly summarised sections. But I am stuck as my macro knowledge is basic and I can only record macro's not program VBA.
I can create a macro and manually delete all entries for one spreadsheet but would rather have a code to do this than can run (x) amount of times in case the size of data changes. When it deletes section by section, the cells move up and the row numbers change.
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I need a Macro that will search all of the cells for specific text, and delete all cells containing the text. I prefer to make a list of the specific texts and run macro to find all of them and delete all cells containing the text. I need to be able to add some more text into the list in future.
What i have is a workbook that can have multiple sheets. Sheet 2,3,4 (if there are) are a continuation of sheet 1. First 2 rows of sheet 1 are headers and the last row of the last sheet (can be 2,3, 4....) is a footer. There are 14 columns (A-N) in each sheet. Col "N" has set status and col I has set names.
What i am looking for is a macro that can perform the following actions in the the set order: 1) deletes all rows where status in col N is "closed" 2) delete all rows where name in Col I is "Many" 3) if there are multiple sheets copy all data in 1 sheet once above 2 are deleted 4) I want to update cell A2, C2, I2, M2 & N2 with Hostel, Direction,Name,Flight & Status respectively 5) Create a pivot table with all data (Col A-N & Row 2 to last) in a new sheet and call it Summary - Pivot I can then manually select how the pivot will look
Currently i am going through all sheets manually and filtering data. Using excel 2007
I am working with a spreadsheet generated from software that keeps track of fuel usage for a large fleet of vehicles. The data comes out looking like the snapshot below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDEFGH3 Transactions for CUSTOMER ID: 0000CUST7 Sales 4 5 6Product summary for Vehicle ID 00001080 7 8Product Description Transactions Quantity9 101 Unleaded 3 57.60 GL11 12Hose summary for Vehicle ID 00001080 13Site ID HoseGradeProductTransactions Quantity140001 2113 57.60 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am trying to get the data into a more convenient format for analysis. I need a macro that will:
1) Take the text "Product summary for vehicle ID 0000****", extract the last 4 digits of the text, and paste it where the 1 is under the Product heading (a10). Those digits are the actual fleet number, and I need to separate them out from the rest of the text. The digits will change for each vehicle, so the macro should just move down the spreadsheet doing the same thing for each instance (the setup you see is repeated for every vehicle).
2) Once the first goal is accomplished, I would like the macro to then go back through and delete every row except for the rows with the pertinent data in them. So this means I would only want one row per vehicle and all rows would line up directly below each other like demonstrated below.
******** ******************** ************************************************************************>Microsoft Excel - June Fuel Transaction Listing.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA10=ABCDEFGH101080 Unleaded 3 57.60 GL111081 Unleaded 6 84.70 GL121122 Unleaded 5 47.00 GL131182 Unleaded 8 95.80 GLJune Fuel Transaction Listing [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have a spreadsheet that allows room for 35 students per period, but instead of having the teacher manually go in and delete the extra data and hide the rows, I want to create a macro that will do so.
In cell B4 the teacher will enter how many students are in their first period class. (cell C4 for second period, cell D4 for third etc...) I want the entering of the number to automatically hide the superfluous rows and delete the data in the second column for those rows. I don't want the rows to be completely deleted because another year they may have more students and need those rows back.
I have attached one of the workbooks that I need to put this macro into. I have created room for 35 students in a given period. So if they have 23 students entered into B4 (period one) I would need rows 30-41 to be hidden, and I would need the formulas in B30:B41, E30:E41, H30:H31, K30:K31 etc....deleted.
Basically the point of the formulas is the teacher will enter the total points possible on that given assignment in cell B6, E6, H6 etc....and it autofills that score down, so the teacher only enters those that missed points instead of entering in the missed ones and the 100 percent ones.
I would need those formulas to be deleted because if the assignment was out of 10 then cell B30-B41 will give students a 10, and then the class average will be computing those scores,but those students don't exist.
But if I can't get the macro to auto delete the formulas, I will just not have those formulas in there, and the teacher can enter all scores.
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no., the selected part no. row should be deleted.
