Macro/function To Take Data From Source File Into Base File
Jun 24, 2006
I have a base excel file for summarizing some data, the problem is that the data comes from a different excel spreadsheet. What I want to do is make a function that pulls the data from another spreadsheet into my base file. It would be easy if it were just one excel sheet, but this job would require where the data is pulled from a data file which has many modified versions.
Can anyone tell me how to do this? The files with the data will be structured the exact same but with different data entered in. I just want a button so I can click the file I want the data from and have it show up on my summarizing base file.
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons. Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later): Template file name: template_order.xlsm Template file location: \servershared emplate
Total list file name: total_list.xlsx Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10 The column heading for order data is located at A11:N11 The selection to be copied is A12:N550 - But only rows where column A includes data (not empty). (If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
I need to extract data from another source file using VBA. I have problems copying the extracted data and format into the required data format. And also, how do i delete the row that is not required in the output file, in the below example: The row, D0, is not needed. An Example Data Format From the SOURCE file:...............
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
If I have a spreadsheet with some data loaded in through XML and viewed as a map, what is the VBA command to reload/reimport the source XML data?
The scenario is that the source data (XML) may change at any time, and I would like to reflect this to the presentation layer (excel) at regular intervals. I have a loop set up but cannot find the command to reset the source XML data.
I have 2 excel files. One excel file (File A) is a basically a source file on a shared network that users input data into. The second excel file (File B) I created in hopes of basically using to analyze the data in File A. I used a data connection to access the data on File A while on File B. The issue I'm facing now is that when I refresh the connection, it locks File A from being opened until I close File B. Currently, I have the data connection to refresh only manually, so I'll open File B, refresh it, save, close, and then re-open without refreshing, so that way File A only stays locked for that short amount of time. Is there any way to make it such that when I refresh the connection, it doesn't lock File A?
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8 B2 to E9 C2 to F10 D2 to G11 E2 to H12 F2 to I13
1. In centain directory I have xls files where name of each file starts from "HR" string, eg. "HG_Control Mike.xls", ora "HR_Control Mark.xls". 2. I have a master xls file where I want to start a macro that will open each of xls "HR" files and copy selected rows to this master xls workbook (need to write this one too).
Problem is that opening of every "HR" files suposed to be protected by password. Users will be adding new xls "HR" files to the directory so I will not be able to change macro everytime new xls is added. So, I need to make a macro that will generate password base on xls "HR" file name and then I will use this password to protect this files and open them by another macro.
I'm trying to streamline some processes at work and have encountered a bit of an issue.
I have a source file containing a grid of data (9 columns) with a series of dates they relate to down the left side e.g. 13-Feb-2013. This data covers a whole year.
What I want to do is pull through data relating to a discrete month into a grid in a seperate workbook.
I have a feeling that date ranges and possibly some kind of lookup might be useful but am struggling to see how to pull it all together.
I have a lots of number arranged in a column. I want to take log of each number (on the base 2) and show the result in the adjacent column. I want this to be in a macro and the results to be displayed all at a time (I dont want to drag the cursor down to get log values for number corresponding to each row).
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
I have to run a report each morning and in once cell I need to pull in the contents of another cell from another worksheet. I'd rather not have to open the additional file each morning to copy and paste special the values so I'd like to add to my current macro to pull this data in automatically. I have to do this for two different reports/portfolios and the data I am pulling in for each portfolio is located in the same exel file but under two different tab names, the portfolio names 2010 and 2045.
Deliverables The spreadsheet I am pulling data in from is located here: K:Risk OversightMarket RiskTracking ErrorBARRA and the file name is: Daily Barra Tracking Error.xls
Tab name would be 2010 for the 2010 portfolio or 2045 for the 2045 portfolio The vlookup will be from "A32:B2500" and I would like to incorpoprate an IFERROR function that returns "" in the event of an error. Column "A" are dates and column "B" is the data I need to pull in.
Receivables I am pulling the data into cell "J23" of a summary report (sheet 2) and would like the vlookup to read something like this:
IFERROR(VLOOKUP("K1"Active.Workbook.Sheets2,[K:Risk OversightMarket RiskTracking ErrorBARRA"&"Daily Barra Tracking Error.xls"(Sheets)]."2010""A32:B2500",2,0),"")
This should return a blank cell if the date cannot be found in the data spreadsheet and return the correct data for teh correct date being referenced in my summary sheet.
how to do the bracketed parts for pulling in the right spreadsheet and tab.
I am setting up a "save as" macro that saves a file by replacing another file in a folder. Even though the macro has been recorded by approving the replacement (the prompt appears "the file --- already exists. Do you want to replace the existing file?"), when I run the macro, I am again prompted about replacing the file. Is it possible to avoid the prompt so the file is automatically saved by replacing the named file?
I do routine tasks every day which involves opening 3 different files and pasting data into my main file. The data is always in the same format, and the 3 files are saved in the same location (3 different folders through). The 3 files are saved each day and the naming convention is constant, with only the date changing. For example, the files are always saved in C:My Documents and the files are called test_05.02.2013.xls. Tomorrow the file will be called test_06.02.2013.xls and so on.
So each day I will be rolling a file forward and I want to bring in the info from each of these files based on the new day.
How to do 1, and I will do the others (because the concept will be the same).
I have a macro which successully saves a worksheet as new file to another file path....(below)...but I can't figure out how to close this new file and return to the original file...
Dim myPath As String, fName As String myPath = Sheets("Date").Range("C8").Text fName = Sheets("Date").Range("C9").Text Sheets("Sage CSV File").Copy With ActiveWorkbook .SaveAs Filename:=myPath & fName End With'
How do I create code that will copy and paste 30 rows of data
Copy from filename "Record1", worksheet name "CA3M", in column A and B, and Paste data in filename "Record2", worksheet name "CA3M"
- Column A 30 rows of data paste into A20:A49 of the new file and worksheet
- Column B 30 rows of data paste into E20:E49 of the new file and worksheet, prior to pasting these results remove all dashes "-" eg.0-0-1 = 001
The location of the 30 rows of data to be copied changes, so the code should always look for the following information in the row before and after the 30 rows to copy in column A and B:
- Find the 30 rows of data in Column A and B where the first row immediately follows the row with the cell result "Account Date" and the last row is immediately before the row with the cell result "Previous Page Page".
Also, I will probably have to update the code to repeat this function for several other worksheets within these 2 files after my database is completely built.
I am working with a bunch of files to do a dashboard. I have the main file which shows end results and what my users will see "Dashboard". I have about 10-15 files which I export weekly from MS Access. and I have the "Dashboard Data" file. In this file I have a sheet which links all the information for the files exported from MS Access. In here I have named ranges and dynamic ranges plus additional columns created to look up information in other tables. I use this file as my source data for my Dashboard File so that I can reduce the size of the actually dashboard. Dashboard Data file is about 5.5 mb and my Dashboard is 300kb. I have 12 branches that need to view the data over a radius of a hundred miles so the smal file are better.
My problem: I can get the Dashboard Data file to update without opening all the 10-15 other files. I cant however make the Dashboard file update without opening the Dashboard Data file. I have it to auto update without prompting but its still tells me that it cannot update. I was wondering if there is some VBA code I could use that would work like this:
I open the Dashboard File, Dashboard Data file opens so that Dashboard file updates and then close Dashboard Data File so that the user never has to see the Dashboard Data file. I dont want them to see it or have access to it.
I have a blank file and a data file for current year with formulas.
So I am trying to take a couple of tabs from current year data file and pasting them into the blank file.
My question when is do that all the formulas get referenced to the current years file. i want to keep the same formulas but reference the tabs on blank file.
Is there a quicker way to do this rather and going into all the cells and changing reference from current years files data to blank files data.
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
Set CopyRng = Wkb.worksheet1.Range(Cells(RowofCopyworksheet, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
I am trying to copy the first sheet in each file in the designated folder and paste it into a master worksheet.
Below is the code.
'Description: Combines all files in a specific folder to Format File for Upload.xls Sub MergeMultipleFiles() Dim path As String, ThisWB As String, lngFilecounter As Long Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet Dim filename As String, Wkb As Workbook Dim CopyRng As Range, Dest As Range Dim RowofCopySheet As Integer
I presently have a macro that, when run, takes to conents of C4 and C6 and saves a new version of the file being worked on into a folder on my desktop. I love the macro with the exception of one part: I don't want to be prompted to overwrite the file if it already exists. How can I change this macro so that, when pressed, it overwrites the file without prompting the user and waiting for their answer?
When it opens the desired file, it increments the file name by 1 each time it is opened (via the macro).
Example; The first time it is opened you see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages1"... I close the file and open it again (via the macro), and see the file name in the header read "P'Binder L&T Pages" for a moment, then it changes to "P'Binder L&T Pages2"... The file name continues to increment on every opening until the PC is rebooted, then it starts at 1 again.
Sub OpnLTpages()
Dim wb As Workbook Dim AlreadyOpen As Boolean
AlreadyOpen = False
For Each wb In Workbooks 'Scan open workbooks If wb.Name = "P'Binder L&T Pages.xls" Then ........................
I have a workbook entitled "Correlation Matrix". I download data from my data provider and then update it in excel each night. I have several worksheets in the workbook, one called "MCC". Today I made a copy of MCC, named MCC2, to experiment, then deleted it. Now, after I update my data in Excel it gives me the msg, "We can't open the source file '(Path)[Correlation Matrix.xlsx]MCC2'".
The data updates fine and all values are correct in the worksheets. I've tried closing and reopening the file, closing and reopening excel, but I'm still getting this msg when updating the data.
I have a workbook with a number of pivot tables as well as the source data for each pivot table. Some of the source data sheets needed to be deleted so now if I try to refresh the corresponding pivot table I get "Connot open pivot table source file '[Std Mthly Rpt.xls]Report_2'". I recreated the source file again and would like to know if I can link the pivot table to this new source file or do I need to create a new pivot table??
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB: Sub CopyFromFile() Dim fPath As String Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
This is a sample of the data with which I am working. I know that a macro can do what I need, but I am only versed in Excel formulas and not that much programming. I need to be able to first sort the data by Column C ("Element Type"), then by Columns E, F, G ("Year", "Month", "Day"). Then, I need to be able to copy all rows that have the same "Element Type" and "Year" to a new file, using the same header from the original spreadsheet on each new spreadsheet - doing this multiple times until the end of the file is reached.
Ideally, the new files would have a strict naming convention: XXXXXX-ZZZZ (YYYY).xls, where the X's are the value of the "COOP Station ID" in Column A, the Z's are the "Element Type" from Column C, and the Y's are the "Year" from Column E. If this theoretical macro were run with the Sample Data file I provided, it should result in the creation of five new workbooks. Is there a way to write a macro to do this, or at least something similar
