VBA - Copying Table of data from 3 different sheets and into one table on another sheet

[mrsushi]

HI
I have 3 tables of data and wish to copy from all 3 tables to be placed into one table located on another sheet.

The 4 sheets have tables of data

Summary
Summary 2
Summary 3
Final Summary

Summary, Summary 2 and Summary 3 has data which starts from cell C7 to G7 and rows of data

What I need is to have each of the tables of data put into the sheet Final Summary - in cell C7 - G7. How do I add to the below code to consolidate this? The last line of code is position to paste table from Summary 2 sheet

Many thanks

Sub CopySummaryUSD()

Sheets("Summary").Select
Range("C7:G" & Range("C" & Rows.Count).End(xlUp).Row).Copy
Sheets("Final Summary").Range("C7").PasteSpecial Paste:=xlPasteValues
Sheets("Final Summary").Select

Range("C7").End(xlDown).Offset(1).Select
End Sub

 

[smozgur]

It can be done by using less code lines, but I wanted to explained step by step by using source and target objects.

Assumptions:

1. Final Summary worksheet has headers at C6:G6.
2. First data row is defined as C7:G7.

Note: Edited code to replace Copy method as I noticed you want to copy only data.

Sub CopySummaryUSD()
Dim shtFinal As Worksheet
Dim arrSheets As Variant
Dim strSheet As Variant
Dim shtSummary As Worksheet
Dim rngSummaryData As Range
Dim rngTarget As Range

    ' Target sheet object - Final Summary
    Set shtFinal = Sheets("Final Summary")
    
    ' Summary sheet names in array
    ' so we can iterate and reuse the action code
    arrSheets = Array("Summary", "Summary 2", "Summary 3")
    
    ' Loop through sheet names
    For Each strSheet In arrSheets
        ' Current summary sheet to copy data from
        Set shtSummary = Sheets(strSheet)
        
        ' Range to be copied in the current summary sheet - C7:G7 is hardcoded
        Set rngSummaryData = shtSummary.Range(shtSummary.Range("C7:G7"), shtSummary.Cells(shtSummary.Rows.Count, "C").End(xlUp))
        
        ' Target cell to paste copied data
        Set rngTarget = shtFinal.Cells(shtFinal.Rows.Count, "C").End(xlUp).Offset(1)
        
        ' Copy data from the current sheet to the target sheet
        ' starting from the target cell
        ' rngSummaryData.Copy rngTarget 'Edited this line: Copy method will copy with formulas, use following to transfer data only
         rngTarget.Resize(rngSummaryData.Rows.Count, rngSummaryData.Columns.Count).Value = rngSummaryData.Value
    Next strSheet
    
    ' Go to last copied cell in Final Summary sheet
    Application.Goto shtFinal.Cells(shtFinal.Rows.Count, "C").End(xlUp)
End Sub

 

[mrsushi]

Thank you very much Smozgur. The explanation's are very helpful.

One last question, although its copying from the summary sheets to the final, the data from the summary sheets are vlookups from other worksheets. So when copying from summary sheets to final, they will contain the vlookups. How will the formula be amended to only paste special values (without the vlooksup)

Many thanks

 

[smozgur]

Code is supposed to transfer values only.
That's why I put a note to indicate why I didn't use the Copy method.

        ' Copy data from the current sheet to the target sheet
        ' starting from the target cell
        ' rngSummaryData.Copy rngTarget 'Edited this line: Copy method will copy with formulas, use following to transfer data only
         rngTarget.Resize(rngSummaryData.Rows.Count, rngSummaryData.Columns.Count).Value = rngSummaryData.Value

 

[mrsushi]

Many thanks for confirming .Looks good