Looping Macro To Populate Master Spreadsheet?
Feb 11, 2013
I have a number of spreadsheets, all of the same format, saved into ("C:Email Attachments").
I wish to populate a master workbook, using a macro to loop through all of the workbooks in that folder, copying all of the data (Starting at B52-Q52) using Selection.End.Down, as the row count is always different and pasting it into the master workbook. Sometimes there may only be one row, so an IF statement may be required, to say if there is nothing in B53 then don't do Selection.End.Down. The overall result would be for all of the data from each spreadsheet is detailed as one big list in the master spreadsheet.
View 4 Replies
ADVERTISEMENT
Jan 16, 2010
How do I write a macro that will delete every other line on a spreadsheet? I can create a macro that delete one line using the Record a macro command but what do I ad at the end of the macro to keep it going until stopped with Ctrl Break?
View 13 Replies
View Related
Nov 11, 2009
A macro which compiles a list of entries and adds or summarises the cost.
Sheet1 contains multiple entries per group and a cost in ColumnB
Sheet2 contains only one instance of the group with a summary in ColumnB
Here's an example and I hope it helps;
Sheet1
A1 = November , B1 = 100
A2 = November , B2 = 50
A3 = December , B3 = 75
A4 = December , B4 = 10
A5 = September , B5 = 60
After running the macro with CommandButton1
Sheet2
A1 = November , B1 = 150
A2 = December , B2 = 85
A3 = September, B3 = 60
View 9 Replies
View Related
Apr 26, 2007
I purchased the book VBA and Macros for excel and it has gotten me pretty far in this code, but I am having difficulty with one code though. I want the Macro to run through a specified directory, and copy the contents of the specified cells (9th row to last row) from each file and paste them in the next available space on my master. Then repeat this process for each file in the file directory. So far, it is successfully going through each file, but it isn't pasting it to my master sheet.
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim ws As Worksheet
Dim NextRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "K:ESAR GroupForecasting"
.FileType = msoFileTypeExcelWorkbooks
.Filename = "*.xls".........................
View 9 Replies
View Related
Jan 4, 2010
I have attached a document paralleling a document I am working on. The dollar amount in each spreadsheet represent sales. I have entered in values into the candy, soda, and chips spreadsheet. I have also linked values for candy into the total spreadsheet. My question is can I somehow type something or drag the formula down to populate the other cells in the total spreadsheet?
The idea I am thinking but which I don't know how to implement is to list all the items (as in column G) and list all of the relevant cells (e.g. B1 in the Candy spreadsheet) as in columns H and I (Note that all items will have the same cells but the cells will have different values...e.g. all three items have a cell B1 and B2 in their spreadsheet but these cells contain different values). I then try and fail to create a formula in cell B3 of the Total spreadsheet. I am trying to create a formula of the following nature:
='(Spreadsheet Name From Column G)'!(Cell Name From Columns H and I)
The Second half of the formula doesn't really concern me (i.e. the cell name from column H and I). However I am perplexed as to how to achieve the goal in the first parentheses above.
View 4 Replies
View Related
Mar 11, 2012
I have a Main Customer Spreadsheet. I want to Auto Populate FROM the Main Customer Spreadsheet to a New Spreadsheet. I want to be able to key in a customer name on the New Spreadsheet and take the info for that customer from the MAIN Spreadsheet and fill in the blanks. I need to be able to do this several times a day.
View 3 Replies
View Related
Apr 23, 2013
A1:A10 contains numbert 1 - 10 (A1 = 1, A2 =2 etc.)
B1:B10 contians letter A - J ( B1= A, B2 = B, B3 = C etc.)
Condition: If the Value of cells in Column A is greater than 1 Then read the adjacent cell's value into an array (eg. MyArray)
I was able to use Dictionary object to read everything and then dump into an temp array
Is it possible to populate the temp array values into spread sheet wiout looping through each item?
The following code simply populate the first item in the temp array into each cell in the the destination
Code:
Sub MyArray_Test()
Dim MyArray As Variant
Dim i As Long
[Code].....
View 2 Replies
View Related
Feb 16, 2014
I have spent many hours trying to research and create a code to enable me to create a master workbook with very little success
I have attached the "master workbook"!!!
I have a file C:workjobs
In this folder I have many workbooks all named differently normally with the customers name and a description of work completed ie: Mrs Jones, Bathroom refit, etc and the file is updated with new workbooks all the time as we complete new jobs
I need create a macro that automatically starts when the masterfile is opened that grabs all the information needed from all the individual workbooks and then inputs the totals into the cells in the masterworkbook so I can then analyse the information
The master workbook I have attached is exactly the same layout as the individual workbooks
View 3 Replies
View Related
Jun 4, 2009
I am currently working on a project that has six worksheets with information to populate into one master worksheet.
I would like it to work as if the information is not in the first worksheet to search the second sheet and so on and so forth.
Here is the complex part, the information that I need does not always appear in the same spot…Example: Sheet 1 has Weekly Benefit under B20, Sheet 2 has Weekly Benefit under B22, and Sheet 3 has Weekly Benefit under B23.
Here is what I have, unfortunately not working very well for me…
VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
View 9 Replies
View Related
May 22, 2013
So lets say I have 3 different sheets each with information on them like this:
Sheet 1 Sheet2 Sheet 3
item1|2 item2|7 item1|4
item3|5 item3|6 item6|2
item9|7 item8|4 item7|8
The first part (code#) is a code for an item. The second number is the quantity of that item. What I need to do, is to be able to populate a master list which will pull the quantity of each item and sum them up. In the above case, the master list would look like this:
item1|6
item2|7
item3|11
item4|0
item5|0
item6|2
item7|8
item8|4
item9|7
View 2 Replies
View Related
Feb 8, 2009
I have a master workbook with 20 worksheets. 5 worksheets in the worksbook are distributed to field reps, but the remaining 15 worksheets are not. What is the best practice for receiving the 5-worksheet workbook from the field rep and importing the data into the 20-worksheet master workbook? If I merely use Copy Sheet I am able to copy the worksheets into the master workbook but then I have to remove the pathname references in each of the copied worksheets in order to map the data in the master workbook - which is quite cumbersome.
View 9 Replies
View Related
May 2, 2014
For example, I have a master sheet that has a drop-down list of the months January and February, which have their own sheets. I am trying to populate the sales of the week 1, 2 ,3 ,4 in the master sheet based on the same data for the month chosen.
View 2 Replies
View Related
Jul 19, 2009
I'm on a BTEC in college and my tutor wants me to create 3 spreadsheets, one of them is a Master spreadsheet which shows each pupils grade for each module, their over all end year grade and how many UCAS points that grade will earn them. The other two sheets are for the modules which will have the grades of the pupils in that lesson, Pass, Merit, Destinction or Fail. I hope you're still with me... I've attached an example of one of the 'Slave' spreadsheets as I call them.
My problem is that I can't seem to find a way of doing all this without constant nesting of COUNTIF. The code I have for my 'Unit1' spreadsheet's grade is:
"=IF(COUNTIF(B3:G3,"x")=6,IF(COUNTIF(H3:J3, "x")=3, IF(COUNTIF(K3:M3, "x")=3, "Distinction", "Merit"), "Pass"), "Fail")"
It works, but it's very messy. If this sounds confusing I apologise, the attached documents should clear some of the confusion up. Once the smaller spreadsheets work, the Master needs to use them to update it's cells. If someone has a Merit grade in 'Unit 1', the Master needs to show it too by somehow linking the 'Grade' cell from 'Unit1' to the 'Grade' cell in the Master spreadsheet.
View 3 Replies
View Related
Jan 15, 2009
i just want a macro to run over 4 sheets and combine onto one. I found this code -
Sub masterer()
if sheets(1).name "Master" Then
Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Master"
end if
For i = 2 To Worksheets.Count
Sheets(i).Columns("A:G").Copy
Cells(1, (i - 2) * 7 + 1).Select
ActiveSheet.Paste
Next
End Sub
This places all data in a line in row 1. Can the code be easily changed so
it puts each line from each worksheet onto a new row. So i have one big list of data ranging from columns A:G
View 9 Replies
View Related
Dec 3, 2012
I have attached the sheet :
Certain job descriptions require certain training. Drivers needs these two particular trainings and an office employee only needs that training...
I have a reference sheet with what trainings are required for what job. I then want the next chart with each employee name and subsequent job title to have the required trainings automatically checked. (I can take care of the conditional formatting after that).
Job Desc.xlsx
View 5 Replies
View Related
May 11, 2009
I have never really used VBA and so am completely stuck at this problem. I need to create a macro which auto-populates a master worksheet from the individual user sheets in a shared workbook. Sheet 1 is the master sheet "Team Stats". There will be an undetermined number of individual worksheets to accomodate new staff. Each worksheet will be identical, using columns A-I with row 1 having the headings:
Date, Name, Reference, Value, Price, Age, Purchased?, Destination, Add. Products (the last 3 columns will have a drop-down list which will be used to enter data into the cell). There will be a varying number of rows in each of the individual sheets. If possible I would like the macro to run every time data is entered into one of the individual worksheets. If this is not then it would be fien to update every time the workbook is opened.
View 5 Replies
View Related
Apr 16, 2014
I am trying to find a code that will allow me to generate multiple sheets depending on what is selected in one specific column. I have found a code that is perfect for this however I can't seem to set the range. For example instead of copying all of column headings I only need to copy up to column AN2.
View 1 Replies
View Related
Dec 17, 2012
(i) I have a spreadsheet listing all the investments details. For example,
Investment Investment Date Amount
xxx company 1/1/2012 $10,000
yyy company 2/1/2012 $20,000
(ii) Each investments have different investors. For example, xxx company has two investors: A and B; yyy company has 4 investors: B, C, D, E
I want to assign Investors Name and their Personal Investment Amount to the main investment spreadsheet. So that I can retrieve individual investor's investment positions.
For example, I want a spreadsheet showing B's investment. Then this spreadsheet should have details of xxx company and yyy company, as well as B's personal investment amount.
View 3 Replies
View Related
Jan 8, 2010
From a master (data entry) spreadsheet of work projects, I'd like to automatically create a tab for each individual contributor that lists all projects they are involved with. The thing is that being a contributor can mean being the lead, assisting with, or being the technician on the project (all columns in the master). To make the individual tabs easier to read, it'd be great to have that tab's contributor name in red, wherever it happens to fall in the various columns.
I've created an example that's a little simpler than reality - in real life there would be 20 tabs for the individual contributors and instead of 3 "Assist" columns there would be 20 columns.
View 7 Replies
View Related
Jan 19, 2014
how to highlight entire rows within a spreedsheet based on the information in a cell. I have gone to the conditional formatting and done:
=$A4="Needs Labs" and formatted that red, but when i go to note what areas it applies to, It will not highlight the row. I use the wizard box to decide where to apply the formatting like I saw on an online tutorial and dragged across the row, but nothing happened.
View 4 Replies
View Related
Jan 22, 2014
I'm creating a master spreadsheet and I want it to pull live data from other spreadsheets depending on a variable.
I've a cell that has a variable date. I've files saved that have this variable attached to it in the name format: 'filename variable'.
I want to pull data, hopefully live, from workbooks based on this variable.
For example, I have files: generic 01212014, generic 01222014, generic 01232014.
The date is stored in the master workbook say in A1 that I can manipulate by entering a different date to pull data from a different workbook.
='[generic 01212014.xlsm]Database'!$L$3
='[generic 01222014.xlsm]Database'!$L$3
='[generic 01232014.xlsm]Database'!$L$3
There has to be away to make the bolded portions of the file name variable, right?
View 2 Replies
View Related
Jul 12, 2013
Every week I receive a sheet of data that I need to import into my master spreadsheet. The problem is I have to adjust the data I receive to import it unless there's a better way of doing it. I'm trying to automate both the modification and the importing of this sheet. I've tried to create a macro to modify and import without any success.
To adjust the spreadsheet I receive I need to: delete or find a way to exclude the first 3 rows, the bottom row with data stating "complete" and columns C,D&E from being imported; change font, size, color, bold to match the main spreadsheet in addition to formatting; split data in column A and remove any special characters. Formatting is important because I have to be able to easily remove duplicates. Also, the number of rows in the sheet I receive changes weekly.
I've attached a workbook with sample data. The first sheet is representative of what my master looks like, the second is what I sent and the third what I've been modifying it to and then importing. As stated before, I'd like to have my master automatically update with the sheet I sent, but I'm at a loss having to reformat everything every week before importing. Having the sheet that's sent to me revised before I receive isn't an option. What's the best way to automate this?
SAMPLE1.xlsx
View 1 Replies
View Related
Nov 19, 2009
I went to add the count to the inventory, it keeps returning an error where the date is pasted from one sheet to the other. I had originally tried to reference the value of the date directly, but because it was being passed to a merged cell, Excel didn't like it too much, so I had it copy and paste it. That worked at first, now it suddenly doesn't.
View 2 Replies
View Related
May 22, 2008
To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.
I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.
View 14 Replies
View Related
Mar 20, 2009
I have directory with multiple workbooks. Each workbook contains multiple sheets (sheet 1, sheet 2, etc). On each sheet of the workbook, there are various data. The data I am looking to extract from these sheets are in the same cell of each sheet. (See data sheet –datasheet.xls). If I can extract these data without opening the file, it would be preferred. Otherwise, opening the workbook, extract the data, and then close the workbook is acceptable.
Desired Solution:
I would like the assistance to create a macro that will extract data from each sheet of the workbook in the directory at a time until all the workbooks and sheets within the directory are read. The macro shall extract data from cell B5, B6, B10:B20, and sheet name from each sheet of the workbook and copy these data to an active sheet called “US” on workbook, DesireResult.xls. The DesireResult.xls shows how the data should be copied over. Before copying the data to the DesireResult.xls workbook, the macro will prompt the user to enter a cell on the “US” sheet as the starting point to paste the information to. Prompting the user to enter the beginning cell shall happen only once and not for each workbook or sheet being read. The reason for this is because the “US” sheet will already have other data in it. After reading each sheet, the macro shall leave a blank row at the end. The data from the next sheet shall be pasted below the blank row. Note: all the workbooks contain macro and link to other file. When opening each workbook, the macro should automatically choose not to update the links.
View 4 Replies
View Related
Dec 19, 2013
I have tried to write the below VBA to copy a specific cell to a specific workbook. I have set the folder path in B1 and listed the file names in column E4 onwards. E1 being the number of files in column E. I get a run-time error 91 "Object variable or With block variable not set" on Current File = ActiveWorkbook.name.
Sub UpdateParameters()
Dim CurrentFile As Workbook
Dim wbOpen As Workbook
[Code]....
View 4 Replies
View Related
Jan 20, 2010
I’m looking to use a user form to populate a quotation template, I have been able to link the first part of the form to the sheet as these are static cells but I’m struggling in getting the items details area of the form to work as I need. Below is a brief description what I need.
Item Details area
Move down a line after every time the add button is pressed and clear contents of the text boxes for the next item.
Also I want to make each * denoted item required before you can add a new item
I’ve uploaded a copy of the file if my description isn’t too clear
View 9 Replies
View Related
Mar 10, 2014
I have a spreadsheet that contains an email address (Each row will most likley have the same email address but there are occasions when it could be different). Ideally I want a button that when clicked will open up an email, popoulate it with the persons email (that is in a specific column and row), add a subject and some text to the main body of the email and then attach the spreadhseet itself.
View 4 Replies
View Related
Aug 29, 2007
I run a web query to populate a table in my spreadsheet. The data is dynamic - and on some days, I will get a couple of extra rows in the table (its weather data, so e.g. when its raining, they will forecast an extra row of how many mm, etc...)
To give a simple example, right now I use Hlookup to locate the value of F3 (the date) in my dynamic range of B26:G26 and step down 3 rows, where my needed temperature values are for that corresponding day. This works great like this already: =HLOOKUP(F3,B26:G29,3,FALSE)
The big issue is...what happens if my target values are not always 3 rows down? If the query refreshes with any extra rows, this reference is shifted. (Well, my starting "Anchor" is shifted)
View 12 Replies
View Related
Jul 29, 2014
I need the tabs of a project action log to auto-populate and auto-delete in a master log. (So when something is added or deleted in a tab it is added or deleted on the master) I use excel a little bit for work and personal finance purposes but I have zero experience with macros or VBA.
View 1 Replies
View Related
