I have one main Excel file with information (in example file - columns A & B). I want to create 4 other Excel files that will draw from the main one (in example file - columns D, F, H, & J). I want the information in Excel file A, B, C, and D to be continuous information that matches the main file. For example, the letter A matches up with numbers 1,3,4,10,11,13,15,17, and 19 in the main file. When I open Excel file A, I want a continuous list of these numbers. And I want this for all 4 Excel files which draw from the main one.
I need to make a macro that creates a specified number of graphs depending on the file's number of data sets. I know the number of sets that are in the data, and I know the number of data points that were taken. Here is what I have:
Sub Graphs() Dim Startpoint As Integer Dim Endpoint As Integer Dim count As Integer Dim xStart As String Dim xEnd As String Dim NumberSets As Integer Dim yStart As String Dim yEnd As String Dim DataSet As Integer Dim Data Startpoint = 11 'The first set always starts in row 11 Endpoint = Range("L4").Value + 10 'Thefirst set always ends after the value of L4+10 NumberSets = Range("L7").Value 'number of times I need the loop to work count = 1..........................
I have a folder with 20 Excel files. I'm trying to create a master list of all the tab names. I can see all of the files opening, but it only copies some of the names.
VB:
Sub GetTabNames() Dim wkBook1, wkBook2 As Workbook Dim stFilePath1 As String Dim FileList(1 To 18) As String Dim iLoopSheet, iLoopProg As Integer Application.ScreenUpdating = False
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.
I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.
Option Explicit Private Sub Worksheet_Change(ByVal Target1 As Range) If Target1.Column = 1 Then
I have a directory which contains many files, they are all names based on their locations. eg. Burwood-File1.xls,Burwood-File2.xls,Burwood-File3.xls etc
I have a master that which will contain the branch in the first column.
I have defined the directory location in a separate sheet as well as email template.
When I click on the Send Email button I want ti to attach the files that match the Branch name.
I have 8 different files all have a set of data in them
each one has a long list of (column a-n) however the number of rows change by date. I need each file copied into the finalfile.xls one after another. in the files that will be merged into the final file the final row i need copied is blank. I have all the copy formulas and everything set, I just need a range to copy that automaticaly takes cell a10 to the first blank a cell from each file and pastes it in the finalfile.xls under the last paste so they dont over write each other.
I have about 7000 Excel files that I need condensed to one file. The data shown that I need in example one. In this example the data needed is in column B20 then B3-B19. My problem is the data in B20 is sometimes above or below.
In the other attached file(ExampleOutput) is how I am trying to get the data outputted.
Do you think that a macro/script con go thru all 7000 files automatically in a row to do this?
I have to delete data from D15 to AZ129, D168 to AZ282, D312 to AZ426 in 46 excel files, saved in the same folder. I go through the process manually by opening each file and then deleting data.
way to take the data from a specific cell that is in the sheet1from every file and put those result in the A2,A3,A4,... cells in the sheet1 of new excel file.
I want to have a primary sheet that refers to two another sheet in the same document.
Basically, the person using the spreadsheet will tell the first sheet what data they're looking for from within the dataset contained in the second sheet and the third.
The first sheet will then have an area which it outputs which data matches the criteria.
Here's some specifics. It's for Pathfinder Deities. The user will input which domains they wish to use and the sheet will output which deities, if any, will be appropriate to select.
The second sheet contains all the Cleric Domains and which Deities are associated with them. The third sheet contains the alignment of each Deity.
If it was not obvious, in the first sheet, the player will indicate which Domains they wish to use for their character as well as the alignment of the deity they wish to use. Once they've indicated what their choices are, the first sheet will show which deities are available given their choices.
I'd prefer to do this without using macros, but if it's easier to use them, then I'll just have to go for it.
The screenshots are too large to post, so I'll just post my project: [URL] ......
I have multiple workbooks in a directory and I need to copy a range of cells from 1 particular worksheet in each workbook and paste the data into the " consolidation workbook (Lar.xls)" that contains the macro I'm working on. I have found the code to open & close all of the workbooks and I can even get to the particular sheet in the workbooks but I cannot select and copy the range of cells that I need (I cannot get it to select any specific cell on the worksheet). Here's the code I'm using, have tried many different variations with the same problem.
Sub RunCodeOnAllXLSFiles() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "C:Temp1" .FileType = msoFileTypeExcelWorkbooks '.Filename = " Book*.xls"................................
I am trying to use the following formula to pull out data from multiple excel files in a folder called "Certificate".
='D:SSR Sec and Techcertificate[STUDENT 2.xlsx]Student Record'!$B$10
='D:SSR Sec and TechcertificatePath [STUDENT 2.xlsx] File Name Student Record'!$B$10 Sheet name and Cell reference
The formula works without any problem. I want to replicate the formula to extract the same data in multiple excel files. In this case only the second part of the formula needs to change to "STUDENT 3", "STUDENT 4", "STUDENT 5" and so on. I have created a column in excel with those values. I am trying to use the cell contents in the above "formula", but I cannot seem to replace this value in the formula. Needless to mention that I tried to drag the formula, but it does not work. I am attaching the two excel files.
Currently using the following code to "pull" a range data from a closed workbook.
[Code] ......
This works well if I know which workbook and sheet I will be getting the info from. What I would like to do is add a function that searches through a few workbooks looking for a worksheet name that matches a value in a cell in my original workbook.
ie. Workbook 1, cell A1 = "Jelly"
search through excel files for the workbook that contains the worksheet named "Jelly" and then copy a range from that worksheet back to workbook 1.
-I receive files everyday by email and saved in a folder C:TEST -each file saved by name Name+daily date •( like: sale_20091214, sale_20091215, sale_20091216 ) -file is in csv/Comma delimiated format -each file have 7 columns A to G and contain 256 rows -i only need 7 or 8 rows & only need value in Column 'F' -the rows i need are going to be same each time -------------------------------------------------------------------------- I want to save automated data in a file name: MonthlySale located in c: Fore each ROW data should be saved in new column next to last saved column. . Example: data from file sale_20091214 should be saved in column B, sale_20091214 should be saved in column C, sale_20091214 should be saved in column D and onward.
I have the job of producing a survey in Excel and collating the results anonymously, to help with this I'm looking for a chunk of code that will enable me to pull a specified range of data from all the files in a particular drive with a filename starting with a specified word and insert them all in to the same blank sheet.
I have about 1200 text files with data regarding different machines.These file contain a line called 'Validation date" and also the programe number on different line.
I need a macro which will extract this date for each program number and write in excel in two columns like program number and validation date.
I have tried with some of the macro help available on this site,but I have to do this one at a time.The time required doing this way is same as going in each text file and copy/pasting in excel.Is it possible to automate this process.
I have 10 rows and 3 columns or words(data). What I am trying to do is export this data one row at a time so that I end up with 10 text files. Each row needs to be inserted into 3 variables within my text file. (Variable1,Variable2,Variable3) and then saved. Each saved text file needs to be named Variable1-Variable2-Variable3.txt. End result should have 10 text files that are named corrosponding to each row of data and each text file should also have the 3 variables replaced with the corrosponding row data.
Right now I have to manually rename the text file (Variable1-Variable2-Variable3.txt) and then manually open the text file and select EDIT-REPLACE and insert the data 3 times. I have to do this for many many text files and it is becoming monotinous.
My situation is the next: I have one columm with 70000+ rows (starts from B3 to the final). I need to copy the data every 10 rows and save it in multiple txt files, example: B3:B12 ----> 00001-00010.txt B13:B22 ---> 00011-00020.txt B23:B32 ---> 00021-00030.txt B33:B42 ---> 00031-00040.txt
and so on.....
the macro (TextMe.xls) of xlite almost does what I am looking for. But, how to change it in order the macro does what I need?.
I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:
1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst) 2. Copy their system info to each respective worksheet
I have .Raw data in 3 different configurations, which can be opened in Excel.
I want to automatically extract relevant data (Columns) from these 3 different types of .Raw data before running analysis using chart using Macros.
Now here is the problem, Data from the .Rawdata needs to be accumulated. That is, I have multiple sheets of excel, which I have to open manually and extract specific data from individual columns onto another sheet to accumulate every data before running analysis.
How to tackle this situation?
1. Able to detect the right type .Raw data for use in Excel. 2. Extract data from many excel onto one main sheet, using Macros.
I have small bit of import code (below), that I am looking to modify. The data that I am importing is for an inventory tool that determine optimal on hand quantities and alerts to any issues. In the past there had been one inventory source (one .csv data file). So I populate that to one sheet and modify the data on that sheet. I now have multiple data sources that I need to address.
There are two things that I would like to add/be able to do.
1) I would like to add a dropdown or set of multiple buttons to the popup window that says "Select the OnHand Report". Previously I only had one report source which I posted to sheet "orow0205". I have 2 other data files now that I have to import and they need to go to sheets "orow0206" and "orow2144". I am grabbing the data the same way on each sheet so that doesnt need to change I just need the option on the pop up window to determine the sheet name I want the import to go to.
2) The files that come from the sources above have a file name like "20121213_00000_groupstatistics.csv". Where the first eight digits represent the date the file was generated. If possible, after I select the sheet via the popup solve above, I would like to have the date populated in "MM/DD/YYYY" format in the last column of the imported data (the same date for each row of data imported). The file imported has no date data on it and can represent any day (which is why I am not using some form of TODAY formula).
I have about a thousand Excel timesheets that all contain 'hours worked' data in a column. Each row contains the area of the project they have worked on and therefore the amount of time they have spent on it. The timesheets also contain the person's name and a w/e date.
I want to sequentially work through each timesheet held locally in a single folder and copy the person's name, w/e date and the hours held in the column into a single new spreadsheet. I need to transpose the data so each amount of time spent on an activity ends up in a column.
I have attempted to record a macro for this but each time I try and run it after the intitial run, it moves to a completely different cell or set of cells to the ones I have directed it and consequently there is no data copied to the new sheet.
I believe this is the first problem..! The second is working through a high number of spreadsheets held in a single locattion but whilst browsing this site I saw the "Excel VBA Loop Through a Folder of Excel Workbooks" page and think this should work fine.
I have a few CSV files that sits in a folder ie C:/Data and I want to split the data in those files into different sheets. Each of those files contains rows of data with an account number. For example, I have 3 files that contains transactions and they have accounts "Orange", Apple, pear, I want to put all transations with account apple together and all Orange transaction together etc etc.
So, today my manager came to me and informed me that he wanted me to do a little data entry for him... WOW... So I have about 2000 invoices to go through(all excel files, in their dated folders... 12-2-2013(folder) and the filenames are 12-2-2013(1), 12-2-2013(2) etc.)
I need to take these individual files and have certain cells from each in certain cells of a new workbook for example...
file 12-2-2013(1)... I need cell C3 from that file to go to cell A2(and this will be the same for all the other files... 12-2-2013(2) cell C3 needs to be in A3 etc etc etc.. this is the sale date) file 12-2-2013(1)... I need cell a15 and b15(they are both "stretched out" cells) to be put in C3 of the new workbook and so on.
is there an easy way/formula to do this all? I have to have this done by friday at the latest(tomorrow preferred @@) I have to input data for 7 cells...
I need to create a dynamic template for importing data from multiple .txt files to my workbook on a daily basis.
I recorded the macro for importing ONE txt files and starting to deveop it into looping through multiple txt files and sheets but to no avail.
1) Currently I have 4 named ranges
FileLocation = Location of my source file FileName = All the file names of my txt files SheetDestination = The sheet to be paste for each txt file (further explaination below) FileFormat = .txt (for VBA purpose)
2) The macro recorded was in "ActiveSheet", thus I thought of looping through each sheet (put the activate code at the first line) for the codes to run.
3) ColA ColB 1SP.txt 2IND.txt 3US.txt 4 5 6
Column A is my SheetDestination, while Column B is the respective filename for source txt file. My final intended result would be simply macthing sheet 1 against SP, sheet 2 against IND, sheet 3 against US... so on and so forth....
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5 E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
I need to find if there are matching addresses in 2 different excel files. If the same address appears in both files, I would like the new worksheet to return the address along with the sale price from the 1 file and the rental amount from the other file.
I have tried using vlookup but the problem is the exported data file contains the street number in one column and the street name on another column. I have attached a truncated example of both the rental data and the residential sales data.
