Copying a range of cells and paste only values in the same range in another sheet

Question

i have this loop, looping through a sheet rows and i need to paste the same range of cells in another sheet with the same structure.

 For i = 1 To NumRows 
            
                
                Set rng = sheet.Range("D" & i)
                rng.EntireRow.Copy otherSheet.Cells(i + LastRow, 1)
                
                
                
 Next i 

Now if i have to paste only values from the Entirerow to the 'otherSheet', how can i achieve this?

Answer 1

Please, test the next code. It assumes that your first row in each sheet can be used like reference to calculate the number of columns to be calculated. If not the case, plese specify which row to be used:

Sub testCopyRangeFromAllSheetsToMaster()
 Dim sh As Worksheet, shCons As Worksheet, lastR As Long, lastC As Long
 Dim lastRCons As Long, arr

 Set shCons = Worksheets("otherSheet")    'use here your consolidation sheet to copy the range
 For Each sh In ThisWorkbook.Sheets
    If sh.Name <> shCons.Name Then
        lastR = sh.Range("A" & rows.count).End(xlUp).row  'last row in the sheet to copy from
        lastC = sh.cells(1, Columns.count).End(xlToLeft).Column 'last column
        arr = sh.Range("A1", sh.cells(lastR, lastC)).Value 'Put the range in an array
        lastRCons = shCons.Range("A" & rows.count).End(xlUp).row + 1 'last empty row in the consolidation sheet
        'drop the array content at once:
        shCons.Range("A" & lastRCons).Resize(UBound(arr), UBound(arr, 2)).Value = arr
    End If
 Next sh
 MsgBox "Ready..."
End Sub

If you want avoiding copying the headers, you should replace sh.Range("A1"... with sh.Range("A2".... This means that the array is built starting with the second row.