Skipping code if a worksheet has no data

Question

I have some code for coping data from two worksheets and pasting into another. There will be times when one of the worksheets may not have any data. Is there an IF statement that I can use to skip the procedure of copying from a blank worksheet?

The next part of my problem is, if the "Job_Surplus" worksheet is blank, when the code gets to the part in the "Kits" that is bold and underlined, the code takes the active cell to the very end of the "Text" worksheet and then fails. 

Here is what I have so far and either of the worksheets could be blank on any given day.

    'Copies data for Job Surplus

    Sheets("Job_Surplus").Select

    Range("A2:J2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Sheets("Text").Select

    Range("A1").Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

    'Copies data for Kit Orders

    Sheets("Kits").Select

    Range("A2:J2").Select

    Range(Selection, Selection.End(xlDown)).Select

    Selection.Copy

    Sheets("Text").Select

   Range("A1").Select

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

        :=False, Transpose:=False

Answer 1

To:  darrend7

re:  no action

There has to be an entry in column 10 (column J) or the code does not copy anything.

[Edit] If that is the case then change all "10" in the code to a column number that will always have the most data.

[Edit]If that is the case then change "10" in...

LastRowJ = .Cells(.Rows.Count, 10).End(xlUp).Row

LastRowJ = Sheets("Kits").Cells(Sheets("Kits").Rows.Count, 10).End(xlUp).Row

Answer 2

To:  darrend7

re:  improve code

Code not tested:

'---

Sub Something_R1()

Dim LastRowA As Long

Dim LastRowJ As Long

With Sheets("Job_Surplus")

  LastRowJ = .Cells(.Rows.Count, 10).End(xlUp).Row

  If LastRowJ > 1 Then

    .Range(.Cells(2, 1), .Cells(LastRowJ, 10)).Copy

     Sheets("Text").Range("A1").PasteSpecial Paste:=xlPasteValues

  End If

End With

LastRowJ = 0

LastRowJ = Sheets("Kits").Cells(Sheets("Kits").Rows.Count, 10).End(xlUp).Row

If LastRowJ > 1 Then

   With Sheets("Kits")

    .Range(.Cells(2, 1), .Cells(LastRowJ, 10)).Copy

   End With

   With Sheets("Text")

     LastRowA = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

    .Cells(LastRowA, 1).PasteSpecial Paste:=xlPasteValues

   End With

End If

End Sub

'---

Note:  you may want to put a message box in there somewhere if LastRowJ = 0.

'---

The new (free) Excel workbook "Professional_Compare" is now available at MediaFire.(compares every cell Or every row in two worksheets - choice of compare type)Download (no ads) from: http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

Answer 3

ok, I got it working now. Thank you for your help

Answer 4

Not sure if I was suppose to paste this over the code I had already written, but I did just that. The macro runs, but nothing gets copied from the worksheet that does have data.