I am trying to wite a macro that will allow me to pull up specific files. All files will be located in the same folder. For example, I want to type the workbook name "sawn" into a cell and hit the macro button. The macro will then look in a "pool" folder for the workbook named "sawn" and open it. Is this even possible?
View 14 RepliesI have many text files basically with the same data from different dates. I am trying to select specific lines from each text, one that includes ".txt" which is the date, ex: "2008_308" and one that includes the word "PIXEL" and the following 5 lines after that.
I found a very similar post to mine and someone provided the script using VB for the question. It works, but it was only for one text file. I would like to use it for multiple text files. The files are all dated, i.e. "2008_308.txt". Although some of the dates are missing.
This is the script, from another posting, for importing selective lines from one text file:
Sub test()
Dim fn As String, txt As String, delim As String, a() As String
Dim i As Long, ii As Long, iii As Long, n As Long, x, y
fn = "c: est.txt" ' Change here (File Path)
delim = vbTab ' Change here (delimiter)
temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
x = Split(temp, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To 100)
For i = 0 To UBound(x)
If InStr(1, x(i), "txt", 1) > 0 Then
n = n + 1 : y = Split(x(i), delim)
For ii = 0 To UBound(y)
a(n, ii + 1) = y(ii)..........................
I would need only the first row data to be copied into a master file in column A (row A from each file to column A, column B.... etc - basically transpose value into master file)
View 1 Replies View RelatedI recently formatted my laptop harddrive. I'm running windows XP Pro. I backed up all files to my 500GB WD external drive. All is fine except that I'm now unable to retrieve my excel files and no one has been able to really help. These spreadsheets are very important to me.
View 9 Replies View RelatedI have several excel files in a folder, and they are named:
AA v.5
AA v.6
AA v.7
AA v.8
BB v.5
BB v.7
CC v.8
CC v.6
The "AA" and "BB" are names of who created it and v.5 are the week it was created.
What I would like to do is in an Excel file be able to choose to retrieve information about for example how man files with v.5 in its name.
OR information about all files with "BB v." in its name (to see all files created bye BB)
Or "BB v.4" (all files created bye BB in week 4)
Or "BB v.4"+"BB v.5"+"CC v.6"
The reason for this is that I would like to be able to create statistics from this so that I can see how it has changed like in a graph. So that I can see the difference and compare how many files BB and CC have created and which week.
I have a spreadsheet which contains many rows (>3000) and a few columns.
I would like to make a macro that will copy specific cells depending on the number from another cell (from the first column) and then transpose the corresponding values. My current problem is that the values I'd like to copy (countries) are on a row (horizontal) and I need it on a column (below "Countries") next to the car models (in black: the current sheet, in red: my target):
Number of Countries
Cars
Countries
A
FJ
A
UK
[Code] .......
Is it be possible to create a macro that transposes automatically the countries from the right into the third column (like 90 degrees against the clockwise direction)?
lately I have promised to program a little macro which looked easy for me to do but on which I have now spend a considerable amount of time without finding the problem why it is not running. I am working under Windows XP with Excel 2010, here is what i was trying to do:
In a worksheet there are two columns G and H. In both columns it shall be possible to select a number of possible entries from a list. For column G this is done via data validation, no problem. Now, the choices which are there in column H shall depend on the entries made in the appropriate row of column G. Column G thus functions as an overarching category. If I choose "Tree" in G, H would give me branches, leaves, apples. If I choose "car" the choices would be wheel, engine, gasoline etc. This shall be done by a Macro:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cat1 As Range
Dim cat2 As Range
Dim x As Integer
Set cat1 = Range("G:G") 'Task Group
Set cat2 = Range("H:H") 'Task Category
[code].....
When running, it gives me Run time error 1004: Method "Range" of object '_Worksheet' failed. Debug highlights the row
Code:
With Range(Cells(ActiveCell.Row, 8))
The sheet shown, Complaints, lists on each row information on a single complaint. This information extends from Col A to Col Y (though not that way here, the data actually begins in row 3). I want to highlight a Customer cell, say H227 for Customer A, then activate a macro that (1) recognizes the active customer (here A), searches the list, identifies each row where A is the customer and then writes each row into sheet "Event" beginning in row 4. I should then see the same A to Y cells of information, beginning in row 4, filled in sheet Event for the six customer A complaints. Note, the actual data in sheet Complaints involves 300 rows of data, and over 100 customers can be on the list, some up to 15 times. I know that I could do this by filtering the data, copying and pasting, but a simple macro would be quicker for the group of individuals involved. Note, I then use this information to populate a chart.
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXY226#INITIATION MonthINITIATION DATECRM Transaction IDSales OrderSalesSHIP PlantCUSTOMERCITYST/PROVPRODUCTProductLbs InvolvedPROBLEM Complaint
[Code]....
how I would go about addressing this issue. I am given a directory with individual files in it. Each file has critical information I have to extract from it. So, I want the user to provided this directory to the macro and I want the macro to cycle through each file in this directory (excluding super and sub direcories), open it, retrieve info, and close it
View 5 Replies View RelatedI need a macro that will ask for a crew # and then select all the rows off another worksheet that contains that crew #. Then take the selected rows and copy them to the active worksheet for reporting.
Possible?
On sheet1 I have 6 products in E15:E20 and their prices in AB15:AB20.
I have created userform1:
Step 1. When the form shows I'd like the products in E15:E20 to show under "Product" and prices in AB15:AB20 to show under "Price".
Step 2. The user needs to be able to add a price value in either the "-Disc" and/or "+Fees" fields, from this the adjusted price should show in "New Price"
Step 3. When the user hits "Add", the value under "New Price" needs to show up in AB15:AB20. To complicated this last step (maybe) in cells AB15:AB20 is:
Code:
=IF($R15=0,"",IF(ISERROR(SUM(INDEX(Data!$V$3:$Z$114,MATCH($E15,Data!$U$3:$U$114,0),
MATCH($B15,Data!$V$1:$Z$1,0))))+
SUM(INDEX(Data!$AO$2:$AS$7,MATCH($T15,Data!$AN$2:$AN$7,0),MATCH($B15,Data!$AO$1:$AS$1,0))),"",
SUM(INDEX(Data!$V$3:$Z$114,MATCH($E15,Data!$U$3:$U$114,0),MATCH($B15,Data!$V$1:$Z$1,0))))+
SUM(INDEX(Data!$AO$2:$AS$7,MATCH($T15,Data!$AN$2:$AN$7,0),MATCH($B15,Data!$AO$1:$AS$1,0))))
So whatever value is entered into "-Dsc" or "+Fees" needs to be added and/or subtracted from this formula.
I don't know much about vba, the best I could come up with is:
Code:
Private Sub cmdclose1_Click()
Unload Me
End Sub
Private Sub cmdadd1_Click()
[Code]......
Is there any way to retrieve all the macro names in a worksheet?
View 2 Replies View RelatedSo I have the macro written, but I don't get the complete data that I am looking for. This is what I am trying to achieve
Here is an example of what I am trying to achieve. This is in the first sheet:
Column A Column B
Car Saw
Dew Jacket
Pen Key
Saw Screen
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
Now I want this in the next sheet:
Column A Column B Column C Column D
Car Saw Screen Sock
Car Saw Screen Mouse
Dew Jacket Window
Pen Key
Saw Screen Sock
Hand Shoe
Jacket Window
Screen Sock
Screen Mouse
In my current macro, It displays most of what I am looking for, but not a duplicate ID if there is one. For example Screen, there are 2 IDs. My macro is only getting the first one and then skipping over the second ID. I would like it to where if there is a double or if it is there more than once that it gets all of the data.
Here is my macro.
[Code] ....
I will also attach the document of what I am looking for : Data 1.xlsx
I need to query a master spreadsheet and pull out all the rows on it that have the same name as I have in cell A1 of my spreadsheet
e.g.
Myworkbook, sheetname = queries
Cell A1 = "ABC"
The Macro queries the "test Data" workbook (c:myfolder estdata.xls) "sheet x" and looks in column A for ABC, if it finds ABC it will copy the row and paste it into Myworkbook, sheetname = queries. There are often several rows of data that need to be copied that all have "ABC" in column A.
Also, in column B of the test data workbook, is a number, the highest value denotes the "version" of the data, the highest number is always the latest version of data that should be retrieved, e.g. if 9 the highest value in column B then all data that has ABC in column A and has 9 in column B should be retrieved, all other records should be ignored.
I guess this is a sort of a macro loop but not too sure how to do it.
Excel macro to retrieve some valid data from one sheet to another which has some values.
Description;- I have made an excel sheet for daily stock details which contains data like stock in, out, daily production for various products. I have made a menu which contains button of stock in, out and production, and one sheet for current stock where I enter In and out. I wand a macro when I click the button from the main menu it gives me all the production details taking from one sheet and past it in different sheet in the same workbook but it should not give the details of those products which does not have and production.
I have a spreadsheet using an Input Form (user cannot get to the spreadsheet to enter data - they must use the form). This is working well.
What I need to do now is allow the user to retrieve data that is already entered. Example: Spreadsheet has 100 unique records (rows). In each is ID, Name, State, Dues, and about 20 other columns of information.
I need to allow the user to key the ID # and then up pops all the other information for just the ID provided. I will also need the ability for them to then change that information if needed.
So if they key 13WIL they will get Wilson, TX, $50, etc. And then they can make changes to any field needing a change. Add a phone, change and address, etc.
I am trying to create a form with dropdowns (see attached file), so that when a particular company is selected in the first drop down, the second and third only display the relevant business units and departments respectively.
The INDIRECT function has confused me and I am not sure how to use it.
IS it poss to sort an entire sheet by one column then another, but to exclude certain rows that include certain data.
This is what i mean, I have a sheet where data is entered on a daily basis. this data has tobe kept with in its dates. Each day starts with a start time and ends with an end time, and on occasion a break is included within the data. then between dates there is a blank line.
IS it poss to sort the data and exclude the blank lines and the start, end and breaks within the data, but keep the data within date, so all data for the 12/4/09 stay within this block...?
I have a inventory report that has multiple stores. I am trying to add the total of all stores in two columns named "AE" and "ASC". Anytime the store name says "AE" i want to add the totals of that store in the "AE" column. Anytime the store name says "anything except AE" I want the totals to add in the "ASC" column. Hope this makes sense. How do I do this? Do I use conditional formating?
View 9 Replies View RelatedI have turned off show zeros on a sheet. This is good in general, but for certain cells I would like excel to show zeros if the formula output for that cell is zero.
Background information: I am working with a very large workbook, and the final sheet is my output file. This sheet is then exported to a CSV file and imported to another program in my workflow process. I need most blank cells to remain blank, so that they are interpreted as blank in the output program. Some of my cells are actually zeros which I intentionally inputted as zeros...those should be interpreted as zero and not blank.
I have a cheque list with Column A for Cheque number, Column B for Date, Column C for Payee Name and Column D for Amount
Cheque number ranges from sequence 1200 to 3600
Is it possible to set up a formula to sum the amount of cheques in column D based on the cheque sequence number of column A . In other words if based on criteria cheque sequence number is
i have a VLOOKUP formula
=VLOOKUP(W3,CAT_A,3,1)
where CAT_A is a dynamic named range on the CAT sheet, I also have CAT_B thru CAT_F
I would like to make the VLOOKUP formula neater by using INDIRECT and the initial letter from column I as follows
=VLOOKUP(W3,INDIRECT("CAT_" & I3),3,1)
However I keep getting #REF, where have I slipped up
I have a workbook with several columns, some of which are for internal company use only.
Sometimes we need to send an updated list to business partners, but they don't need all columns.
Is it possible to make another workbook which takes only specific columns, and excludes the stray info at the bottom of the original workbook? See examples.
Alternatively: Would it be simpler to make an extra sheet in the original workbook, and somehow choose "Safe only this sheet to a new document" whenever someone requests the list?
i have a column that has some values in it. Most values are #N/A but some are numbers. I am trying to create a function of some other snipet of code that would substract one value from the previous value and have the result next to the first value.
I have a spreadsheet with many many columns...the top of each column has a string (it's a bird name). Below in the same column are various blank spaces or letters (defining species abundance, if there), example:....
View 2 Replies View RelatedThis is a formula in an existing spreadsheet that I need to amend.
{=SUM(IF(WeekNo=2,IF(Workgroup="REASSIGN - CAP",1,0)))}
WeekNo2 and Workgroup are names defined and are offsets that define two separate columns.
I have created a new name realclosetime and it is an offset to another column of data.
I have tried to change the existing formula so that it is not actioned if the realclosetime is empty. Its a date and time field and if it is not completed I dont want to be including that row in the SUM calculation otherwise I end up with N/As.
I am trying to copy selective data from one worksheet to another. My intent is to copy data in column F below to another worksheet in Column H. I only need to copy numeric data in Column F. Below is the screen shot of my data set. There are 244 Batches and each batch contain 4 members. In another words, I want to copy data for 4 rows (F2 to F5) then skip three rows (F6 to F8), Then again copy 4 rows (F9 to F12) the skip 3 rows and so on.
Here is the sample data -
ABC DEFGHIJ
1
2JohnAccounts841.5
3JackReceivable842.5
4JimPayable842.7
5JustinClaims843.9
6Next lines of Data contain expenditure for batch 2
[Code] ..........
Option Explicit
Sub copypaste()
Dim lastrow As Long, i As Long, j As Long, sheetlist As Long
Dim ws As Long
[Code]....
i just wanna execute this coding from particular tab to particular tab (in detail to say- if am giving from and to tab as A & D, then coding should be executed to A, B, C and D alone, and remaining should remains as is).
We want to transfer row data based on criteria in that row to another sheet. We only want to transfer certain cells in the row. We have a column that is named status that we want to trigger and identify the row for transfer and then its location in the new sheet. We tried vlookup but it would only take the first instance of a specific status in the column and not take subsequent instances. We then tried the index function but could not get it to work correctly. I hope this makes sense. There will be a high volume of data being inputted continuously so I don't think that copy and pasting is a viable solution....
View 9 Replies View RelatedI have a document that has a sheet for each project, 3 at the moment. On this sheet are various text & currency fields, 24 in total.
I want to create an overview document for all of the projects so it will display lines from the individual project sheets that are currently showing as NOT complete.
To clarify I currently have 4 categories for status at the moment: Planning, In Progress, Awaiting Inspection Report, Complete. So I want my overview to show anything that is not complete.
I also have no need for all of the 24 columns in the overview either, I've highlighted about 9 at the moment.
I think maybe a lookup and If combination is my answer but I'm not altogether sure if there are other functions out there that I just haven't come across yet, or how to combine an if and lookup.
Also I would like the data to be sorted firstly by Inspection Location and then in date order if possible. I know I could use the auto filter option once the data is pulled across but it was be easier for the other users of this document whose skills range greatly.