Transferring data to new workbook.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shiro - 17 May 2008 09:58 GMT

Hi All,
I want to transferring a data automatically by using
vba code.But need soeone to point me the right
direction.What I want to do is something simple.
I want the code to run if it meet some condition.

If a cell in column F:F have value >1 and not empty (""),
I want the code to collect the entire data on every cell
on the left to be copied and paste into a new workbook.

And how to handle if there are some cell that have
value >1.How to copy the recordset and paste it into
a new workbook.And maybe I also need a message
box to tell the user that there is no data will be copied
if there is no cell in range F:F that contains value >1.

New workbook maybe need a header something like:
" Wrong data list ".

I have read automation transfer data to workbook
from MS Help and Support,but since I'm a new user,
I'm still cannot modified the code as I need.I'm still
need more simpler sample and lesson.

Thank's and Rgds,

Shiro.

Reply to this Message

Gary''s Student - 17 May 2008 12:29 GMT

This is a very good problem for using the Macro Recorder. Say we have two
workbooks open Book1.xks and Book2.xls

In Sheet1 of Book1 we have:

qewr    gd    bd    br    t    evv
-2    0    8    0    2    -8
5    -1    -2    0    -3    -10
6    1    8    2    8    4
-6    -4    8    6    4    -3
-9    2    -3    -10    3    2
7    6    5    -1    -2    1
9    -1    -7    10    4    -8
1    -9    -8    -7    -2    6
0    -10    9    7    -3    -5
4    -3    -3    7    -5    10
-9    -4    3    2    6    -6
-6    -6    -6    10    10    -6
-2    9    10    3    3    3
10    5    -5    6    -3    6
3    3    0    6    6    0
-10    -4    -7    0    -4    7
-9    10    4    10    -7    -3
1    -6    6    -8    -1    4
-9    6    -2    -9    -9    1
9    -5    -10    8    2    9
2    -9    -9    7    0    -8
8    1    -7    -10    -5    8
10    9    -7    7    5    5
0    1    4    7    5    -1
1    1    2    4    8    -8
8    -9    4    -2    -3    8
7    -9    -8    5    -2    8
1    -8    -6    -2    6    9
6    0    9    6    10    -10

To do the transfer manually, we click on F1 and:

Data > Filter > AutoFilter...
Custom > is greater than > 1

This displays:

qewr    gd    bd    br    t    evv
6    1    8    2    8    4
-9    2    -3    -10    3    2
1    -9    -8    -7    -2    6
4    -3    -3    7    -5    10
-2    9    10    3    3    3
10    5    -5    6    -3    6
-10    -4    -7    0    -4    7
1    -6    6    -8    -1    4
9    -5    -10    8    2    9
8    1    -7    -10    -5    8
10    9    -7    7    5    5
8    -9    4    -2    -3    8
7    -9    -8    5    -2    8
1    -8    -6    -2    6    9

and we would manually copy/paste to Book2.xls

Let's do this with the Recorder turned on:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/17/2008 by James Ravenswood
'

'
Range("F1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=">1", Operator:=xlAnd
Range("A1:F29").Select
Selection.Copy
Windows("Book2").Activate
Range("A1").Select
ActiveSheet.Paste
End Sub

The only manual issue we face is correctly adjusting the F29.

Signature

Gary''s Student - gsnu200786

> Hi All,
> I want to transferring a data automatically by using
[quoted text clipped - 23 lines]
>
> Shiro.

Reply to this Message

shiro - 17 May 2008 13:36 GMT

Sorry Gary,
not so understand.Please more guidance.
What about the cell value in the left?

> This is a very good problem for using the Macro Recorder. Say we have two
> workbooks open Book1.xks and Book2.xls
[quoted text clipped - 107 lines]
> >
> > Shiro.

Reply to this Message