Free Microsoft Excel 2013 Quick Reference

Help on copying data from multiple sheets into one single worksheet

Hello everyone!!

I'm trying to copy data from 5 worksheet in the same excel workbook into one new sheet that I create in the same workbook. How can I do this??

What I have done so far is: When I select all the 5 sheets and hit copy then paste into the new worksheet it tells me that the copy and paste area needs to be the same size and shape.

Does anyone have any idea and could help me out???


Post your answer or comment

comments powered by Disqus
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,

Plz help me out.....

I have to copy huge data from multiple sheets into a single sheet.

How can I do that......

because sheets are so many and it takes too much of time...

Plz help me out....

Regards
Neeraj

I need help 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. Any help is appreciated.

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

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

Hi,
I want to copy data from multiple sheet and paste in a summary sheet. Every days the number of increases, from where i need to copy the data. Can we write a macro such that it automatically copy the data from all the sheet for the specified field and past in summary sheet.

In my workbook there are suppose 20 sheet and i want to copy the data only from 15 sheet. I have created two sheet blank to make boundary to identify the sheets need to be copied. Header is A2 to A15 and data is in B2 to B15 of each sheet. In the final sheet where i want to copy the data i have already added header as A2 to A15, now i want to just copy the data from cell B2 to B15 of all the sheet.
Please help me on this.

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.

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

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 !

Can anyone help to tell me which function to copy data from many sheets into one sheet on the same file. Eg January (Student Name, Course....) February...........to sheet 2011

Thanks

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!

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 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.

I am copying data from several sheets to one main sheet, basically a summary sheet. In the summary sheet I have my first column set up to reference the data in the first sheet. I would like to copy the formulas in this column to the corresponding columns and only change the sheet referenced and not the cell or the data since the sheets that the data is collected from are identical. I am fairly new to Excel and have only learned from hands on or from other users knowledge. Thanks!

Good day,

First, I'd just like to comment that this forum has really been helpful to me in providing assistance. The information provided has really educated me in excel as well as VBA Macros (though I still have a lot of things to learn).

I've been trying to search the forum about this; however, I have been unsuccessful. I know this has been asked before and I saw a couple of posts related to it; however, it wasn't exactly the format I wanted it to be, I hope you can help me out.

I'd like to clarify that I do not have any VBA Knowledge. I've survived on using the codes I've seen here and apply it to the data that I have.

My goal is to copy data from multiple sheets with the same data structure into a single or master sheet. This is the code that I have to do so:


	VB:
	
 Summarize() 
    Dim ws As Worksheet 
    Dim lastRng As Range 
    Application.ScreenUpdating = False 'speed up code
     
    ThisWorkbook.Sheets("Summary").Rows("2:65536").ClearContents 'clear
     
    For Each ws In ThisWorkbook.Worksheets 
        Set lastRng = ThisWorkbook.Sheets("Summary").Range("A65536").End(xlUp).Offset(1, 0) 
         
        Select Case ws.Name 
        Case "Summary" 'exlude
             'do nothing
        Case Else 
            ws.Activate 
             
             'copy data from individual sheets
            Range("A2", Range("D65536").End(xlUp)).Copy lastRng.Offset(0, 1) 
             
             'add sheet name before data
            For i = 0 To Range("A2", Range("A65536").End(xlUp)).Count - 1 
                lastRng.Offset(i, 0) = ws.Name 
            Next 
             
        End Select 
    Next 
    Application.CutCopyMode = False 'clear clipboard
    Application.ScreenUpdating = True 
    Sheets("Summary").Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
At the same time, I'd like to put a timestamp on the mastersheet to track when the said change were made by using this code on my mastersheet.


	VB:
	
 Range) 
     
    If Target.Cells.Count > 1 Then Exit Sub 
     
    If Not Intersect(Target, Range("A2:A100")) Is Nothing Then 
         
        With Target(1, 6) 
             
            .Value = Now 
             
            .EntireColumn.AutoFit 
             
        End With 
         
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Everything seems to work fine with a single exception. Every time I update the mastersheet with the consolidation code, it refreshes the whole data including the date when earlier data was encoded. Would it be possible to append the data in such a way that the mastersheet only adds the latest cells from the other sheets. I'm using Excel 2010. I included my sample file for your reference.

Please let me know your thoughts on this if its possible. Any help is very much appreciated.

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,

I am developing some reports in excel and was just wondering if anyone can help me ease out the very time consuming activity I am doing.

I have multiple spreadsheets which has simillar data in them filled in by multiple teams. The file names for these are also simillar with only the team name changing (e.g. PSI_2009_Team 1.xls). I need to copy data from each of these sheets automatically to a consolidated master spreadsheet. Then I can play with the date to do my reporting.

Attached are sample sheets to work on a solution.

I need data from <PSI_2009_Team1.xls>, <Team2.xls> and <Team3.xls> to be copied to <Report.xls> one below the other.

Also, I need to be able to run the macro in the consolidated report sheet whenever a new PSI_2009 file is generated to add that data into my consolidated sheet.

Please help me in copying data from 30 sheets in the same workbook in the same A13:L55 range to a single worksheet one below the other so that I have the data consolidated into one sheet.

I would like to add that the data in all the sheets is in the same format and in the same range.

Thank's in advance.

hi everyone, i need some help with a macro, not sure where to start:

I am trying to write a macro which will pull data from different sheets
in an excel file, if i chose that sheet to be selected (via a 1 or 0)
and copy that data and paste the data into a new sheet.

all data is in columns A & B of each sheet and runs to row 100.
Basically, if the sheet is selected with a 1, then i'd want the macro
to go to that sheet, copy the range: A1:B100, and then paste is to a
new sheet. I would want the results (of only the selected sheets) to
end up in columns A & B of the summary sheet...basically creating one
long set of data of only the sheets I selected via a 1 or 0 on the
summary sheet...

does that make sense?

hi everyone, i need some help with a macro, not sure where to start:

I am trying to write a macro which will pull data from different sheets
in an excel file, if i chose that sheet to be selected (via a 1 or 0)
and copy that data and paste the data into a new sheet.

all data is in columns A & B of each sheet and runs to row 100.
Basically, if the sheet is selected with a 1, then i'd want the macro
to go to that sheet, copy the range: A1:B100, and then paste is to a
new sheet. I would want the results (of only the selected sheets) to
end up in columns A & B of the summary sheet...basically creating one
long set of data of only the sheets I selected via a 1 or 0 on the
summary sheet...

does that make sense?

Hi Guys,
You people are really genius. I am watching your posts and concepts. I hope you can kindly help me out in this matter. I have multiple sheets ( named A to J) in a work book. Each sheet has many data’s but there are only two values in the rows of column P that is I & II. (I and II determines two classes of items)
The top three rows are used as headings in each sheet.
I want a macro (code) which can copy the full rows from each sheet containing the value I in column P into a single sheet one after the other. So that I can get a full sheet which shows the rows containing I of each sheet in column P. Specifically all the class I items are needed to be copied into a single sheet at the end in the same workbook.
I am bit novice in this aspect so pls help accordingly.

Thanks in advance
PB

In SEAsia.xls file contain 10 Sheets of data, each Sheet means data for a date.

My question is how can i copy data from Sheet 1 - Sheet10, Column U (SEAsia.xls) into All Data.xls from C3:O12 by using fast formula?

Appreciate for your helping.

SEAsia.xls
All Data.xls

Hi, first time poster,

i have a file which contains a number of sheets(+-1.000), each with 34 lines of data. I would like to copy paste this data into a 'master sheet', creating 34.000 lines of data.

I'm encountering 2 problems when trying to work with record macro.

1. Upon selecting a sheet, the name of the sheet is recorded and the macro doesn't know he needs to select sheet n+1 for the next cycle in the loop
2. Upon pasting the data from a sheet into the master sheet, the cell range is recorded when using the record macro. The first time I select cell A1 and paste, however the second time A35 should be selected and pasted, then A69 etc.

Any help on the matter would be greatly appreciated. Thank you for your time.

Regards,

Simon De Meester

Hello all. I am working on a project where I will need to pull data from multiple sheets and place it on a common sheet for reporting purposes.

(I am attaching an example)

I have an input box that asks for a date.
Based on what date is entered, the following data should be copied from the account worksheets and placed on the "Recon" worksheet: the account number ("C2") and the balance (Column D).

For instance, if the user entered 09/12/2007, the account number and the balance in the D Column for the last entry for that date would be copied to the "Recon" worksheet.

I have prepared some code that will prefill the account number and then display the input box which follows below:


	VB:
	
 Recon() 
    Dim recondate As String 
    Dim anyWS As Worksheet 
    Const dSheet = "Recon" 
     
     
    copyToRow = 5 'initialize
     
    For Each anyWS In Worksheets 
        Select Case anyWS.Name 
        Case "Main", "Recon" 
        Case Else 
            Application.ScreenUpdating = False 
             
            Application.DisplayAlerts = False 
             
             'For Each Worksheet In anyWS
            Set rngToCopy = anyWS.Range("C2") 
            rngToCopy.Copy 
            Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats 
             'Worksheets(dSheet).Range("A" & copyToRow).PasteSpecial Paste:=xl.PasteFormats
            copyToRow = copyToRow + rngToCopy.Rows.Count 
             'Case Else
             'do nothing
        End Select 
    Next 
    Application.CutCopyMode = False 
    Worksheets(dSheet).Activate 
     
    recondate = Application.InputBox("Enter Date to Reconcile." & vbNewLine _ 
    & vbNewLine _ 
    & "Format should be MM/DD/YYYY", "Reconciliation Date", vbYesCancel) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My question is: How do I search through all the worksheets using the date returned from the input box, locate the balance (in Column D) in the row corresponding to the last entry of the date (since there may be multiple entries for that date per the attached example) and copy that to the "Recon" worksheet?

Thank you in advance for any advice/assistance...and I apologize if I have not explained this properly!


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