How can i not copy the headers?

[kardifflad]

Hello and thank you in advance.
I have this bit of code that works almost perfectly. It copies all dat
below the headers on row 3, into a new workbook. If there is data in th
sheet then it works fine, however if there is no data then it copies th
headers.
how can i make it so it knows theres no data below row 3 and so gives
msgbox to say "nothing to copy"?
here is my code so far:

'*** COPY DATA TO MASTER ARCHIVE ***
' Gather data
Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

' copy the data
Range("A4:F" & LastRow).Copy

' add the data
MasterFile = "\\my path\Complete archive.xls"
Workbooks.Open (MasterFile)
Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Offset(1, 0).Select
NextRow = ActiveCell.Row

' paste the data
Worksheets("Cardiff").Range("A" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3").Select
ActiveWorkbook.Save
ActiveWorkbook.Clos

 

[Claus Busch]

Hi,

Am Wed, 14 Nov 2012 17:46:34 +0000 schrieb kardifflad:

' Gather data
Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

try:

With Worksheets("Sheet1")
lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
If lastrow = 3 Then
MsgBox "No data to copy"
End If
End With


Regards
Claus Busch

 

[Wouter HM]

Hi kardifflad,

After you set the vaue for LastRow test if it is greater the 3:

'*** COPY DATA TO MASTER ARCHIVE ***
' Gather data
Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row

If LastRow > 3 then

' copy the data
Range("A4:F" & LastRow).Copy

' add the data
MasterFile = "\\my path\Complete archive.xls"
Workbooks.Open (MasterFile)
Worksheets("Sheet1").Activate
Range("A65536").Select
ActiveCell.End(xlUp).Offset(1, 0).Select
NextRow = ActiveCell.Row

' paste the data
Worksheets("Cardiff").Range("A" & NextRow).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A3").Select
ActiveWorkbook.Save
ActiveWorkbook.Close

End If

Hoop This Helps,
Wouter

 

[kardifflad]

if Lastrow >3 - wow so simple i wonder how i missed that.

thank you both