Copy, Transpose & Consolidate Data From Multiple Sheets
Jan 25, 2010
writing a macro that could save me loads of time. In the attached file there are 2 separate sheets of data ( named "Elements Innovation" and "Dinttap") that I need copied, pasted, transposed and consolidated into one master sheet (named "Desired result"). Note that the fields that relevant cells are highlighted in yellow. In reality I'll have many (~100) of these sheets that I need the data extracted from, and the work looks like it can be automated using VBA to speed things up.
View 2 Replies
ADVERTISEMENT
Feb 11, 2014
I have the following macro, however I am struggling to make it work on my file:
[Code] .....
I have attached my sample. essentially i have the dec, jan etc month tabs to consolidate into the summary sheet. i would like the data to be dumped from both sheets into the summary sheets. i will be adding a feb, march, april etc tabs as the year progresses.
I would also like column A in the summary sheet to repeat the name of the sheet the data is being retrieved from.
Attached File : sample macro_issue01.xlsm
View 4 Replies
View Related
Sep 23, 2007
I have been working on this project for some time and still haven't got anywhere with it. What I am essentially trying to do is create a master summary workbook where by you press a button to activate a chunk of VBA that will grab the same range of data from multiple workbooks but also multiple sheets in those workbooks, only problem is the number of sheets in each workbook are constantly changing so I need first find some way of accessing a closed workbooks, then I need a way of looping through a segment of code that will look into approximately 10 workbooks all with a different number of sheets and taking a fixed range of cells out of each sheet and for every workbook make a new tab and paste the information in that tab then move onto the next workbook, find the number of tabs (missing out the first 3 tabs of every workbook) and copy and paste the fixed range from each tab onto a new tab the summary workbook.
I will then have a summary workbook with a number of tabs, each one a different workbook with all the data from all the tabs in that workbook, I can then pull that information using formulas or some more VBA into a summary tab on the summary workbook, consolidating all of the information.
View 6 Replies
View Related
Oct 7, 2009
this may or may not be easy for some of you but its driving me nuts. Here's what I need to do.
1.Create a new workbook
2.Cycle through multiple workbooks and all worksheets within each workbook (all in the same folder). All workbooks will have the same structure.
3.Take the worksheet names and put them in rows
4.Take the data from the first column (column A) from the first worksheet of the first workbook and put them across the top (i.e. transpose the data). The first column will be the same in all workbooks so it doesn't matter where I get it from.
5.Copy the data from column E from each worksheet and paste them in rows in the new workbook (again transpose) corresponding to each worksheet.
6. Perform simple mathematical calculations at the end of each row.
I expect to have approximately 26 workbooks with a total of 7000 worksheets. In the target workbook (i.e. the new one), I expect there to be 7000 rows (corresponding to the 7000 worksheets) and about 260 columns.
The only reason I need to transpose stuff is because Excel 2007 doesn't have 7000 columns.
Here's an example of what it would look like.
Example worksheet (Input)-
Worksheet ABC
Col A Col E
1/1/2004 $25
1/8/2004 $30
1/15/2004 $15
Imagine another worksheet called LMN with the same ColA but different values in Col E.
Output workbook
ColA Columns B Column C Column D
Sheet 1/1/2004 1/8/ 2004 1/15/2004
ABC $25 $30 $15
LMN $xxx $yyy $zzz
View 7 Replies
View Related
Jun 4, 2014
In the attached spreadsheet, I have the original data display horizontally (sheet2). Col A is Patient #. The header in row 1 are the test codes. Each patient took only 1 test and have result reported either neg, pos, pending or not eval. How do I transpose the header and have the test results consolidated in 1 column accordingly as display in sheet 3.
View 4 Replies
View Related
Oct 17, 2008
I have a workbook that has multiple tabs and need help trying to figure out how to consolidate all the data. I find myself spending hours doing this manually each day.
Here is what I have:
Workbook has tabs labeled....Wk1_Mon, Wk1_Tues, Wk1_Wed, Wk1_Thurs, Wk1_Friday, Wk1_Summary......and repeats all the tabs through Wk5....then I have a Month_Summary tab.
I have 25 users with 25 seperate workbooks each with individual information on each workbook.
I am trying to get a sum of all the data on the Month_Summary tab for each month for each user and as well as a sum of the Month_Summary tab for all 25 users.
The end result I am looking for is to get a Yearly Sum of all the Month_Summary Tabs for all 25 users as well as individual yearly summaries for each users.
I have one main Folder which contains 25 folders (one for each user). Under each user folder there is a seperate Workbook for each month.
View 2 Replies
View Related
Mar 25, 2014
1. The idea is that the macro will start on sheet1, look at column "Jon", copy the values down to the last active cell as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B8 and B9. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
2. The macro will then go to sheet2, look at column "Jon", copy the values to the last active as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B18 and B19. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
3. The macro would then save the "Jon" tab as a separate pdf in a specified location.
4. The process would then repeat for "Mike" and "Paul". Each day the number of columns can fluctuate, so it may be 3 one day (Jon, Mike, Paul) and 8 the next. The number of rows also may vary from day to day, and column to column.
I have attached a workbook that contains the data for the scenario above as well as the output (Jon, Mike, Paul tabs). Ideally there will not be new tabs that remain for Jon, Mike, Paul, just a default tab (e.g. "output" tab) that would receive the transposed data from the columns for Jon, Mike, Paul. I put all 3 in the workbook so you could see how each of them would output.
View 4 Replies
View Related
Jun 30, 2014
I came across the following code, which does exactly what i want. It opens up all workbooks in a specified folder, and consolidates these into one. Each worksheet data is added below that of the previous workbook.
[URL]....
I have changed this to suit my workbook which has the header rows starting in row 6, but is now giving an error due ot the copy area not being the same size as the past area. It appears to be copying columns A:M and trying to paste this into the main workbook in columns A:N.
[Code] .....
View 9 Replies
View Related
Jan 25, 2009
On about 30 worksheets I have lists of names. These names are changed and added to now and then. I would like them to automatically populate a worksheet called "list". Preferably the names of every sheet with a blank cell at the bottom.
The whole work book is about 60 worksheets.
View 2 Replies
View Related
Aug 29, 2007
I have same kind of file stored in a folder every day. For example an expense statement that is being pulled every day has sheet tabs Mon, Tue, Wed, Thu, Fri. Every day it gets stored in a particular folder. I need to consolidate for say 4 weeks. All sheet tabs look alike. I need to consolidate all mondays, all tuesday sheets ... in a master work book.
View 9 Replies
View Related
Feb 9, 2014
I have attached an example of a Trail Balance workbook that shows monthly tabs and a summary tab. Column A&B is just a small sample of the Account #'s and Account description for each month. Obviously i can just Sum the different sheets on the summary page. However, the issue i have is that on the complete detailed sheet (over 200 rows/accounts down) that i have to complete columns A&B don't match up exactly and have added accounts in them or removed. So i need a way to consolidate all sheets in to a summary page making sure all accounts are listed on the summary page along with values summed into the various columns.
View 3 Replies
View Related
Feb 26, 2012
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
View 2 Replies
View Related
Jun 30, 2014
my macro comes up with an error on the Consolidate line that says 'Cannot add duplicate source reference'. I have a workbook with a variable number of tabs and I want to consolidate-sum (not copy and paste) the data from the various sheets onto a summary sheet within the same workbook. I'm at the point where it all works fine until it hits Consolidate.
Option Explicit
Sub ConsolidateExport()
'
' ConsolidateExport Macro
[Code]....
View 1 Replies
View Related
Jun 18, 2007
I joined a file so that you guys can understand what my problem is! In test4.xls there is 4 sheets. The one named "End" should appear in a new workbook. So i'm trying to make a macro that will transform sheet A,B and C into the sheet "End" in a new workbook! i know i have to transpose the data, but i didnt find how! The problem is that the macro have to work for each month of any years(2006,2007,etc)! Anyone know how to do it?
View 14 Replies
View Related
Jun 16, 2014
I have a long column of data in column D (D2 to D8761) and I would like to sequentially select 24 cells at a time (D2:D25, D26:D49, D50:D73...D8738:D8761) copy them and paste them to another sheet as separate rows.
For instance, the selected data from Sheet 1, D2:D25, would be pasted/transposed to Sheet 2 B9:Y9. I would like the macro to loop so it would then select D26:D49 and paste/transpose to B10:Y10 and so on until it finishes transposing the final data group of D8738:D8761.
I'll add the macro that I recorded using the brute force method so perhaps you can have a better understanding of what I am trying to accomplish.
Sub Macro5()
'' Macro5 Macro
Range("D2:D25").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
[code].....
View 3 Replies
View Related
Sep 30, 2006
I'am have one data file with so many sheets those need to edit and bring them to final summary sheet.
I dont know how to move between sheets and edit them automatically.
Editing will need to add the brand name next to the variance name and the sheet name in one new column. And bringing those rows with completed data only to All sheet from those sheets in workbook.
View 7 Replies
View Related
Jan 3, 2008
I have several worksheets in one workbook. To consolidate, I am copying the information from the worksheets into one summary sheet. The worksheet information is entered vertically:
Charges 2007 2006
Inpatient 30000 25000
Outpatient 32000 21000
I want the information in the columns to be transposed on the summary sheet, but still linked to the original worksheet:
2007 Inpatient Outpatient 2006 Inpatient Outpatient
30000 32000 25000 21000
Copy and paste special with transpose does not link the spreadsheet. I tried to drag across after filling one cell, but the fill function increases by column from left to right, not row number. (It increases row number, which is what I need, only dragging down.)
View 3 Replies
View Related
Feb 8, 2014
* I have a workbook, with different sheets (with sheet name as Home, RawData, Data1, Data2, Data3, ...), these sheets are not fixed
* FYI... sheet (home) has a VBA code which on running creates a new sheet and pull data from some other source (So the sheet number is not fixed in the workbook, but the newly added sheet is named in a specific format as Data1, Data2, Data3, ...)
* So we can say, that the sheet number and name are not fixed (It keeps on changing)
* All these sheets have data in the same format starting from range A2:J2, except sheet(home)
Two things i am trying to do:-
- Select sheets from the workbook (to be consolidated)
- Consolidate selected sheets in a new sheet
Here is the sample file Select sheet then consolidate.xlsm
View 6 Replies
View Related
Nov 17, 2008
Twelve months of Data.
Each month in Columns
like so:
The labels are on the left.
The data is in each column to the right.
From column 2 to the end of the sheet at far right.
Family Size
One: 1 3
Two: 2 1
Three: 1 2
Four: 1 1.....................
View 9 Replies
View Related
Feb 1, 2009
I have 2 sheets which validates data using vlookup.
Sheet1
Table 1ABResult1121232313
=VLOOKUP(A7,B:B,1,FALSE) is the formula in result1 column.
Sheet 2
Table 2DEResult21020102040203010#N/A
=VLOOKUP(F7,G:G,1,FALSE) is the formula in result2 column.
The basic idea of these vlookup is to check whether the data present in A or D column is present in B or E. If the data is there then it will be retrieved in "Result1" or "Result2" columns. If not then the result would return the value "#N/A".
What im trying to do is to copy the resultant data(from Result1, Result2) other than "#N/A" from individual sheets to a final consolidated sheet.
View 9 Replies
View Related
Aug 19, 2014
I have a workbook that has several sheets in it with various rows of data on each sheet. I would like to have a macro to loop through all the sheets in the workbook and copy and paste the rows into a new summary sheet. The rows to be copied should only have data in Column A, in other words if Column A of a row is blank I want it skipped. Also Row 1 of every sheet contains my headers, and I am only using columns A through M. It needs to be able to copy rows even when autofilter is in use.
View 13 Replies
View Related
Jul 12, 2014
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
View 4 Replies
View Related
Aug 26, 2009
I am using the following code to copy data from multiple tabs to a summary tab - it is only supposed to copy the data in the rows if the cells in Column A have data in them, its working, but for some reason it is copying the data in columns Y and Z for four extra rows even though there is no data in column A for those rows.
View 5 Replies
View Related
Oct 1, 2007
I have a worksheet with a list of project managers and a bounc of data on them.
How can i make a new file with sheets for every project manager in the list and copy the data for each one into his own sheet ?
Every project manager has many lines (there are about 200 managers and 30 000 lines and 30 columns )
I have managed to create a sheets for project managers from a list of unique entries but that's all.
View 9 Replies
View Related
May 6, 2014
I have a workbook that contains multiple sheets, currently five sheets of which only two of them are Info Datasheets.
I would like to create a VBA code that pulls data from multiple info datasheets and compiles that data into one Main Database sheet. However, after that initial collection, I need the code to maintain the rows data. Specifically, if any row's data changes in the info Datasheets I need that row on the main Database sheet to update.
The columns in the info datasheets range from A:AH, however I only need the columns A:E to be copied. And I would like the code to add column F in the main datasheet displaying the sheet name were the data was retrieved
Each Info sheet (Metals, Polymers) are setup identical; Rows 1:3 are the headers, and Column D and/or Column E will denote a change to the row.
The Main Database sheet (Table of Context) has 2 rows for the header. So, the data will start on row 3
F-S886 (Material Properties Database)_DRAFT_5.xls
View 7 Replies
View Related
Feb 5, 2008
trying to copy data from multiple sheets to one single sheet. I am pretty sure this is possible. The problem I am running into is that the number of sheets at any given time is dynamic. The numbering of the sheets is from 000 to 999 (they must be a three-digit code). The other issue I am having is I only want to copy the rows in each sheet that have an "x" in column "A". Also, the row in which the first "x" occurs can differ from sheet to sheet. I have attached a copy of the spreadsheet that indicates how the spreadsheet is layed out.
View 13 Replies
View Related
Nov 3, 2008
I have multiple .xls sheets in a folder. C:Documents and Settingsu369875DesktopProject stuffTestin Save_ASCompleted History. And need to copy the data in all of them and paste them into a new sheet (one main sheet) in this folder...........
View 2 Replies
View Related
Jan 23, 2009
I recieve a daily spreadsheet with ~25,000 rows of data and is 4 columns wide. Each day I need to break the data equally up into 19 or 20 different sheets within the same workbook. This is very time consuming as I need to copy the first ~1,000 rows and paste it into the first sheet, then I need to take the next ~1,000 rows and paste it into the second sheet, and so on.
I have attached a small example with desired output. In my example, the raw data file contains 19 records. For these 19 records, I need to distribute the records evenly between 5 individual sheets. In this case, each sheet will contain 4 records or less.
View 5 Replies
View Related
Sep 26, 2013
I have a workbook with 10 sheets.
Each sheets has data starting row 14 and column B with row 13 being column Headers.
I want to copy data from each sheet, having column headers suppose A, F & G in to a single sheet. The position of column headers I want to copy are different in each sheets
Means macro will go to sheet1 first, he will copy data from column headers A, F & G and paste in new sheet suppose main.
then macro will go to sheet2, he will copy all the data from column headerA, F & G and paste it in main sheet after the last used row and so on.
View 8 Replies
View Related
Jun 30, 2006
I am trying to create a macro that will copy data from one sheet to four other sheets depending on some requirments in the data. I have a main worksheet that will store the data, the name on this sheet will change daily, so you cannot use a "named" sheet in your macro. The other three names of the sheets are "Esky", "Indy", "Gfld", and "Other". When copying the data, I need to copy the entire row of data to the required sheet. The data comes in such as:
MZ03948 134 126.23
PZ34533 128 12.28
IM04985 630 198.50
Z109384 40 35.65
2130494 76 12.28
2236326 12 45.63
5123132 12 12.23
Based on the FIRST TWO characters in the first column, I need to copy the data to the specific sheet. I would assume a simple LEFT() function would work for this. Here are the requirments for each sheet:
Esky - "MZ" or "51"
Indy - "IZ" or "Z" or "21"
Gfld - "PZ" or "22"
Other - Anything else
View 9 Replies
View Related
