Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Macro To Copy Data From Multiple Worksheets Into 1 Worksheet

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a spreadsheet that has hundreds of different worksheets. Within each worksheet there is a multiple row of data from cell A1 to K2.

I want to copy this rows from each worksheet into a single worksheet.

Any help on how I can build a macro to loop through each worksheet and do this would be greatly appreciated.

Thanks for your help.

View Answers     

Similar Excel Tutorials

Guide to Combine and Consolidate Data in Excel
Guide to combining and consolidating data in Excel. This includes consolidating data from multiple cells, multiple ...
Import a Worksheet from One Workbook to Another in Excel
In Excel, you can quickly copy an entire worksheet from one workbook to another workbook.  This allows you to impor ...
Get the Name of a Worksheet in Macros VBA in Excel
How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel.  This means that y ...
Select Data from Separate Worksheets with Macros VBA in Excel
Select data from other worksheets with Macros and VBA without navigating to those worksheets or activating them. T ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...

Helpful Excel Macros

Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

Similar Topics

I'm not sure if there is a thread relating to this but the ones I have come across did not help, but I'm sure that's to do with my lack of knowledge on VBA!!
I want help on creating a macro that will copy one cell value from a set of worksheets to a summary worksheet. I'm using MS Excel 2003.
The cell value in each worksheet = O8
The worksheets range from '200501' to '201009' or indexed from 25 to 93
The summary worksheet is called 'RAW DATA'
I need the data to paste into cell C3 downwards! and also need it to recognize if the cell above is empty.
Alternative: If there us a formula that can do the above and I can use the autofill function then please let me know of one.

I have two worksheets in one workbook... The worksheet 'data' contains some data that I need to copy and paste into certain cells in another worksheet called 'form'. for each row of data I need a new worksheet created from the 'form' worksheet.

Basically for each row of data in my 'data' worksheet (321 rows) I need a new worksheet created from my template worksheet 'form'. the data that I need copied from the 'data' worksheet is in cols B-G, and I need this data pasted into the 'Form' worksheet into cells B4 - G4.
The names of the new worksheets created doesnt matter, i suppose they just have to be unique.

Also I would like to fill in the date field which is located in column J in my 'data' worksheet and have this pasted into H8 in my 'form' worksheets.

Also, the rows of data will change daily, it will not always be 321 rows.

I'm fairly new to VB... So any help would be GREATLY appreciated!

btw, I am using office 2k3 on Windows 7.

I tried using this code to create the new worksheets, it worked but, it only creates 254 new worksheets then errors out. Option Explicit

Sub Copy_Sheets()

Dim i As Integer
Dim wks As Worksheet

Set wks = Sheets("data")

For i = 1 to 360
Sheets("form").Copy After:=Sheets(2)
ActiveSheet.Name = wks.Cells(i, 2)
ActiveSheet.Cells(1, 2)=wks.Cells(i, 2)

End Sub Thanks!!


Hi guys, am currently having a look around for a solution to my problem, but though i'd ask here first in case someone has already come across this before.

I have multiple workbooks, all in the same format (i.e. worksheet names and data layouts), saved in a single folder. I want to copy data from multiple worksheets in each workbook into a master workbook.

I want to write a macro that will go into the first workbook, go to a worksheet, copy a range of data, go back to the master workbook, paste the data. (This will repeat for a number of worksheets)

Then it will go to the next workbook in the folder, and do the same.

Essentially my work structure is as follows:

1. Open master workbook manually
2. Select folder containing source workbooks (via dialogue box)
3. Run macro
i) workbook1.activate
ii) select first worksheet, copy range of data
iii) masterworkbook.activate
iv) select first worksheet, paste range of data
v) repeat steps i) to iv) for 5 worksheets total
vi) repeat steps i) to v) for n workbooks total

I can write the code that will copy and paste data from one workbook to another.

However, can anyone help with:
1. Cycling through the multiple workbooks - would I need to specifiy workbook names? (this is fine to do as they won't change, but wondering if there is a quicker option)
2. Keeping each active workbook (apart from the master) hidden while the macro is running

Any help much appreciated!


Can anybody teach me how to copy data from multiple worksheets, to a new worksheet? I need the same cell from each worksheet. For example, i have 300 worksheets, i need to copy each data value in each b4 cell from each sheet onto the 301st worksheet, and have each data value one after the other in the format of a column.

is this possible??


I am sure this is simple, but I don't know how to do it.
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code. Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?

Any help on this would be greatly appreciated

Hello, sorry if this question has been asked before.

I've got a project where there are a LOT of worksheets. Each worksheet has the same basic layout. On the first worksheet I created a graph, which uses data from columns A and B.

I want to create an identical graph on every worksheet, using data still from columns A and B on the relevant worksheet. Obviously I could do this by copying and pasting the original graph onto every worksheet, then formatting every graph to update the source data worksheet reference. This will take ages though, as there are so many worksheets.

Is there another way of doing this so that the worksheet reference automatically updates as I copy and paste the graph?

Many thanks,

Is it possible to import a single text file into Excel, splitting the incoming data across multiple worksheets rather than a single worksheet? Each each row on the text file would be evaluated by the value in one of it's "columns" and written to the appropriate worksheet. The file is "!" delimited and has 11 columns for each row.

Currently, I import the file into one worksheet and cut/paste the rows manually into new worksheets/tabs. The files are very large, sometimes exceeding the 65,536 row limit, which I could avoid if the data was split out coming in.

Any help anyone could provide would be WONDERFUL. Thanks!


I'm running Excel 2008 (Mac, v.12.2.4), and have a question about saving multiple PDFs.

I have a workbook that consists of around 40 worksheets. I would like to print each of these worksheets as its own individual PDF (ie. 40 PDF in total). In addition, I would love if I could name each PDF according to the worksheet name.

Is any of this possible? There's tons of info on how to save multiple worksheets to a single PDF, but nothing for what I need to do. I've done it before, and I thought it was simple, but I can't seem to figure it out now!

Any help would be greatly appreciated!


I have zero macro knowledge and went through many similar topics in this forum, but I still can't figure out. I really need your help to create a macro for my case.

I constantly need to copy and paste one cell from multiple worksheets to a summary sheet. I created a sheet upfront called "Summary". My wants a

1) Copy the worksheet name to the summary sheet;
2) Copy the value (not the formula) from B34 or any cell from all worksheets in the workbook next to the worksheet names just created from the above Step 1. (all worksheets are set in the same format)
3) Hopefully this macro allows me to handle different number of worksheets, i.e. some file contains 30 worksheets and some 50 or 100, etc.

Ideally, the summary sheet needs to look like (for example):

Client 1 100
Client 2 200
Client 3 500

Client 35 20

Thank you very much.


I am new to macros and I am having problems with creating a macro that will copy an entire row from the original worksheet if column B is an LIR to a worksheet labelled LIR and to a worksheet labelled KLE if the row and column B in the original worksheet is KLE. I would like the macro to copy the entire row to the destination worksheet either worksheets LIR and KLE to start at the beginning of the worksheet at row 2 so that when the macro is ran it will updated the worksheets. Currently, when the macro is ran, it keeps adding the rows to the exisiting rows with data and keeps adding to it every time the macro is ran. I am including the the macro below.

Public Sub CopyRows()
LColumn = 1
' Find the last row of data
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column B
ThisValue = Cells(x, 2).Value
If ThisValue = "LIR" Then
Cells(x, 1).Resize(1, 33).Copy
'Copy to LIR Data1 worksheet
Sheets("LIR").Cells(2, LColumn).PasteSpecial
NextRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(NextRow, 1).Select
ElseIf ThisValue = "KLE" Then
Cells(x, 1).Resize(1, 33).Copy
'Copy to KLE Data1 worksheet

Sheets("KLE").Cells(2, LColumn).PasteSpecial
NextRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(NextRow, 1).Select
End If
Next x

End Sub

My co-worker have multiple Excel files (1 worksheet per file); all worksheets have the same column headers and structure; she needs to combine all these worksheets from the multiple files into one worksheet with all the data stacked one below the other.

Please help if you know of an easy way to do this.

If would be great if you could provide a macro code and instruction on how to use the macro.

I have a master data worksheet with employee names, employee #...

A3 - 1, A4 - last name, A5 - last name, A6 - employee #....
B4 - 2, B4 - last name....

I then created a master template worksheet and then copied it 50 times (one/employee) and ran a macro to name them 1,2,3....50. SO far so good - now I have a template for each employee but I need to fill in their data from the master worksheet.

What I want to do is automatically populate the worksheets with the data from the master worksheet that contains the first name, last name, employee that I don't have to manually type (copy/paste) the data in again since its already in the master worksheet.

I was trying to find a way to automatically populate the first name, last name (& any other data I needed) to all 50 worksheets automatically.

The information I've searched for hasn't worked out for me yet?


Dear All,

I have like 100's of worsheet with the data. But I want to compile all the data into one worksheet. All the worksheets are almost similar. I just want to compile the particular column data of all the worksheets.

SUppose I have a worksheet name 10.15 and I have the data on E,F, and G column and I have other worksheet 10.20 and have the same type of data on the same columns but the row number changes a bit. NOw, I want to continuously arrange the data from these worksheets in one worksheet. Suppose the worksheet 10.15 has 15 rows and the worksheet 10.20 has 20 rows, I want a new spreadsheet with 25 rows which contains the data of both the worksheets.

Hope you good people know my problem.

Please help soon. Thanks.

Best Regards,

Good Morning All,

I am working with 2 worksheets. Worksheet 1 is named 08 billings and worksheet 2 is named 08 $ X Job. Worksheet 2 needs to reference various cell values from worksheet 1. I would prefer to use a formula as opposed to paste special, select values, and check the transpose box. Here is my scenario:

Worksheet 1 has data in row 55 starting with column C and ending with column AZ. I am trying to move the data in row 55 from worksheet 1 into a column A of worksheet 2. I have several instances where I will use a different row but remain selecting columns C - AZ in worksheet 1. The data is appearing horizontal in worksheet 1, and my goal is to get it to appear vertical in worksheet 2. Any assistance would be greatly appreciated.

Sorry if this is in the wrong forum and worded incorrectly, just a newb here. I was unsure of what to search for so I am just going to post my question.

I have two worksheets with similar data. I want to compare the cells in one column in worksheet A to a column in worksheet B. If the values are the same then I want to copy a cell from the row with the similar value in worksheet A to a cell in worksheet B

Example workbook. If A1 of worksheet B is equal (fuzzy equal) to A1 of worksheet A then copy E1/F1 from worksheet B to E1/F1 of worksheet A. And then repeat down each row until the end.

Thanks a lot.

I have a workbook with several worksheets of data and one worksheet of forumulas which does a lot of calculations. In one column on this calculation worksheet there are hyperlinks derived from URL's found on the preceding data worksheets.

This last calculations worksheet is delivered to clients. The other data pages are not and they need to remain confidential. So, to break the links with the other worksheets I copy paste the values of the last worksheet to a new workbook and send it to the client.

Unfortunately, the hyperlinks are lost in this process as they can no longer find the URL's.

Is there a way to copy/paste hyperlinks from my last worksheet to another workbook and have the hyperlink retain the URL?

Thanks for any assistance that's offered.



I have one file containing multiple worksheets. Each worksheet contains
one invoice and I want to create a separate worksheet that pulls
details such as inv number, date, net, vat and total from all the other
sheets. Each invoice layout is exactly the same.

I have created one row in the summary worksheet containing all the info
I need from the first invoice worksheet. Is there a way that I can copy
and paste that row but get it to increment to the next worksheet each

Sorry I may be way off here - if anyone can give any help or make
alternative suggestions I'd be really grateful!



I need to create a chart from data contained in multiple worksheets (all in the same workbook). I am using Excel 2003. I need to chart a specific cell within each worksheet. It's the same cell in each worksheet, say B7. I have about 100 worksheets. So I need to be able to select all 100 worksheets and tell Excel to chart the progress of the B7 cell in each worksheet. Each worksheet contains data for one day and is named after the date/time of creation. So really the chart is to show the value of the B7 cell over 100 days.

Does that make sense? Can anyone help me to create this chart?


I have a file that has multiple worksheets with some of the worksheets visible and some of the worksheets hidden. I want a macro to run only on the visible worksheets and to ignore the hidden worksheets. The macro below is supposed to create a header on all the sheets in the workbook that combines the file name with information from cell C1 of the tab titled Productivity and then add the worksheet name on a new line. Right now my macro runs on all the sheets in my workbooks, both visible and hidden, but I only want it to run on visible sheets. Any help would be appreciated.

Sub Workbook_BeforePrint()
Dim ws As Worksheet, wsT As Worksheet
Set wsT = Sheets("Productivity")
For Each ws In Worksheets
ws.PageSetup.CenterHeader = "&f " & wsT.Range("C1").Value & Chr(13) & "&a"
Next ws
End Sub

hi all,

i'd love it if someone could show off their excel prowess on this one.

what i'm trying to do is have a workbook with multiple "common"
worksheets and one main worksheet. on the main worksheet i want to
have a drop-down menu where you select which common worksheet you want
to pull data from to populate the main worksheet.

the common worksheets will always be the exact same structure and will
contain a weeks worth of data. every week i want to "move and copy" a
common worksheet so that i have one worksheet for evey week. i don't
want to have to make a million worksheets at the inception of the

what i think will be the tricky part is having the drop-down menu
automatically update so at any point in time i would be able to select
any common worksheet available.

ready to learn...


I am a beginner to Excel and VBA, can somebody show me a few lines of scripts and instructions how to use VBA scripts to combine multiple Excel xls Files (which contain single worksheet) into a single Excel file of multiple worksheets?

Can somebody also suggest a good book with examples I can start to learn to solve these kinds of problems?

Thanks very much


Here is my task:

I have many worksheets (30 or more) called Product1, Product2, Product3, etc. The number of worksheets is constantly increasing. For each worksheet, I need to look in column B starting at row 5 and find the last row with data in it, then copy the data in columns A-K, rows 5 through the last row.

I will then consolidate that data in a worksheet called Orders. I will copy the data into the Orders worksheet by looking in Column B starting at row 5 and paste into the first empty row using columns A:K. I will then repeat that process for each Product worksheet.

Thanks for any help provided.


Hi all,

I need help in putting together a macro that collapses groups across all of my worksheets.

I know the VBA to collapse the groups in the active worksheet


ActiveSheet.Outline.ShowLevels RowLevels:=1


But i don't know how to write a macro that will go to each worksheet in my workbook and collapse the groups.

Assume the worksheets are called 'sheet1', 'sheet2' etc.

Any help would be greatly appreciated!



Hello! I have many single-worksheet workbooks, all stored in a single folder. I would like to copy the second column of each workbook into a single master worksheet. My version of VBA is 6.3, if that matters.

Thanks for your help!

Hi, I'm having a problem.

Is there a way to copy a consecutive formula to seperate worksheets within a workbook

for example

I have 32 tabs ('Total','1','2','3','4','5'... etc) 1-31 being seperate dates.

I want to have Cell A1 in worksheet 'Total' to read the current month & year, so i have the cell value as 1/1/08 (farmatted to read January 2008)

I want cell A1 in each consecutive worksheet to read the date (plus 1 day)

worksheet '1' A1=Total!A1
worksheet '2' A1=1!A1+1
worksheet '3' A1=2!A1+1
worksheet '4' A1=3!A1+1


worksheet '1' A1=Total!A1
worksheet '2' A1=Total!A1+1
worksheet '3' A1=Total!A1+2
worksheet '4' A1=Total!A1+3

my question is can I have excel fill this consecutive formula without having to change each A1 cell to read the previous worksheets name?

Just a time consuming process I would like to reduce.