Hi:

I posted this last night but I think I was unclear as to what I need to do.
The code below (which was put together with the help of several forum
members) performs a check on 10 different worksheets to determine if anything
is in the first data entry cell of the last page. If not it goes to the next
page (up). When data is found it copies all cells on that page and above to a
primary spreadsheet. However, I am finding that I am spending a lot of time
adjusting row heights. Can anyone help me get this to copy the entire row,
rather than just the cells. I understand that would preserve the row heights
and solve my problem.
All sheets are same # columns and Rows.

It also goes back to sheets and clears entered data.


Sub Data_Ranges_Copy_and_Clear()

Dim vCopySheets As Variant
Dim vCheckPoints As Variant
Dim vCopyRange As Variant
Dim rng As Range
Dim Rng2 As Range
Dim Rng3 As Range

Dim iCounter As Integer
Dim iCounter2 As Integer


vCopySheets = Array("Cores", "NPN", "Est", "GOG", "Fact Claim", "OS Claim",
"Fact PS", "OS PS", "INV-NOT-REC", "Prepaid", "Sold During")

'Select each sheet in turn

For iCounter = LBound(vCopySheets) To UBound(vCopySheets) Step 1
Sheets(vCopySheets(iCounter)).Select

'Cells on this sheet to test
vCheckPoints = Array("A279", "A249", "A219", "A189", "A159", "A129", "A99",
"A69", "A39", "A9")

'Corresponding ranges to copy
vCopyRange = Array("A1:P300", "A1:P270", "A1:P240", "A1:P210",
"A1:P180", "A1:P150", "A1:P120", "A1:P90", "A1:P60", "A1:P30")

For iCounter2 = LBound(vCheckPoints) To UBound(vCheckPoints) Step 1
Set rng = Range(vCheckPoints(iCounter2))
If Not (IsEmpty(rng)) Then
'set copy area
Set Rng2 = Range(vCopyRange(iCounter2))
'Before copying find pasting point
Set Rng3 = Sheets("INV").Cells(65536, 1).End(xlUp).Offset(1, 0)
'Now copy to other sheet
With Rng2
.Copy Rng3
' .ClearContents

End With

'Items found and copied so get out of (inner)loop
Exit For
End If
Next
'Move on to next sheet
Next

' Now Clear Data Ranges
Dim ws As Worksheet, i As Long

For Each ws In Worksheets(Array("Cores", "NPN", "Est", "GOG", "Fact
Claim", "OS Claim", "Fact PS", "OS PS", "INV-NOT-REC", "Prepaid", "Sold
During"))

For i = 0 To 9
ws.Range("A1:L28").Offset(i * 30).ClearContents
Next i
Next
Sheets("INV").Select

End Sub


Thanks Very much,
--
Sam Fowler