Excluding 1 line from VBA Copy Range

Question

I am working on some code to consolidate multiple worksheets which form individual parts lists into 1 large parts list.

So far I have 2 functions which scan each worksheet for the last row and column

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
                        After:=sh.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
    On Error GoTo 0
End Function

and

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
                        After:=sh.Range("A1"), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
    On Error GoTo 0
End Function

I then have another sub which creates a new worksheet called 'Parts List' and pastes the ranges in there.

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Parts List").Delete
On Error GoTo 0
Application.DisplayAlerts = True

' Add a new summary worksheet.
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Parts List"


' Loop through all worksheets and copy the data to the
' summary worksheet.
For Each sh In ActiveWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then

        ' Find the last row with data on the summary worksheet.
        Last = LastRow(DestSh)

        ' Specify the range to place the data.
        ' Set CopyRng = sh.Range("B3:G10").
        Set CopyRng = sh.UsedRange

        ' Test to see whether there are enough rows in the summary
        ' worksheet to copy all the data.
        If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
            MsgBox "There are not enough rows in the " & _
               "summary worksheet to place the data."
            GoTo ExitTheSub
        End If

        ' This statement copies values and formats from each
        ' worksheet.
        CopyRng.Copy
        With DestSh.Cells(Last + 1, "A")
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        End With

        ' Optional: This statement will copy the sheet
        ' name in the H column.
        DestSh.Cells(Last + 1, "I").Resize(CopyRng.Rows.Count).Value = sh.Name

    End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

' AutoFit the column width in the summary sheet.
DestSh.Columns.AutoFit

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub

The issue that I am having is that the title rows are being copied with the ranges. Does anyone know how to exclude the titles from the row and column scan or from being copied?

Thanks for any help Dan

Answer 1

Haven't tested it, but something along these lines should help you by looping through all rows in the cell and making a new range out of this using the union function. Then when all rows are checked for numerical values totalrange can be copied using your code.

Dim row as integer
Dim temprange as range
Dim totalrange as range
Dim startrow as integer
For row = 2 to lastrow+1  `assuming there is always a title in row 1
If IsNum(Cells(row,1)) = false Then
    If temprange = Nothing then
         Set temprange = Range(Cells(2,1),Cells(row-1,[lastcolumn number] `[replace with number of last column]
         startrow = row+1
    Else
         Set temprange = Range(Cells(startrow,1),Cells(row-1,[lastcolumn number])
    End if
    If totalrange <> Nothing then
          Set totalrange = Union(totalrange,temprange)
    Else
          Set totalrange = temprange
    End if
End if
Next row

Second approach, deleting title rows before copying

For row = lastrow to 1 step -1
If IsNum(Cells(row,1) = False then
    Rows(row).EntireRow.Delete
End if
Next row

Then call your last row function again and do the rest of your code.