0

I'm trying to merge several sheets into one.

Configuration

DataSheet1 : First sheet
DataSheet2 : Second sheet
ConsolidatedSheet : Consolidated sheet

Code

Set consolidatedSheet = Worksheets("ConsolidatedSheet")
consolidatedSheet.Activate

startRow = 2
startCol = 1

Worksheets("DataSheet1").Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
consolidatedSheet.Range("A" & consolidatedSheet.Cells(Rows.Count, 1).End(xlUp).row + 1)

Worksheets("DataSheet2").Activate
lastRow = Cells(Rows.Count, startCol).End(xlUp).row
lastCol = Cells(startRow, Columns.Count).End(xlToLeft).Column
Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy _
consolidatedSheet.Range("A" & consolidatedSheet.Cells(Rows.Count, 1).End(xlUp).row + 1)

Issue

Two arrays are created in the consolidated sheet. It means I can't sort on the consolidated sheet.

How do I copy data as values instead of arrays?

Improve this question
9
  • Have you tried using Range.PasteSpecial Paste:=xlPasteValues? Commented May 5, 2021 at 15:15
  • with this code ? consolidatedSheet.Range("A" & consolidatedSheet.Cells(Rows.Count, 1).End(xlUp).row + 1).PasteSpecial Paste:=xlPasteValues. Because I'm facing following issue : Compile error, Expected : end of statement` Commented May 5, 2021 at 15:17
  • First you must do Range.Copy. See stackoverflow.com/questions/67322157/… Commented May 5, 2021 at 15:22
  • It is done on the row : Range(Cells(startRow, startCol), Cells(lastRow, lastCol)).Copy Commented May 5, 2021 at 15:31
  • And what you called an array - did you mean formula array or a smart table? Commented May 5, 2021 at 15:39

2 Answers 2

Reset to default
1 
Sub consSheets()
    Dim ws As Worksheet
    With Worksheets("ConsolidatedSheet")
        .Cells.Delete   ' clear the assignment sheet first
        For Each ws In Sheets(Array("DataSheet1", "DataSheet2"))
            ws.Cells(2, 1).CurrentArray.Copy
            .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        Next ws
    End With
    Application.CutCopyMode = False ' "clears" the clipboard
End Sub

Edit2: (not copy headers from DataSheet1 and DataSheet2 and keep existing header in ConsolidatedSheet)

Sub consSheets()
    Dim ws As Worksheet
    With Worksheets("ConsolidatedSheet")
        .Rows("2:" & .UsedRange.Row + .UsedRange.Rows.Count).Delete ' clear (without header in Row 1) the assignment sheet first
        
        For Each ws In Sheets(Array("DataSheet1", "DataSheet2"))
            Set Rng = ws.Cells(2, 1).CurrentRegion
            Set Rng = Intersect(Rng, Rng.Offset(1)) ' eliminate headers
            
            If Not Rng Is Nothing Then
                Rng.Copy
                .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            End If
        Next ws
    End With
    Application.CutCopyMode = False ' "clears" the clipboard
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for your comment. However, I'm facing an error No Cells were found on the row ws.Cells(2, 1).CurrentArray.Copy
@Royce you previously wrote: "By array I mean, formula array yes". Given the error, it appears that you don't have an array formula (support.microsoft.com/en-us/office/…) in this location. You can use another way to select the area
... for example, ws.Cells(2, 1).CurrentRegion.Copy
Thank you for you feedback. It is working fine. The only remaining point is headers. This code copying headers from DataSheet1 and DataSheet2 and remove header in ConsolidatedSheet. It is possible to not copy headers from DataSheet1 and DataSheet2 and keep existing header in ConsolidatedSheet ?
@Royce possible, see Edit2 in the solution
1

I'm not sure what you mean by it creating arrays, and I don't think that code is actually the code using as it's not doing what you describe.

But here's something that does what your intending.

Option Explicit
Sub Test()
    
    Dim cSht As Worksheet
    Set cSht = Worksheets("ConsolidatedSheet")
    
    Dim StartRow As Integer, StartCol As Integer
    StartRow = 1
    StartCol = 1
    
    'Split out to a sub and don't need to repeat self
    Call ConsolidateData(cSht, "DataSheet1", StartRow, StartCol, True)
    Call ConsolidateData(cSht, "DataSheet2", StartRow, StartCol)
    
End Sub

Private Sub ConsolidateData(cSht As Worksheet, FromSheet As String, StartRow As Integer, StartCol As Integer, Optional IncludeHeader As Boolean)
    
    Dim FromRow As Integer
    If IncludeHeader Then
        FromRow = StartRow
    Else
        FromRow = StartRow + 1
    End If
    
    With Worksheets(FromSheet)
        lastrow = .Cells(.Rows.Count, StartCol).End(xlUp).Row
        lastcol = .Cells(StartRow, .Columns.Count).End(xlToLeft).Column
        
        'Just transfering value is faster then copy, but doesn't bring formatting
        cSht.Cells(cSht.Rows.Count, 1).End(xlUp).Resize(lastrow - FromRow, lastcol - StartCol).Value2 = .Range(.Cells(FromRow, StartCol), .Cells(lastrow, lastcol)).Value2
        
    End With

End Sub
Improve this answer

1 Comment

Thank you very much this is exaclty what I'm looking for. However, the last column and the last row are not copied. Any idea why ? +1 on lastcol fixed a part of the problem. However, +1 on lastrow will works only for the last sheet copied... The last row of previous sheet is replaced by the first row of the next sheet copied.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.