Copy data from different tabs and paste it in one sheet

[Vimal_ku_mar]

Hi All,

I am working on an excel file, which has over 30 tabs with data in it starting from line # 18 till line # 38 and from Column A to AD.

I want to copy the data from all the tabs and paste it into one tab in the same file (let's call it as Summary). In addition to this, I also want to copy the tab name in the AE column in the summary file.

Please help me with the Macro that can help me in doing this job.

thanks,
Vimal

 

[dfsobral]

Probably you don't need a macro to do it but I would need an example of the data

 

[Vimal_ku_mar]

Please find the data that is placed in all the tabs. which i want to copy and paste in one tab.

Name	Department	Class	Section
ABC	IT	2nd Class	3rd
CDE	Finance	1st Class	2nd
EFD	US	3rd Class	1st
EGT	UK	4th Class	4th

<tbody>
</tbody>

 

[dfsobral]

In this example how do you want the information to be shown
ABC, IT, 2nd Class, 3rd

Or do you want other info as well?

 

[mumps]

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWs As Worksheet, bottomA As Long
    Set desWs = Sheets("Summary")
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            bottomA = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            ws.Range("A18:AD38").Copy desWs.Cells(desWs.Rows.Count, "A").End(xlUp).Offset(1, 0)
            desWs.Cells(desWs.Cells(desWs.Rows.Count, "AE").End(xlUp).Row + 1, "AE").Resize(bottomA - 17) = ws.Name
        End If
    Next ws
End Sub

 

[Vimal_ku_mar]

I want whatever data is available in the tabs from the given criteria along with the tab name to be added to AE column please

 

[Vimal_ku_mar]

When i am running this macro i am getting the error as Visual Basic application error 400

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.