I want to check rows From T2 across to AA2 down to T37 across to AA37
on worksheet sheet1! If any of those rows have one or more cells that
have a number greater than 0.then

I want to then copy and paste the entire one or more rows to workSheet
1A! into the row area of E10 across to L10 down to E19 to L19. However
if none of the cells in a row have a number greater than zero dont'
copy....

Thats it in a nutshell.

Hi Pano,

Using a helper column and AutoFilter should easily achieve what you want
without using macros.

I assume that range T1:AA1 are column headers. (If not then they need to be
for the following to work)

If column AB is already used then insert a new column for column AB (Can be
deleted later)

Insert a header in AB1 (Any name you like)
In AB2 insert =COUNTIF(T2:AA2,"0")
Copy formula down to AB37

Select range T1:AB37. (Ensure you include the column headers and new column
AB)

If using xl2007:
On Home ribbon select Sort & Filter in the Editing block far right of ribbon.
Click on Filter.

If using earlier version of xl:
Select menu item Data - Filter - Autofilter.

Click on the dropdown Autofilter arrow in column AB

If using xl2007:
Click on Number filters - Greater than - and then insert 0 in the box
beside €œis greater than€�
Click OK

If using earlier version of xl:
Click Custom
Click DropDown arrow under test.
Select Is greater than
Insert 0 in the box beside it.
Click OK.

Select all the visible data excluding Column AB
Select your other worksheet
Select the first cell only (cell E10) of the position to paste the data.
Paste the data.

Autofilter is a toggle like command. Turn it off by reselecting the
appropriate menu items.

--
Regards,

OssieMac


"pano" wrote:

I want to check rows From T2 across to AA2 down to T37 across to AA37
on worksheet sheet1! If any of those rows have one or more cells that
have a number greater than 0.then

I want to then copy and paste the entire one or more rows to workSheet
1A! into the row area of E10 across to L10 down to E19 to L19. However
if none of the cells in a row have a number greater than zero dont'
copy....

Thats it in a nutshell.

Hi OssieMac,

Thanks for the response.

Unfortunately this has to be a macro as the workbook is a daily
activity type running sheet that is based over a month and is used by
a number of different users so everything is locked down and
calculations are done on the fly behind the scenes with a daily print
out of activity's performed. Lots of calculations.

I'm only doing this as management changed the main sheet and altered
the way it was set up, so this one macro is holding me up as without
it I cant continue past the first day and there are another 90 plus
sheets to add in which are all linked with running totals etc
etc........

Thanks anyway
Pano

On Feb 10, 8:40*am, pano wrote:
Hi OssieMac,

Thanks for the response.

Unfortunately this has to be a macro as the workbook is a daily
activity type running sheet that is based over a month and is used by
a number of different users so everything is locked down and
calculations are done on the fly behind the scenes with a daily print
out of activity's performed. Lots of calculations.

I'm only doing this as management changed the main sheet and altered
the way it was set up, so this one macro is holding me up as without
it I cant continue past the first day and there are another 90 plus
sheets to add in which are all linked with running totals etc
etc........

Thanks anyway
Pano

Number of rows on sheet1 from T2 to T37 is 36.
Paste area in sheet1A from E10 to E19 is 10 only.
What happens when number of rows on sheet1 where cell value is 0 is
more then 10?

Here is a code that will paste all the rows where cell value is 0 on
sheet1A from E10 downwards.

Sub test()
Dim RVAL As Range
Sheets("SHEET1A").Select
Sheets("SHEET1A").Range("E10").Select
For I = 2 To 37
Set RVAL = Sheets("SHEET1").Range("T" & I & ":" & "AA" & I)
If Application.Sum(RVAL) 0 Then
Debug.Print "SUM OF RVAL = " & Application.Sum(RVAL)
RVAL.Copy
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
End If
Next
End Sub

Hope this works.
Regards,
Madiya.

Madiya

Well this works somewhat it actually copies over the formulas in the
cells not just the numbers that are in the cells a lot of #REF! in the
cells on Sheet 1A.

Do you have an idea how to fix this?

Very close though, thanks for your input..

By the way for your question there will never be more than 10 rows
needed on sheet 1A..


Regards
Pano

On Feb 10, 4:06*pm, pano wrote:
Madiya
Ok, got it did a bit of flinging thru the search function found paste
special, and guess what it works !!!!! YEA.!!!!!!!
Here is the amended code that works, lots of thanks to Madiya and
OssieMac for replying...

Sub testbaby()
Dim RVAL As Range
Sheets("1A").Select
Sheets("1A").Range("E10").Select
For I = 2 To 37
Set RVAL = Sheets("SHEET1").Range("W" & I & ":" & "AD" & I)
If Application.Sum(RVAL) 0 Then
Debug.Print "SUM OF RVAL = " & Application.Sum(RVAL)
RVAL.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
ActiveCell.Offset(1, 0).Select
Application.CutCopyMode = False
End If
Next
End Sub

Regards Pano