Macro to copy data from multiple worksheets into 1 worksheet

[Amstrad]

I have a spreadsheet that has hundreds of different worksheets. Within each worksheet there is a single row of data from cell A2 to J2.

I want to copy this row from each worksheet into a single worksheet. I also want it to copy the name of the worksheet into column A and then paste the data next to it. That way, I will be able to see which worksheet the data is for.

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.

 

[dmalviya]

Hi
The above macro is amazing. However I have similar problem. I have a folder called reports which is saved under desktop and there are 30 worksheets in the folder. I now need to copy particular tab (Data) from all workbooks and save all into a Master file.
Many thanks in advance

 

[MO11STA]

Hi,

I am try to create a macro to transfer specific data (staff names) from one worksheet to another (cell row could be different each time this is done but will always be in column C) but having difficulties so I am wondering if you can help please?

I run a daily report in which I have a list of staff in two different business sectors, however I gather the data online and put this onto a spreadsheet but this data is gathered into one list on sheet1 of a workbook. I have to cut and paste the people from each sectors into separate worksheets but in one workbook and was wondering if you can help me create a macro for this?

For DPA reasons, I can not post staff names but if I can get help creating this macro and where required in this code the specific data (staff names) can be highlighted so I can edit this code to fit, this would be great!

Also, does anyone know if there is a macro code for pasting in 'match destination formatting' as a default or assigning CRTL + V to this?

Thanks in advance!

 

[MO11STA]

.

 

[jithinmenon]

I have a spreadsheet that has many different worksheets. Within each worksheet there are data across rows and columns. All I want is to copy the row of data from cell I10 to AC10. I want to copy the row from each worksheet into a single worksheet/ summary sheet. I also want it to copy the name of the worksheet into column A and then paste the data next to it. That way, I will be able to see which worksheet the data is for.

ex:

WorksheetA: I10 J10 K10 ------- AC10
WorkSheetB:I10 J10 K10 ------- AC10

Any help on this is greatly appreciated.


Can any changes be made to the one below?

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" Then
Sht.Select
Range("A:A").Insert
Range("A2").Formula = "=Mid(Cell(""filename"",B1),Find(""]"",Cell(""filename""))+1,255)"
Range("A2").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues
Range("A2:K2").Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A:A").Delete
Else
End If
Next Sht


End Sub

Thanks for your help.

 

[My Aswer Is This]

Try this:
This script will copy data into sheet(1) which is the sheet to the far left on your tab menu

Sub Copy_Data()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Sheets.Count
        Sheets(i).Range("I10:AC10").Copy Cells(Lastrow, 2)
        Cells(Lastrow, 1).Value = Sheets(i).Name
        Lastrow = Lastrow + 1
    Next
Application.ScreenUpdating = True
End Sub

 

[Neavalmi]

This willwork, let me know if there's any problems. And if anyone wants to clean up my code a little, feel free.

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" And Sht.Range("A2").Value <> "" Then
Sht.Select
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "M")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A2", Cells(LastRow, "M")).ClearContents
Else
End If
Next Sht


End Sub

Dear Phanmore,
Sorry to revive your old thread. Not sure if also if you can still read this but if anyone can help me, appreciate it very much.

I tried to use the code above since the first one in your answer contains only 1 row of data. Mine contains more than 1 data.
The code i have quoted here is deleting my data in each worksheet and also it does not write the "worsheet name" similar to the first one (#2 reply).

Thanks in advance for your hel por from anyone to modify above macro to my data:

Original data on each worksheet should not be deleted
Worksheet name should be written in column "A" of the Master.
My data has A to R columns (18) - fixed
My data contains more than 1 row - around 1,000 to 5,000 rows per worksheet
My data start on row 41 (headers)

Really appreciate anyone's help.

Thanks,
Nea

 

[LTRS777]

This willwork, let me know if there's any problems. And if anyone wants to clean up my code a little, feel free.

Sub CombineData()
Dim Sht As Worksheet
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name <> "Master" And Sht.Range("A2").Value <> "" Then
Sht.Select
LastRow = Range("A65536").End(xlUp).Row
Range("A2", Cells(LastRow, "M")).Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sht.Select
Range("A2", Cells(LastRow, "M")).ClearContents
Else
End If
Next Sht


End Sub

Thank you this really helped! Is there a way to have the formula stop after the least column with a header in it on each spreadsheet? I am using this to compile multiple tabs for a tab-delimited file and its adding a lot of extra tabs/columns to the end of each line item (depending on how much data is in each sheet). Thanks for your help!

 

[jessicafang]

Sorry to bring this thread back. But is there a macro could combine 5 different macros on 5 different sheet? Currently I have to go to each sheet to run the macro, would be easy if I could just combine all the five and run one Macro. Many thanks, Jess

 

[My Aswer Is This]

jess It would probable be better to start a new posting and explain what your wanting to do. There are a half dozen different scripts here on this thread.

Sorry to bring this thread back. But is there a macro could combine 5 different macros on 5 different sheet? Currently I have to go to each sheet to run the macro, would be easy if I could just combine all the five and run one Macro. Many thanks, Jess

 

[jessicafang]

jess It would probable be better to start a new posting and explain what your wanting to do. There are a half dozen different scripts here on this thread.

Will do. Thanks for reading.