copy range code

G

ganja

hello i need to do somthing like this


if(a1>0,copy range (a1:q1) to sheet1 (a1:q1)



how can i do this plz ?!

now days i make that formula for each cell
like
=if(a1>0,a1,) if(a1>0,b1,) ect....

thank
 
S

Simon Lloyd

Welcome to The Code Cage
A formula can only return a value from a cell it cannot copy anything
to another cell or range, what you want to acheive can only be done in
VBA (Visual Basic for Applications, Microsofts version of VB), if you
cannot use VBA in your workbook you will still have to do it by hand im
afraid!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
G

ganja

yeah that why i asked the code for this one
i should sort that way about 1000 raws
thats really can help m
 
M

Mike H

Hi,

You posted in programming so I gues you want a macro. Right click the sheet
tab where your source data is, view code and paste this in and run it.

Sub Stantive()
If IsNumeric(Range("A1").Value) And Range("A1").Value > 0 Then
Range("a1:Q1").Copy Destination:=Sheets("Sheet1").Range("A1")
End If
End Sub

Mike
 
G

ganja

thanks Mike that works
now how can i make it for all rows a1..a1000 for example ?!
like if(a1>0,copy range (a1:q1) to sheet1 (a1:q1)
if(a2>0,copy range (a2:q2) to sheet1 (a2:q2)
if(a3>0,copy range (a3:q3) to sheet1 (a3:q3)

and maybe the paste gonna be like "next empty raw in sheet1"

like if(a1>0,copy range (a1:q1) to sheet1 (next empty raw)

thanks again
Hi,

You posted in programming so I gues you want a macro. Right click th
sheet
tab where your source data is, view code and paste this in and run it.

Sub Stantive()
If IsNumeric(Range("A1").Value) And Range("A1").Value > 0 Then
Range("a1:Q1").Copy Destination:=Sheets("Sheet1").Range("A1")
End If
End Sub

Mike
Click to expand...
 
M

Mike H

Hi,

Try the code below. Note that I've now qualified the ranges and assumed your
source data are in sheet1 so change MySheet to suit

Sub Stantive()
MySheet = "Sheet1"
lastrow = Sheets(MySheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets(MySheet).Range("A1:A" & lastrow)
For Each c In MyRange
If IsNumeric(c.Value) And c.Value > 0 Then
Set c = c.Resize(, 17)
lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
c.Copy Destination:=Sheets("Sheet2").Range("A" & lastrow + 1)
End If
Next
End Sub

Mike

Mike

Sub Stantive()
lastrow = Sheets("Sheet1").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet1").Range("A1:A" & lastrow)
For Each c In MyRange
If IsNumeric(c.Value) And c.Value > 0 Then
Set c = c.Resize(, 17)
lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
c.Copy Destination:=Sheets("Sheet2").Range("A" & lastrow + 1)
End If
Next
End Sub
 
S

Simon Lloyd

Not tested but this should do it:

Code:
--------------------
Sub Stantive()
Dim i As Long
For i = 1 To Range("A").Rows.Count
If IsNumeric(Range("A" & i).Value) And Range("A" & i).Value > 0 Then
Range("A" & i & ":" & "Q" & i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next i
End Sub
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 
M

Mike H

Oops,

There's 2 lots of code there I pasted a version in and changed my mind and
forgot to delete. This is the correct version. It copies from sheet 1 to
sheet 2 so if that's incorrect change SrcSheet and dstSheet to the correct
names

Sub Stantive()
srcSheet = "Sheet1"
dstSheet = "Sheet2"
lastrow = Sheets(srcSheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets(srcSheet).Range("A1:A" & lastrow)
For Each c In MyRange
If IsNumeric(c.Value) And c.Value > 0 Then
Set c = c.Resize(, 17)
lastrow = Sheets(dstSheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
c.Copy Destination:=Sheets(dstSheet).Range("A" & lastrow + 1)
End If
Next
End Sub

Mike
 
G

ganja

thanks all
i thought it will be easy for me to understand how to change the cod
after i got it , but hell no :)
the colum i need to check is CP* > 0
and on the CP cell i have formula =IF(CO*>0[text],1,)
in that way i get result 1 if the CO* cell isnt empty (thats why
asked to code a>0)
if there is an option to make it shorter and make if CO* has somthin
written
then copy all the raw to for example sheet2 next empty raw


Simon Lloyd ur code has some Error

thanks again im happy i found this forum i'll take ur help many time
:
 
M

Mike H

Hi,

In posting a question its always a good idea to ask the question you want
the answer to in the first place.

I assume you have a formula like the in column CP you still haven't told us
which sheet this is so I assume sheet 1
=IF(CO1>0,1)
The will return a 1 if CO1 has text or a number in

This modified code now looks at column CP and copies the data to sheet 2

Sub Stantive()
srcSheet = "Sheet1"
dstSheet = "Sheet2"
lastrow = Sheets(srcSheet).Cells(Cells.Rows.Count, "CP").End(xlUp).Row
Set MyRange = Sheets(srcSheet).Range("CP1:CP" & lastrow)
For Each c In MyRange
If IsNumeric(c.Value) And c.Value > 0 Then
Set c = c.Resize(, 17)
lastrow = Sheets(dstSheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
c.Copy Destination:=Sheets(dstSheet).Range("A" & lastrow + 1)
End If
Next
End Sub


Mike
 
G

ganja

thanks mate , but i did the same change and its doesnt work
it copy only CP cell and not A:CP (i've change the 17(q) to 94(CP)

thats how it look here now

Sub Stantive()
srcSheet = "12.2008"
dstSheet = "תיקונים"
lastrow = Sheets(srcSheet).Cells(Cells.Rows.Count, "CP").End(xlUp).Row
Set MyRange = Sheets(srcSheet).Range("CP1:CP" & lastrow)
For Each c In MyRange
If IsNumeric(c.Value) And c.Value > 0 Then
Set c = c.Resize(, 94)
lastrow = Sheets(dstSheet).Cells(Cells.Rows.Count, "A").End(xlUp).Row
c.Copy Destination:=Sheets(dstSheet).Range("A" & lastrow + 1)
End If
Next
End Su
 
Top