Code copying header rows

andysh

Board Regular
Joined
Nov 8, 2019
Messages
111
Hi,

I have the below code which copies data from several workbooks to a master file. It works perfectly and ignores the header row when there is data in row 2 and below. However, if there is no data in row 2 and below (i.e. blank sheet apart from the headers) it copies the header row. Is there something incorrect in the code? What can I change to make sure it always ignores headers?

VBA Code:
Sub btnImport_Click()

Application.ScreenUpdating = False

Dim wkbDest As Workbook

Dim wkbSource As Workbook

Set wkbDest = ThisWorkbook

Dim LastRow As Long

Const strPath As String = "P:\Returns\Engineering Jobs\Toms Triage\Process User Forms\"

ChDrive "P"

ChDir strPath

strExtension = Dir("*.xlsm")


Do While strExtension <> ""

Set wkbSource = Workbooks.Open(strPath & strExtension)

With wkbSource

LastRow = .Sheets("Triage").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


With .Sheets("Triage").Range("A2:K" & LastRow)

.Copy wkbDest.Sheets("Triage Master").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

.EntireRow.Delete

End With


.Close savechanges:=True

End With

strExtension = Dir

Loop

Application.ScreenUpdating = True


MsgBox " Import Complete !"

End Sub
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You could make LastRow have a minimum bound of 2, e.g:

VBA Code:
LastRow = Application.Max(2,LastRow)

Or you could nest you code in an If:

VBA Code:
If LastRow >=2 Then
     'do your stuff
End If
 
Upvote 0
Try adding the blue line:
Rich (BB code):
LastRow = .Sheets("Triage").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

If LastRow = 1 Then LastRow = 2

With .Sheets("Triage").Range("A2:K" & LastRow)

Edit:
The above code will copy paste a blank row. So maybe this one:
VBA Code:
If LastRow > 1 Then
    With .Sheets("Triage").Range("A2:K" & LastRow)
    
    .Copy wkbDest.Sheets("Triage Master").Cells(Rows.count, "A").End(xlUp).Offset(1, 0)
    
    .EntireRow.Delete
    
    End With
End If
 
Last edited:
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top