Hello and thank you in advance.
I have this bit of code that works almost perfectly. It copies all data below the headers on row 3, into a new workbook. If there is data in the sheet then it works fine, however if there is no data then it copies the headers.
how can i make it so it knows theres no data below row 3 and so gives a 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.Close

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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

thank you both.