Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How can i not copy the headers?

11 views
Skip to first unread message

kardifflad

unread,
Nov 14, 2012, 12:46:34 PM11/14/12
to

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




--
kardifflad

Claus Busch

unread,
Nov 14, 2012, 2:49:20 PM11/14/12
to
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

Wouter HM

unread,
Nov 14, 2012, 2:55:20 PM11/14/12
to
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

unread,
Nov 20, 2012, 5:59:06 AM11/20/12
to

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

thank you both.




--
kardifflad
0 new messages