Is there a way to automate the importing of macros into a list of files ? I have a number of files that I want to add some code to and was wondering if this could be automated as opposed to adding the code to each file manually as it's going to be a regular job.
I've written a routine to loop through the list of files and open them, I just want to know how, if possible, i can automatically import the required macros.
I have two spreadsheets. One with 1,000 names and addresses, the other with 5,000, names addresses and birthdays.
Within that spreadsheet of 5,000 contain the same 1,000 contacts as the other spreadsheet. How do I import the birthdays of the 1,000 contacts without doing it by hand. Is there a macro or forumla that could do this?
I run an online baseball league, and the game I use to simulate our games has issued a new version which has updated ratings for all the baseball players. I have exported rosters from the new game and rosters from my online league to .csv files and what I want to do is find which players are duplicates in both files and line them so that I can scroll both files simultaneously and update my online league file easily. I also want the non-duplicate players to be sorted below the duplicates so I can deal with them later. Someone on the other forum posted some dynamic named range code and two macros and gave me some vague instructions on how to use the codes. I have posted the codes below with their instructions:
In both:
a) There're defined 2 dynamic named ranges:
"DataTable" as: =DESREF(INDIRECTO(DIRECCION(COINCIDIR("//Player ID";updated_rosters!$A:$A;0)+2;1;1;1;"ml_rosters"));;;CONTARA(updated_rosters!$A:$A)-
[Code]....
I need to lock the macros in spreadsheets at work.
If I go to Developer>Macros I can edit any of the macros.
So to protect them I went to Visual Basic>Tools>VBA Project
What I don't understand is that there is already a password there.
How it is possible that I can edit any of the macros under Developer>Macros when there is a password which I haven't entered under Visual Basic>Tools>VBA Project
I'm having a problem with how excel opens my files. Up until yesterday when I would open any of my spreadsheets they would open seperately. I would have multiple files open across my taskbar and could easily switch between files with a simple Alt-tab windows function.
Now when I open multiple excel files it opens them under "one" file so to speak. The only way to switch between files is if I go to Window>then select the file I want or minimize within excel so I can see the other spreadsheets.
I am trying to find a vlookup formulae for multiple spreadsheets in excel.
I have the below data that i need to lookup, test and produce an output.
Main Spreadsheet:
Column B...........................Column C
A 0000564235.................... <Desc> <---- Desired output is Coumn C from searching all part numbers in each spreadsheet.
Spreadsheet 1:
Column B (Part No.) ..........Column C (Desc)
A 0000564235................................Hose
Spreadsheet 2:
Column B (Part No.) .......Column C (Desc)
A 02315645646 ............................Clamp
Spreadsheet 3:
Column B (Part No.) .......Column C (Desc)............
I was curious if there was a way to combine multiple spreadsheets that have multiple tabs all onto 1 spreadsheet?
View 9 Replies View RelatedI have a workbook that I add about 20-35 tabs in a month. To individually go through and protect the sheet will take too much time, is there another way where I can select multiple tabs so the worksheets can be protected?
View 8 Replies View Relatedi have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file.
is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file.
or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
after finishing a huge macro, the last step is to take row 1 of sheet 1 and insert it at the top of every worksheet.
This is the first way I tried it. It does absolutely nothing (except copy the row).
For Each ws In ThisWorkbook.Worksheets
Sheets("Sheet1").Range("1:1").Copy
ws.Range("1:1").Insert Shift:=xlDown
Next ws
This was another try, which resulted in a 1004 "Select method failed" error
I have a workbook to collate ratings for staff, there are three sheets, one is a summary of data, and two are sheets that can have data entered into them, called "Eligible" and "Ineligible".
I would like to write a macro to search cells in "Eligible" that contain a staff number, and copy this data to "Summary". If excel encounters a blank cell, i.e it has no staff number in it, it will move to she sheet "Ineligible" and perform the same operation, pasting the data below that which has been copied from "Eligible"
To further complicate things, every time the macro runs to populate "Summary" I would like it to clear the existing data in "Summary", to allow for deletions in "Eligible" or "Ineligible".
I have part of macro to import a txt file into excel but it is not working as it should. It is giving me a message "This file is not a recognizable format" but its just a normal txt file. Also after it gives me this warning it will open in excel and with every new txt file it gives me this warning and after it opens it.
The code I am using is:
Sub su()
Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
sPath = "C:Historical" 'location of files
ChDir sPath
sFil = Dir("*.txt") 'change or add formats
Do While sFil "" 'will start LOOP until all files in folder sPath have been looped through Set oWbk = Workbooks.Open(sPath & "" & sFil) 'opens the file
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
' do something
sFil = Dir
Loop ' End of LOOP
End Sub
In this line it gives me Runtime error 1004 Method 'Opentext' of object workbook failed:
Workbooks.OpenText (sPath & "" & sFil), Comma:=True, DataType:=xlDelimited
I'm trying to set up conditional formatting to show when an employee is both scheduled to be working for the current date, and is trained in a particular task.
I have a spreadsheet called "Training" for all of the tasks that employees can be trained in, with the employee names in each row, and the task names at the top of each column. The intersection of an employee's row with a task's column will have an "X" if the employee is trained in the task. This is the spreadsheet that I'd like to be formatted; if the employee is trained and also scheduled to be working, I'd like to the "X" to change to a different color.
There are separate spreadsheets within the same workbook for the schedule. This is a bit more complex since the since each week of the schedule has its own spreadsheet within the workbook; each spreadsheet is named for the Saturday of the week it is for, such as "06-07". I've been able to successfully have other formulas in the workbook auto-adjust to the current week's schedule by using variations on the TODAY function (something like INDIRECT("'"&(TEXT(TODAY()-MOD(WEEKDAY(TODAY(),1),7),"mm-dd"))), but I'm not sure how to incorporate this into conditional formatting. On each schedule sheet, there is a column for each day of the week and a row for each employee. The cell for a given day will contain the employee's schedule if they are scheduled, or it will be blank if they are not scheduled. So, the conditional formatting formula would just need to check to see if the appropriate cell was blank or not.
I am creating a MIS for my team. But I am now struck while creating dashboard. I have a workbook with 12 raw data spreadsheets for specific months. Now what I want is that in 13th sheet if I select January then 1st cell in 13 sheet will show the value January cell 1 value. However if I select February then 1st cell in 13th sheet will show the value of February cell 1 value.
View 7 Replies View RelatedI have several excel spreadsheets by which I would like to add together certain values in the column with the same columns of all the other excel spreadsheets. So basically, If one of the values are "SELF" , I would like to calculate how many times that value occurs in all the spreadsheets. Any easier way to organize the data to be able to do this also.
View 1 Replies View RelatedHow can i combine multiple Excel Spreadsheets into one Spreadsheet ?
View 3 Replies View RelatedI have a master spreadsheet that links to various other spreadsheet for budget figures. I have now created and updated the budgets for the 2008/09 budget year.
It is painstaking to update the links and browse to the new files one at a time and point them to their new folder. Is there a way I can update all of the link workbooks to the new directory in one fell swoop?
I need to combine these 6 spreadsheets into one single spreadsheet. It won't seem to allow me to paste it.
It needs to begin with 2012 and count down.
2011.xls2010.xls2009.xls2008.xls2007.xls
I have a workbook with two spreadsheets. One contains customer information. The other line items and shipping information. I would like to pull the customer number over to the line items spreadsheet. Both spreadsheets have the order number, but note there is a new line for every item in an order on the line items sheet. How do I accomplish this?
View 2 Replies View RelatedThe company deals with Machine services, we maintain, service and brake-fix machine problems, the machines are manufactured my ourselves and there are approximately 15 different machines.
We have a Field Service spreadsheet that is filled in by all service engineers for every job they go on, this is filled in with information like:-
Serial Number
Machine Type
Hours Run Time
Reason for Visit
Reported Problem
etc, etc
All the forms are identical so all the fields are identical
Field Service Record Spares 2013 Master Rev4 (2).xlsx (this is the correct file). The forms are then saved on a central server folder and stored in month order.
Is there a way of creating a master excel sheet to gather all the information from all the Field Service spreadsheets which would then allow me to search all sheets by serial number or fault, without me having to copy and paste all the information into the master sheet??
We have 10 service engineers and each fills out 1 sheet per job per week, so a 4 week job will have 4 sheets for 1 engineer. an engineer could go on 5 jobs in 1 week and would therefore fill out 5 sheets. so we could be looking at in excess of 500 Field Service spreadsheet per year.
I currently have a macro that can be used by multiple spreadsheets. Is there that I can execute the macro within any specific spreadsheet without saving it in each spreadsheet. I just want to place it in a general spot so that in case there is modifications, I only have to change it in one place.
View 14 Replies View RelatedI have created with most of it being done by Austrada (who has done a great job) code to copy multiple spreadsheets into 1 spreadsheet. We have run into a error Run Time Error '9'. Subscript out of range.
Sub CopyData()
'----------------------------------INFORMATION----------------------------------------
'You need to make sure you have activated the Scripting Runtime reference for the FSO to work
'--------------------------------------------------------------------------------------
Dim fso As New Scripting.FileSystemObject
[Code].....
I have 5 sheets in my workbook and I need to find out if a string exists in any of them. The string would always be in column L of the work sheet. I have searched many places but cant seem to get anything to work, is this possible?
View 8 Replies View RelatedI am attempting to amalgamate several (100+) spreadsheets into a single spreadsheet. The data is very simple (only three columns, several rows). I have written a simple macro to copy 5 spreadsheets (called TIMESHEET1, TIMESHEET2 etc) into 1 spreadsheet called AMALGAMATED TIMESHEETS. I would like to expand this using a FOR - NEXT loop if possible - or any other way that someone can think of! I cannot use the variable TIMESHEETx as this is not accepted......
View 9 Replies View RelatedI have a workbook that contains over 100 worksheets with stock data and price information. It uses a screenscraper to update the workbook each day with the latest day's price, and then exports each of these .csv files into a local directory. The macro for this is working. In effect it is generating a price history file automatically in my absence for me that can be read by charting software.
What I would like though is a macro to loopthrough and backfill missing price histories on each worksheet. I have a source of .csv price histories already, but would like to avoid having to cut and paste each of the 105 files manually, as it may become a regular occurence.
Each worksheet that requires backfilling has the stock code in the cell "A2", so that can be used to search for the filename Range("A2") & ".csv"
This is as far as I have got - however it results in a runtime error (91) Object variable or With block variable not set, pointing to this line:-
VB: Workbooks.Open Filename:="D:FinancialData SheetsSpreadsheetsPension" & Ws.Range("A2").Value & ".csv"
VB:
Sub BackFillData()
Dim Ws As Worksheet
For i = 1 To 105
'Sheets.Add
ActiveSheet.Name = "Fund" & i
[Code] ......
I am troubleshooting my macro that seems to cause a data shift with the Letter "F" when I import multiple CSV files into one spreadsheet. When I go outside of the macro and record a macro and import the CSV into a workbook it works perfectly fine. But there must be something in this code that is causing the shift:
VB:
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Public Function ChDirNet(szPath As String) As Boolean
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
[Code] ....
I have attached an excel workbook that shows how the macro imports vs. a regular import. Why "F" is causing a shift in the data.
Data.xlsx
I have an excel spread sheet with about 300 tabs. each sheet has the same column fields..I need to pull certain column fields ( the same fields ) out of each tab and export them to another spread sheet.
View 6 Replies View RelatedIm trying to import data from multiple worksheets. The data from these worksheets are scattered throughout the different work sheets.
I would like to extract the specific data from each worksheet into a master template that ive created. 80% of the worksheets templates match my master template.
Is there any way to do this without taking 1 week to complete?
i want to import data from multiple excel files (with same kind of data) into a master file with each import want to display name of the file from which the data is imported in last column on all rows. for example if file 1 has 10 rows with file name abs, after import the macro show display on all 10 rows in a blank column abc. then import second file and its name and so on.
View 3 Replies View RelatedThis is a great bit of code (I found), to import multiple text files.
I have one small issue -
currently it imports with each file going to a new column, like this: text file1 A1:A4, txt file2 B1:B4, txt file3 C1:C4 etc etc
And eventually i risk running out of columns because I'm working with a lot of text files.
Can someone tell me what to do to modify this code so it imports like this? :
text file1 A1:D1, txt file2 A2:D2, txt file3 A3:D3 ETC ETC
.e.g. first file imports across row1, second file across row2 etc
Sub test2()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "\BP1MELIS001SHARED_DATAEVERYONESolutions IntranetPriceMasterlogs" '