I have been looking everywhere and I can't seem to find what I am looking for. I want to create a Macro that will go into a folder, search every Workbook (each with multiple Worksheets), and copy and paste the data onto a single Worksheet. The data that I want will begin in Row 3 of each Worksheet (the first two rows are titles and headers). The formatting will be the same for each sheet.
The only other part is that Column G has the following formula in each cell that I would like to keep if possible.
I have had a good look around and have found some scripts that look like they can help but do not. here is my situation
I have 300+ questionaires in excel that contain around 20 questions with each question having the possibility of 5 answers i.e A1 A1Do you like this service, answers will be scored in cells F1,G1,H1,I1,J1
What I would like is for a master document to combine all the data into 1 document so I will know how many of the 300 have F1,G1 etc.
In the filed F1 the variable may be X or it may be a number, I would like to add up the number of non blanks in that field, from 300+ closed workbooks
I would like to merge multiple workbooks into a master workbook. All the individual workbooks are identical and only have 1 sheet, and I would like to append them to the master as worksheets then sum them all together. The front sheet showing the totals from all the merged ones.
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files. My question is how do I write the code so the source files populate the master in a specific order. For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit Sub ConsolidateSheetsFromWorkbooks() 'Author: Jennifer Starr 'Date: 7/12/2013 'Summary: Open all files in a folder and merge data (stacked) on all
I have this macro to go to a specific folder and open up all of the files in the folder and merge them into a worksheet.
I want to change it so the user can select the files to be merged.
Sub MergeFiles() 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
I have 5 worksheets, representing data from 5 days, M-F. On a weekly basis I open all 5 files and manually copy, and paste all into one large weekly file. Does not take very long so I am probably just being lazy but if I could find a simple solution to merge all 5 into one quickly it would be great.
The one solution I was planning on implementing was to cycle through all OPEN workbooks, copy the data into a new workbook, and close each workbook as the copy and paste is done. This will work but to me its 'dirty'. Each workbook only has one Worksheet.
I have 24differnet workbooks each with one sheet with data. Each sheet has the sae headers with differen amounts of info under the headers. I am looking for a quick way to get all 24 sheets in to one workbook instead of copying and paste.
I have a client that I process their data every Monday and am already using some VBA code on it. I receive their data in four workbooks that have the same name every week. CABAM_U262.XLS CABAM_U265.XLS CABOB_U262.XLS CABOB_U265.XLS and the files are put in the same subdirectory every week: D:HealthcareNewBusiness
What I'd like to do is run some VBA code that would automatically look in this subdirectory for the four files and merge the rows of account data (rows 2 - the last used row and columns A - S) from these four workbooks into one workbook and have the header row from any of the four workbooks (they're all the same) placed in the new "merged" workbook at row 1. Could anyone provide to me the VBA code that would do this?
Each month I run 2 separate reports and have macros that break each report out by distributor. The end result is each spreadsheet creates a new tab and new workbook for each distributorís information. Each spreadsheet has information for 30 distributors, so I end up with 60 new tabs and 60 new workbooks.
Example: Spreadsheet one separates detailed sales information for: Abc Company 123 Company Misc. Company
Spreadsheet two separates summary sales information for: Abc Company 123 Company Misc. Company
Currently, I save each spreadsheets new workbooks in a different folder because each spreadsheet creates workbooks with the same distributor name. I then go back and open each spreadsheet with the same name and move a sheet from one workbook to the other and resave. Now I am trying to figure out the best way to merge these back together and am struggling with where to start.
I could keep opening each of the 2 new spreadsheets that are alike and copy or move sheets but with 30 new tabs to move to the matching spreadsheet it becomes time consuming. Iím hoping thereís a better way!
Iíve attached the Sales Detail Example and Sales Summary Example to see how the spreadsheets are being broken out but had to remove some data to trim down file sizes.
Two support staff added data to the same base document. I now have two files that I need to combine into one. Is there a way to merge the two together so that I have one workbook that contains all the information entered by both users? We use Excel 2007.
I'm sure that there is an excel function that can do this but darned if I can figure out which one it is. This is what I would like to do: I have two different workbooks. Each workbook has one worksheet that has two columns of data. The first column is a part number and the second column is a price. The first workbook holds my master data. Starting at the first column of the first row on the second workbook I wish to compare the part number held in that cell with the part numbers held in the first column of the first workbook. If there is a match then I want to take the associated price from the second workbook and place it in a cell to the right of the corresponding part number in the first book - actually in the first open cell in the third column. I wish to do this automatically via macro, or otherwise, for all part numbers in the second workbook. The macro will also need to recognize that some part numbers in the second workbook may not appear in the first workbook in which case that part number is skipped.
The end product is my first workbook that has been transformed from two columns of data to three columns of data - one part number and two price points although not all part numbers will have the two price points. If it helps I can copy the data in the second workbook and paste it in to a second worksheet in the first workbook and do all the work in the one workbook.
Ive just started using vba for excel and put together a very simple peice that uses the goalseek function. See below.
Public Sub calc_vol() Dim currentcell As Range Worksheets("sheet1").Activate Set currentcell = Worksheets("sheet1").Range("bl2") Do While Not IsEmpty(currentcell) With Application . Iteration = True .MaxIterations = 100 .MaxChange = 0.00000001 Application. ScreenUpdating = False Application.Calculation = xlCalculationManual End With ActiveWorkbook.PrecisionAsDisplayed = False currentcell.GoalSeek 0, currentcell.Offset(0, -13) Set currentcell = currentcell.Offset(1, 0) Loop Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub
The code works great (a little slow - but fine for 65,000 lines), however i have nearly 100 workbooks that i have to run this macro for. I was wondering if anyone knew how i could manipulate it so that i could incorporate all 100 files into the macro, therefore running the macro once for all 100 files. If this is not possible is it possible to link multiple worksheets.
I am looking for a way to close a group on Workbooks that are in separate instances of Excel.
These Workbooks get opened similar to running a Powerpoint program, but much different usage than Powerpoint.
AS AN EXAMPLE:
I have 7 Workbooks open in Excel . . . Could be more or less.
Each workbook has 2 sheets, the "User Sheet" (not the actual name, as all user sheet names vary) and a sheet named "Setup." (Always named "Setup.")
On the "Setup" sheet I have a list of all the possible Workbooks that I want to close. For reference purposes only, I'll call this list "To Close". All 7 Workbooks would be listed, but not all of them would be opened. If any particular Workbook on the "To Close" list is not open, it would simply be bypassed.
Example of list: Column A
Row 25 has the name of Workbook A Row 26 has the name of Workbook B Row 27 has the name of Workbook C
And this would continue down to the last Workbook possibly open. I will know how many Workbooks could possibly be open.
Other workbooks could also be open, but they would not be closed by this macro. In this example, I would only want Workbooks A & B & C closed.
I would have rows 25 thru 27 (or more) listed in the same location on the "Setup" sheet of each Workbook.
Each "User Sheet" would have a macro called "Close Group"
The macro would have to search through all open Workbooks and when it found one of the Workbooks listed for closing, it would close that workbook. Then, the macro would look for the next Workbook on the "To Close" list.
The Active Workbook would also be on the list and that also needs to be closed. I would guess that this would be the last Workbook to close, but it really would not make any difference if the macro would still work. I think the macro has to have the active Workbook open to close the others.
After the list has been gone through, the macro would stop.
NOTE: Each Workbook has it's own "To Close" list. If it would help, I can leave the Workbook Name A off of the list in Workbook A. I would do the same on Workbook B, etc. Then the Active Workbook could automatically be closed at the end of the macro.
Some copies of excel workbooks are made using save as option for data entry purpose. Each copy has same file structure because they are created using save as option. Is it possible to merge them (preferably preserving the macros) ? The workbooks are not shared because they were protected sheets and so macros wont run.
I have 4 different workbooks that I want to merge into one workbook. I tried moving the sheets from these workbooks into one workbook but to my surprise all the vba / macros didn't transfer over to the other workbook.
I searched here but all I can find were people that wanted to make a summary workbook or merge workbooks into a worksheet which isn't what i want to do.
I just want to simply merge these workbooks into one workbook without losing all the marco and vba stuff that are in each workbook.
I have 400+ workbooks in a folder. If they are all .xlsm (macro-enabled) workbooks, how can I import a macro (.cls file) to a particular sheet (sheet1) in each workbook using VBA so that when a user on another machine opens one of them, the macro works? Right now, I have this code to loop through all workbooks; I only need the import function to go in the middle. Copying and pasting or importing manually 400+ times is just plain unrealistic!
Sub OpenfileUpdate() Dim strFile As String mFolder = "C:files1Files" strFile = Dir(mFolder & "*.xlsm*") Do While strFile <> ""
For work I have to create copies of the same workbook for every day of the month. I have got the following to do it properly, which may not be the best way so I am open to suggestions, but I don't want it to save this macro in every workbook. There are however other macros that I do want it to take with it, just not this one.
Sub SaveByDate() Dim a As Integer, b As Integer, c As Integer
' number of days in month + 1 (this is just a test; for Jan a = 32) a = 6
' start date b = 1
c = 0 For c = 1 To a If c < a Then
MyMonth = "Jan" MyDay = b MyFileName = MyMonth & " " & MyDay & " 2008 daily report" ActiveWorkbook.SaveAs Filename:=MyFileName b = c + 1 End If Next c End Sub
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.
E.g. I'd have 3 workbooks: - BrandA.xlsx - with the 2 user listed - BrandB.xlsx - with the 1 user listed - BrandC.xlsx - with the 1 user listed
I have 17 workbooks which contain similar data and formatting. The only variance is the number of rows of data in each. I want to write a macro in the Master worksheet that will accomplish the following
1. Create a worksheet name in the master workbook with the same name as the workbook that it's pulling data from. 2. Copy over the column headings from columns A:P to the newly created worksheet on the master workbook 3. Copy over all rows that contain the word "down" under column D titled Operational status
The naming convention for the master workbook will be titled "All Switches". The naming convention for the worksheets containing the data are titled Port_Map_on_tiegcsw01, Port_Map_on_tiegcsw02, Port_Map_on_nypgcsw01. There are other names , but I can reference the code an update accordingly.
I found some searches on the forum for creating a macro to copy data between workbooks, but not quite sure how to write the macro for the three functions.
I have been given the following code to transfer data from multiple workbooks within folders and subfolders to retreive the same line of data from each of the workbooks and place them in a master workbook.
the folders are set up as follows,
there is a main folder, (a yearly folder) within this are 12 monthly folders (named January to December) within these are four weekly folders (named week 1 -week 4) contained within these weekly folders are the workbooks that i wish to copy data from.
for example a1 - k1
the code i am using transfers the file names but comes up with #REF! instead of transfering the data