How to combine sheets in Excel

[kamus45]

Hi, I'm new in the forum and I have this question about how to combine sheets in Excel

I got a file with sheets "A1, B1, C1... J1" with data in column A and need to combine it in "Final" sheet, searching in google I find this post:

http://www.mrexcel.com/forum/excel-...e-columns-multiple-sheets-into-one-sheet.html

But don't know how to use it to get what I need.

Sheets("Final").ActivateColumns("A:A").Select
Selection.EntireRow.Delete

Dim ws As Worksheet
For Each ws In Sheets(Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"))
  With ws
    .Range("A:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1)
  End With
Next ws
Sheets("Final").Activate

Thanks for your help

 

[Joe4]

Welcome to the Board!

You just need to make one minor change. You need to add a row number after the first A, i.e. see the number I added in red:

    .Range("A[COLOR=#ff0000][B]1[/B][/COLOR]:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1)

 

[kamus45]

Thanks Joe4

I did the change but only copy and paste the data of A1 sheet and get run time error (1004)

'runtime error 1004 - microsoft excel cannot paste the data'

don't get the data from B1 to J1 sheets

 

[Joe4]

Here is the version of the code that I used that worked for me:

Sub MyCopyData()

Dim ws As Worksheet

Sheets("Final").Activate
Columns("A:A").EntireRow.Delete

For Each ws In Sheets(Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"))
  With ws
    .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1)
  End With
Next ws
Sheets("Final").Activate

MsgBox "Done!"

End Sub

If it does not work for you, check the following things:
1. Make sure that your sheet names match EXACTLY.
2. Are any of your sheets hidden?
3. Are any of the ranges you are copying from/to protected, hidden, or part of merged cells?
4. How many rows of data are you working with? When they are all combined, will they go beyond the last possible row in Excel?

If you are still having issues figuring it out, I would recommend stepping through your code line-by-line, while watching what happens and see where things are when the error occurs. That often sheds light on what it happening.

 

[kamus45]

No luck yet, still getting 1004 error

1. Make sure that your sheet names match EXACTLY.
All sheets names match exactly

2. Are any of your sheets hidden?
No one is hidden

3. Are any of the ranges you are copying from/to protected, hidden, or part of merged cells?
A1 range A1:A160, B1 range A1:A158, C1 range A1:A60, D1 range A1:A109, E1 range A1:A79... no hidden, merged or protected cells, but only paste the data from A1 sheet and send error

4. How many rows of data are you working with? When they are all combined, will they go beyond the last possible row in Excel?
No, maximun amount of rows are 2000 when all are combined

 

[kamus45]

Can't paste information because copy area and paste area have diferent forms, try this
-Clic in one unique cell and then chose paste
-Select a rectangule with the same size and form and chose paste

 

[Joe4]

That makes no sense to me, as this part:

Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Offset(1)

is, in fact, selecting a single cell to paste to.

We are just copying values from Column A on all the sheets to Column A on the Final sheet, right?
What exactly is in these cells? There aren't any images, are there?
And we are certain that we do not have any merged cells on any of our sheets?

Out of curiosity, what version of Excel are you using?

 

[kamus45]

We are just copying values from Column A on all the sheets to Column A on the Final sheet, right?
- Yes only copy the values from A1 to J1 in Final Sheet


What exactly is in these cells? There aren't any images, are there?
- No images, only text

And we are certain that we do not have any merged cells on any of our sheets?
- Yes I'm sure that are not merge cells in any sheets

Out of curiosity, what version of Excel are you using?
- I use 2013, but I try in 2003, 2010 and get the same error in 3 versions.

 

[Joe4]

This SHOULDN'T make a difference, but let's try breaking it down into smaller, more explicit steps, like this:

Sub MyCopyData()

Dim ws As Worksheet
Dim lastRow As Long
Dim newRow As Long

Sheets("Final").Activate
Columns("A:A").EntireRow.Delete

For Each ws In Sheets(Array("A1", "B1", "C1", "D1", "E1", "F1", "G1", "H1", "I1", "J1"))
    Sheets("Final").Activate
    newRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    ws.Activate
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    ws.Range("A1:A" & lastRow).Copy Sheets("Final").Cells(newRow, "A")
Next ws

Sheets("Final").Activate

MsgBox "Done!"

End Sub

The only other thing I can think of is that you are not placing the code in the correct module. Are you placing it in a General Module and NOT one of the specific "Sheet" or "ThisWorkbook" modules?

If none of that resolves the issue, then I suspect that there may be something special or different about your workbook that you aren't telling us (maybe because you are not aware of it, or aware that is impacting the macro). The only suggestion I would have then is to upload your file to a file sharing site so someone can look at it for themselves to see if they can see what the issue is.

 

[My Aswer Is This]

You have given your sheets unusual names so I'm going to assume:
We are dealing with Sheet(1) to Sheet(10) and copying all the data into sheet named "Final"
Sheet(1) is the sheet on the far left side on your tab bar
Try this:

Sub Copy_Column_A()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To 10
    Sheets(i).Range("A1:A" & Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Final").Cells(Sheets("Final").Cells(Rows.Count, "A").End(xlUp).Row + 1, 1)
Next
Application.ScreenUpdating = True
End Sub