ExcelBanter - copy range code

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - copy range code (https://www.excelbanter.com/excel-programming/420974-copy-range-code.html)

hello i need to do somthing like this

if(a10,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(a10,a1,) if(a10,b1,) ect....

thanks

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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

"ganja" wrote:

hello i need to do somthing like this

if(a10,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(a10,a1,) if(a10,b1,) ect....

thanks

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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

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

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

thanks again

Mike H;137846 Wrote:
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

"ganja" wrote:

hello i need to do somthing like this

if(a10,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(a10,a1,) if(a10,b1,) ect....

thanks

--
ganja

------------------------------------------------------------------------
ganja's Profile: 'The Code Cage Forums - View Profile: ganja'
(http://www.thecodecage.com/forumz/member.php?userid=54)
View this thread: 'copy range code - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=38215)

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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

"ganja" wrote:

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

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

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

thanks again

Mike H;137846 Wrote:
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

"ganja" wrote:

hello i need to do somthing like this

if(a10,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(a10,a1,) if(a10,b1,) ect....

thanks

--
ganja

------------------------------------------------------------------------
ganja's Profile: 'The Code Cage Forums - View Profile: ganja'
(http://www.thecodecage.com/forumz/member.php?userid=54)
View this thread: 'copy range code - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=38215)

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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

"Mike H" wrote:

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

"ganja" wrote:

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

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

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

thanks again

Mike H;137846 Wrote:
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

"ganja" wrote:

hello i need to do somthing like this

if(a10,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(a10,a1,) if(a10,b1,) ect....

thanks

--
ganja

------------------------------------------------------------------------
ganja's Profile: 'The Code Cage Forums - View Profile: ganja'
(http://www.thecodecage.com/forumz/member.php?userid=54)
View this thread: 'copy range code - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=38215)

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

thanks all
i thought it will be easy for me to understand how to change the code
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 i
asked to code a0)
if there is an option to make it shorter and make if CO* has somthing
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 times
:)

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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(CO10,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

"ganja" wrote:

thanks all
i thought it will be easy for me to understand how to change the code
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 i
asked to code a0)
if there is an option to make it shorter and make if CO* has somthing
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 times
:)

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215

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 Sub

--
ganja
------------------------------------------------------------------------
ganja's Profile: http://www.thecodecage.com/forumz/member.php?userid=54
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=38215