Free Microsoft Excel 2013 Quick Reference

Query data from multiple sources into one pivot table

Hello,

I just figured out how to write simple SQL statements that bring data from 3 excel workbooks into 1 workbook. I did it by using three seperate pivot tables and selecting the External Data Source option. Is there any way to write an SQL statement that brings all that data into just one pivot table? My hope is that doing the same operation in one SQL statement will be faster for the end user than going through essentially the same process three times.

many thanks for your help!
mb


Post your answer or comment

comments powered by Disqus
I have an actual sales data source, and budget sales data source, i need to
compare actual vs budget years, is there a way to merge two data source into
one pivot table?

I am having trouble pulling data from multiple spreadsheets into one master spreadsheet. Someone tried to help me and provided me with this code:

Sub DataCompiler()
Dim z  As Long, e As Long, h As Long
Dim f As String, g As String
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""compile"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir("C:UsersTomyDesktopardaman*.xls")
    Do While Len(f) > 0
    If InStr(f, "Test") > 0 Then
    ActiveCell.Formula = f
    End If
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 2 To z
        If Cells(e, 1) <> ActiveWorkbook.Name Then
            For h = 1 To 2
            g = Choose(h, "B13", "B15")
                    Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) &
"]LogResults'!" & g
                    Cells(e, h + 1) = Cells(1, 3)
            Next h
        End If
    Next e
MsgBox "collating is complete."
End Sub
I have tried it and it is not working. The debugger says there is a problem with this line:

Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]LogResults'!" & g

What I am trying to do is pull the values from cells B13 and B15 from the worksheet named (LogResults) from multiple files that include "test" in the file name and list them in the master spreadsheet. Can anyone help? Thanks in advance.

Hi,

I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.

Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.

From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:

Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.

Here is my current code:

Private Sub GetData() 
    Dim LookupDate As Date 
     
    LookupDate = Range("C6").Value 
    GetWorkStreamData ThisWorkbook, LookupDate, "Vauxhall" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Ford" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Fiat" 
    GetWorkStreamData ThisWorkbook, LookupDate, "VW" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Honda" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Toyota" 
     
End Sub 
 
Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String) 
     
    Const ROOT_FOLDER As String = http://sharepoint.net/meetings/reports/ 
    Dim LastRow As Long 
    Dim NextRow As Long 
     
    With wb.Sheets("WorkstreamReport") 
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 
    End With 
     
    Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator &
"Report to PMT from " & WorkStream & ".xls" 
     
    With ActiveSheet 
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
    End With 
     
    Rows(2).Resize(LastRow - 1).Copy wb.Sheets("WorkstreamReport").Cells(NextRow, "A") 
    ActiveWorkbook.Close savechanges:=False 
     
End Sub
Currently, this is finding the appropriate workbook to copy from, but doesnt copy anything into the summary report. Therefore, I suspect theres a big problem with the cells it is looking at and then the location of the cell it is looking to place the copied data???

Any ideas would be much appreciated.

Cheers,
Alex

Needin help wi pulling data from multiple workbooks into one. What needs to happen is that it copys certain columns from multiple workbooks and then pastes the columns into one master workbook, anyone tell me how to do this, had a wee look at the other threads on this but they dont seem to work.

Hi~

I'm trying to set up a grid template that will take a few columns of data
from each of several pages within a workbook and automatically populate it
into a master list on another worksheet. I know how to do this for copying
from one worksheet to another, but am unsure of how to copy from multiple
sheets into one without the data pasting over the previous page's list.

I'm not sure how best to describe this, so let me know if there are questions.

Thanks!!
KW

Using =A7&","&B7 to add data from two columns into one, separated by a comma.

What about multiple columns, still separated by a comma? Please See Example in attachment

Thank you in advance! Mike

Excel 2007

Hey all,

I'm attempting to consolidate a ton of data from multiple workbooks into one spreadsheet. Unfortunately it's been a while since I've done any programming and it was never with VBA... I think I can struggle through data selection/copy process but I'm not sure how to access multiple workbooks.

The data I want to copy from the workbooks is always in the same cells, which helps. If anyone could give me some quick pointers on how to start this macro up and access other workbooks I'd really appreciate it.

Secondary problem: One of the sections of data I'm copying is a column of data points which would correspond with the y-axis of a graph. The x-axis values are a couple columns over, but I'd like to only copy the y- data to save space in the other workbook. The problem is that the range of the x- data will occasionally extend to larger values without changing the rest of the values. I would need the macro to "see" which x- value the y- data begins at and place it accordingly. That was extremely hard to understand so I'll try illustrating:

Data Source 1:
x y 10 1 9 3 8 2 7 3 6 4
Data Source 2:

x y 9 2 8 1 7 3 6 4 5 5
What I'd Like To Construct:

x y1 y2 10 1 9 3 2 8 2 1 7 3 3 6 4 4 5 5
Hopefully that makes it a little more obvious. I'm assuming I'll just have to have the macro look to see what value the data begins collection at and then have it match up to the proper row in the destination table.

I'll be scanning some intro VBA material I saw posted in the stickied topic as well, hopefully that will help. Thanks for any help on this!

I would appreciate it if someone could point me in the right direction,

I have a workbook that contains a number of sheets (up to 10) and each of these sheets is an expense sheet for one employee.

I need to collate all of the Data from each employee into one weekly sheet.

We have some regular suppliers, but we also have suppliers that are one-offs, that may or may not be used by seperate employees in a week.

What I need to do is collate each of the expenses into the weekly sheet, with each supplier on a line of their own, and the relevant daily total for all employees for that supplier.

I have attached an example because I,m not sure that was very clear!

Thank you.

Hi,

I'm trying to write a macro. Currently it asks the user how many sheets the
user has e.g. 5 and opens the 5 sheets.
What i need to be able to do is copy the data from these sheets into one
sheet then output it into another workbook.
So if the first sheet has 6 rows, then those rows will start at (A1)
if the next one has 2 rows it should start at the 7th row

Hello,

I have been struggling with this Macro for a while and I have tried in every way that's suggested on the web. This Macro works fine to copy data from multiple sheets into one master sheet, but it is also copying the formula. How can I change this Macro so that it is copying and pasting VALUE only?

Sub Combine()
Dim J As Integer

On Error Resume Next

' work through sheets
For J = 4 To Sheets.Count ' from sheet 3 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A10").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets("Combined").Range("A65536").End(xlUp)(2)

Next
End Sub
Please help. I need to resolve this quickly.

Thanks,

Yang

Hi there,
My problem is to that I'm trying to gather data from multiple workbooks (I have 600) and get it into one master worksheet.
All the file names are different but are in one folder. The row of data I want to copy from each workbook is located in the same area on a similar named sheet (i.e Sheet3), and I was hoping if there was a macro that I could run to copy the row from each workbook and paste it into the master workbook in the next blank row.

Hopefully someone can help me with this!

Hi there,

I hope you can help me. I am using excel 2007,and I have multiple worksheets with information on different countries. One sheet has a country profile, with info such as GDP etc. Then the other sheets have sales data, from different sources.

What I need to do is be able to have a sheet where I can type or use a list to select a country, say Italy, and it will display the information from each sheet onto the the summary sheet.

Now the country profile bit won't change but the other data will. So I could call up a country and the general information is displayed, and as you scroll down the data from each sheet is displayed, but only of that country. I am currently having to do this manually, and need to be able to do it automatically.

The data needs to have the column labels displayed as well, as each sheet has projections for a different year.

All I need is the info that is there to be displayed all together but only of that country. I have tried the consolidate tool but it doesn't seem to do what it is I need. A clever filter is what I need, to fetch the data and list it in its seperate tables.

I hope you can help.

Thanks.

Rick

Hello,

I'm trying to merge several sheets (not all) from multiple workbooks into one sheet in a single workbook.

The data I need to copy is placed in sheets Client 1, Client 2, Client 3 in the example file I've attached; I also need to copy cell C3 from summary sheet and paste it at the 1st right sided cell available, along the same range (it will allow me to group the clients from each workbook) .

The example has 3 client sheets, but our workbook has up to 16. The Client sheets always go from sheet 3 to 18, but some users delete the client sheets that have no data, so the amount of sheets to be copied may vary.

Is it possible to skip sheets, using some kind os argument like "if A1=0, go to next sheet"?

Thanks in advance,
Alexandra

I would like to merge data from 30 tabs in one spreadsheet. They all have the same heading but different number of rows. How can I do it in an effective ways. I got bore just copy and paste data from all the tab into one.

Thanks

Regards
Steve

Hi Friends,

I am trying to collect the data from multiple sheets (which are containing in files saved in a perticular folder) to one sheet....

I had tried myself but there are some extra codes requires. Its reunning properly on first file after completing first files all sheets its giving error...

sending the codes which I have tried...

waiting for the reply on highest priority...

Dim Worksheet As Variant
Dim m As Integer

Application.FileDialog(msoFileDialogFolderPicker).Show
excelfile = Dir(Path & "*.xls")
Do While excelfile ""

Workbooks.Open Filename:=Path & excelfile

excelfile = Dir
Sheets(1).Select
Range("H5").Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("Previous Closing").Select
m = Range("BX1").Value
Range("A" & m).Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWindow.ActivateNext
Range("G25:G97").Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveCell.Next.Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
Range("BX1").Select
ActiveCell.FormulaR1C1 = m + 1
Range("A1").Select
ActiveWindow.ActivateNext
ActiveWindow.ActivateNext

Do While Worksheet ""

Worksheet = Dir
ActiveWindow.ActivateNext
ActiveSheet.Next.Activate
Range("H5").Select
Selection.Copy
ActiveWindow.ActivateNext
m = Range("BX1").Value
Range("A" & m).Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveWindow.ActivateNext
Range("G25:G97").Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveCell.Next.Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
Range("BX1").Select
ActiveCell.FormulaR1C1 = m + 1
Range("A1").Select

Loop

ActiveWorkbook.Close False

Loop

End Sub


Hi all,

I know there are quite a few threads out there on importing data from multiple workbooks into one master file. But somehow my search is not giving me what I am looking for. If I had missed it, please let me know!

Anyway, I basically just want to copy and paste a certain range from different workbooks into the relevant sheets in a master file. The code that I have now for copying and pasting just from one workbook into one specific sheet in the masterfile. The code below is copying and pasting from sheet 'report' in workbook 'Source 1.xls' to sheet 'Source 1' in workbook 'master file'.


	VB:
	
 copysource1() 
     '
    Application.ScreenUpdating = False 
    Windows("source1.xls").Activate 
    Sheets("report").Select 
    Range("B6:U12").copy 
    Windows("master file.xls").Activate 
    Sheets("source1").Select 
    Range("B6").Select 
    Selection.PasteSpecial Paste:=xlValues 
     
    Windows("Source 1.xls").Activate 
    Range("B19:U25").copy 
    Windows("master file.xls").Activate 
    Range("B19").Select 
    Selection.PasteSpecial Paste:=xlValues 
     
    Windows("source 1.xls").Activate 
    Range("D1:E1").copy 
    Windows("master file.xls").Activate 
    Range("d1").Select 
    Selection.PasteSpecial Paste:=xlValues 
    Range("A1").Select 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I got this from recording the macro. I have 6 more workbooks to be copied and pasted this way, so basically I have 7 different macros to do the same thing with changes in filenames of the source file and sheet names of the destination file. The range of cells to be copied and pasted remain the same.

I know there must be a better way of doing all these without having to repeat the macros 7 times.. I just can't figure it out with my very limited knowledge.. Can someone show me the way?

Thank you so much!!

I am attempting to combine data from several worksheets into one sheet for
analysis. Other than copying and pasting each sheet, is there another way to
do this? Once combined I will be doing a pivot table to capture the
information I need.

Thanks,

English.xls

French.xls

Master Sheet.xls

Spanish.xlsHi,

I have information spread in multiple sheets. The information is the same except for 2 columns - One additional Name and the date of closure. I am attaching the excel sheets here. I have knowledge object names in english, spanish and french.

I want to transfer this information from multiple sheets into a single sheet which i am calling the master sheet. In the master sheet I want the information to automatically reach the moment someone types in the sheet of anyone of the language. For example, the moment i type bubba gump in english and put in the closure date it should appear in the master sheet and in the column in english there should be a tick mark or colour of some kind that says the work is done. Now if someone finishes the same in spanish it should appear in spanish column with the tick mark or colour of a different. The names of the knowledge objects should not be repeated. Or maybe the master sheet can just pick up the end date and display it there.

Please look at the sheets to have a clear picture of what I am trying here.

Your help will be greatly appreciated.

Regards

Mayank

Hello,

I'm trying to merge several sheets (not all) from multiple workbooks into one sheet in a single workbook.

The data I need to copy is placed in sheets Client 1, Client 2, Client 3 in the example file I've attached; I also need to copy cell C3 from summary sheet and paste it at the 1st right sided cell available, along the same range (it will allow me to group the clients from each workbook) .

The example has 3 client sheets, but our workbook has up to 16. The Client sheets always go from sheet 3 to 18, but some users delete the client sheets that have no data, so the amount of sheets to be copied may vary.

Is it possible to skip sheets, using some kind os argument like "is A1=0, go to next sheet"?

Thanks in advance,
Alexandra

Hi

I'm hoping someone could help me out I've got a spreadsheet with multiple tabs containing different amounts of data but all in the same format (a list of tasks in columnA, Names running along the top of the page and their skill level next to each task name running vertically)

I want to pull this information into one sheet so I have a list of all tasks vertically and all names horizontally. The twist is I want the colour of each of the cells to pull through i.e. skill level 1 = red, 2= green, etc. In addition, I want to be able to add data straight into my results table e.g. if a user decided they wanted to add a skill level in for a task that wasnt on their original task list they could.

I can pull the data from multiple tabs using a pivot table but this doesnt pull the colour or enable me to edit it.

I know my question is as clear as mud but hopefully someone will have a vague idea of what I'm on about and help me

Thanks in advance

Lucy

p.s. I have some programming experience with PHP, JS, etc but none with VB

Hi there,
First of all I want to thank you for that wonderfull site here.
it makes me happy that there is always someone who can help you in trouble.
I am new for the VBA macros but i like Excel and i am suprised from its power

Here is my problem:
Every month I have *.xls files containing one sheet with different data.
I tried to make a macro whose job was to copy all data from different files into one sheet but i failed .
Please tell me is there some kind of macros which will copy all data sheet from all files into one sheet and to detect every next empty row to paste the information there ?
Please help me !!
if you want more information my e-mail is
t.totev@abv.bg

In the attached file, I created an example of a list I’m trying to break down and combine data from multiple cells into one cell. As it shows on sheet1, level 1 has two people who occupy it, Paul and Peter. I am trying to get the names Paul and Peter to be combined in one cell next to the #1 as shown on sheet2. I want it to put the correct amount of people next to their corresponding number as shown on sheet2. Is there anything that I can use that may make my life a little simpler? This will be done on a weekly basis, and incorporates a couple hundred items. Any help is appreciated.

Thanks.

Hi Guys, i hope you can help me.

Im trying to retrieve data from multiple worksheets, into one main spreadsheet.

So say i have a folder "c:tempexcel" and in this folder i have 50 workbooks.

I want to run a piece of code so that when excel will root through all 50 workbooks, take out the relivant criteria, and place into a new worksheet.

How do I copy the data from several spreadsheets onto one sheet without copying and pasting from each one seperately. I would like the data from the first sheet to be first, the second sheet directly underneath, etc., through the end of the sheets.


No luck finding an answer? You could always try Google.