Automate worksheet copy

Hi,

I have a worksheet with several columns and in 1 column there is a number
from 0 to 10. I would like to copy "automatic" all rows from this worksheet
to another worksheet but only if the number is different from 0. This is not
a one-time operation but the numbers can dynamically change, they are formula
based. I have not found a solution yet. Has anyone experience with this ?

Marco.

One way ..

Assume the source table is in Sheet1, cols A to C, data from row2 down, with
the key column being col C

Use an empty col to the right, say col E

Put in E2: =IF(C2="","",IF(C2<>0,ROW(),""))

Copy E2 down to say, E100, to cover the max expected data range in the
source table

(Leave E1 empty)

In Sheet2
-------
Put in A2:

=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A2 across to C2, fill down to C100
(cover the same range size as in col E in Sheet1)

Sheet2 will return the desired results from Sheet1, all neatly bunched at
the top

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Marco" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a worksheet with several columns and in 1 column there is a number
> from 0 to 10. I would like to copy "automatic" all rows from this
worksheet
> to another worksheet but only if the number is different from 0. This is
not
> a one-time operation but the numbers can dynamically change, they are
formula
> based. I have not found a solution yet. Has anyone experience with this ?
>
> Marco.

"Marco" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a worksheet with several columns and in 1 column there is a number
> from 0 to 10. I would like to copy "automatic" all rows from this
worksheet
> to another worksheet but only if the number is different from 0. This is
not
> a one-time operation but the numbers can dynamically change, they are
formula
> based. I have not found a solution yet. Has anyone experience with this ?
>
> Marco.

Here's one way:

'copy input data to a temp worksheet then switch to it for maniupation in
case anything goes wrong
Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "temp"

'add desitnation sheets for the matching data
Sheets.Add.Name = "Matched"
sheets("Matched").Range("A1:G1").Value =
Sheets("Temp").Range("A1:G1").Value 'copy title row

'any processing you do prior to seperating the matches needs completed here

'sort matching data to destination sheets
'change column(G) to your longest data column
Sheets("temp").Activate
For Each cell In Range("G1:G" & Range("G65536").End(xlUp).Row)
if cell.value > 0 then cell.EntireRow.Cut
Sheets("Matched").Range("A65536").End(xlUp).Offset(1, 0)
Next

'presuming you want to remove the temp worksheet once data is seperated,
without bothering the user
Application.DisplayAlerts = False
Sheets("temp").Delete
Application.DisplayAlerts = True

'any followup processing goes here
end sub

Hi,

I tried this and I get a compile error at this line, it concerns the Offset

>> Sheets("Matched").Range("A65536").End(xlUp).Offset(1, 0)

Error message: "= expected"

I have no excell programming experience...

How can I change this to copy only a few columns:
> if cell.value > 0 then cell.EntireRow.Cut

Thanks.

Marco.


"Jef Gorbach" wrote:

>
> "Marco" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have a worksheet with several columns and in 1 column there is a number
> > from 0 to 10. I would like to copy "automatic" all rows from this
> worksheet
> > to another worksheet but only if the number is different from 0. This is
> not
> > a one-time operation but the numbers can dynamically change, they are
> formula
> > based. I have not found a solution yet. Has anyone experience with this ?
> >
> > Marco.
>
> Here's one way:
>
> 'copy input data to a temp worksheet then switch to it for maniupation in
> case anything goes wrong
> Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
> Sheets(Sheets.Count).Name = "temp"
>
> 'add desitnation sheets for the matching data
> Sheets.Add.Name = "Matched"
> sheets("Matched").Range("A1:G1").Value =
> Sheets("Temp").Range("A1:G1").Value 'copy title row
>
> 'any processing you do prior to seperating the matches needs completed here
>
> 'sort matching data to destination sheets
> 'change column(G) to your longest data column
> Sheets("temp").Activate
> For Each cell In Range("G1:G" & Range("G65536").End(xlUp).Row)
> if cell.value > 0 then cell.EntireRow.Cut
> Sheets("Matched").Range("A65536").End(xlUp).Offset(1, 0)
> Next
>
> 'presuming you want to remove the temp worksheet once data is seperated,
> without bothering the user
> Application.DisplayAlerts = False
> Sheets("temp").Delete
> Application.DisplayAlerts = True
>
> 'any followup processing goes here
> end sub
>
>
>
>

... while waiting for Jef to respond further ..

Perhaps you could drop me a line or 2 here what was it about the non-array
formulas approach suggested in my response which failed to meet the specs
you posted ? Or, which part of the response was not up to your expectations
? Just wanted a closure to this exchange of views before the thread fades
away .. Thanks.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

Max,

I have tried your solution also and it works fine. I am automating a sales
process and I am trying to find the best solution. The idea is to prepare 2
worksheets, "Hit the button" and create a new worksheet with all data in the
format I like. I think working with the VB gives more flexibility: I would
like to have some blank rows in between some data, change formatting... etc..

Thanks for your post!

Marco.

"Max" wrote:

> ... while waiting for Jef to respond further ..
>
> Perhaps you could drop me a line or 2 here what was it about the non-array
> formulas approach suggested in my response which failed to meet the specs
> you posted ? Or, which part of the response was not up to your expectations
> ? Just wanted a closure to this exchange of views before the thread fades
> away .. Thanks.
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
>
>
>

Thanks for the feedback, Marco

Ok, I've put together a sample file (link to download below) where there's a
clickable commandbutton on Sheet2 which will make a static copy of Sheet2 on
another sheet. And you could then make further use of this copy (no
formulas). Play around and see whether this helps you progress a little
further.

FWIW, I've also pasted in a working version of Jef's sub in a regular
module, with line-breaks corrected, so you can also tinker with that if you
want, while waiting for Jef or any others who might respond further in this
thread. (My knowledge of vba is not sufficient to help you edit Jef's sub to
suit, I'm afraid)

Link to sample file:
http://www.savefile.com/files/5220377
File: Marco_misc.xls

The commandbutton sub attached is:

Private Sub CommandButton1_Click()
' Makes a static* copy of the activesheet
' *Only values and formats are copied [no formulas]

Application.ScreenUpdating = False
ActiveSheet.Select
Cells.Select
Selection.Copy

Sheets.Add
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlFormats

Sheets("Sheet2").Select 'Amend sheetname to suit
Range("A1").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Marco" <[email protected]> wrote in message
news:[email protected]...
> Max,
>
> I have tried your solution also and it works fine. I am automating a sales
> process and I am trying to find the best solution. The idea is to prepare
2
> worksheets, "Hit the button" and create a new worksheet with all data in
the
> format I like. I think working with the VB gives more flexibility: I would
> like to have some blank rows in between some data, change formatting...
etc..
>
> Thanks for your post!
>
> Marco.