Transferring data to new workbook.

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.




0
shiro (16)
5/17/2008 8:58:05 AM
excel 39879 articles. 2 followers. Follow

2 Replies
276 Views

Similar Articles

[PageSpeed] 11

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.
-- 
Gary''s Student - gsnu200786


"shiro" wrote:

> 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.
> 
> 
> 
> 
> 
0
GarysStudent (1572)
5/17/2008 11:29:00 AM
Sorry Gary,
not so understand.Please more guidance.
What about the cell value in the left?


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:815C496A-B8AD-441D-98CF-59CD200B8CCB@microsoft.com...
> 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.
> --
> Gary''s Student - gsnu200786
>
>
> "shiro" wrote:
>
> > 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.
> >
> >
> >
> >
> >


0
shiro (16)
5/17/2008 12:36:26 PM
Reply:

Similar Artilces:

New S/MIME website
SMIME.org was created to promote email encryption usage, by providing help and references to email encryption products and standards. Although S/MIME standard is supported by most of the email clients, it is not widely used. The supplied information in SMIME.org will help all kind of users, from end-users to developers, to easily use and integrate S/MIME. http://www.smime.org ...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

No more new fonts may be applied to this workbook
Hello, I have an Excel Workbook with 6 worksheet containing a variety of charts linked to data sources in different workbooks. I receive the error message "No more new fonts may be applied to this workbook" when any font changes are attemped in the charts. I have tried the suggestion on the MS knowledge base (uncheck autoscale selection) with no success. I only receive the same error message. I am running Excel 2002. Any suggestions? Thanks for any help provided :-) Could you get by with fewer charts? Split them over multiple workbooks, perhaps, or use dynamic charts ...

How to import DATA from SQL2K to Access?
I am using get external data, import, ODBC type and it's error out saying I cannot use ODBC to import/export data. I would appreciate if someone please show me the better way of importing data from sql. Mehbs, Two ways. 1 - From SQL Server, use DTS to copy the data from the SQL Server table to the Access table. 2 - From Access, link your SQL Server table through ODBC and then use an append query to copy the data from the linked SQL Server table to your Access table. Good luck. -- Sco M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005 Denver Area Ac...

Outlook 2002: New Contacts & Events wont appear
Hi; I've been struggling to get Outlook 2002 to add new contacts. They are created, but the names won't appear. Or I get the error; Errors have been detected in the file ....outlook.pst. Quit all mail-enabled applications and then use the Inbox Repair Tool. I've run the SCANPST program a few times, no errors. Also when I try to add an event to the calendar, nothing appears. I have had these problems since Outlook 2000, which was the main reason I upgraded to Office XP Pro. But it hasn't solved the problem. I've been trying to reduce the size of the .PST by ...

transposting data
Hi I am having a terrible time trying to transpose horizontal data with vertical datsa. I have survey results with 288 questions and 35 respondents currently I have the respondents running horizontally with the questions running vertically; I am planning on importing the data into SPSS and need the question fields across the top and the respondents down the side. All help gratefully received Soozle Soozle Unfortunately the limitation of 256 collumns prevents you from transposing 288 rows into columns. If no greater than 256 rows you could select and copy the data then move to a ...

Porting class to new project
I developed a CDialog-based class in one project which I would like to move to another project. I copied the .h and .cpp files to the folder with the new project, included these file as existing items, and created some entries in the resource.h file to to correspond with the child items on my CDialog-class. All compiles fine but I get an error at run-time when I try to create my CDialog. It seems like this should be very easy so I assume I'm missing something obvious. Any suggestions? Thanks in advance, Bob Curry >>All compiles fine but I get an error at run-time when...

How to enter a long address to new web query
sup pros something like this http://tinyurl.com/e2n5e when i paste it , i get the message:- The text string you entered is too long. Reduce the number of characters used or cancel the entry -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4062 View this thread: http://www.excelforum.com/showthread.php?threadid=400401 You should be able to get rid of http:// Unless you are still using IE2 or 3 "Pivotrend" wrote: > > sup pros > something like th...

entering data in excel invoices
-- TomA --- PattiD .. >-----Original Message----- > >-- >TomA >. > ...

why do 5 workbooks open when I double click one
I double click on an Excel file and 4 or 5 files open simultaneously. you have workbooks stored in your XLStart folder, a hidden folder that you could put workbooks in it in order to open workbooks automatically once you open excel. to get rid of this problem, you have to turn on "show hidden fikes" in windows. to do this, double click on my documents --> go to tools,folder options, underf iles and folders, check "show hidden files and folders" radio button then click ok. go tothe path below, assuming C is the letter for your hard drive, and ofcourse User is your...

Auto-entering data from another sheet
Hello all, Hopefully someone can help me. I am wanting to automatically enter data from one sheet into the next by a text prompt. For example: On sheet 2 I will have 3 static columns that will have data entered in the format: text / text / number respectively. On sheet one I want to be able to enter the text from the first column on sheet two and have excel automatically fill in 2 other cells with the data from cell B and C from sheet 2. For example: Sheet 2 contains hello / how / 123 in row 1 and column A,B,C. I enter: hello (on sheet 1 cell A) and in cell B and C I automatically get...

New to Access 03-05-08
I am new to access and would like to design a database that will automatically email certain documents when the count down reaches a designated number. "carlos24mar" <carlos24mar@discussions.microsoft.com> schrieb im Newsbeitrag news:1FD00A4F-2FBB-4008-B8F8-44C05342B0FC@microsoft.com... > I am new to access and would like to design a database that will > automatically email certain documents when the count down reaches a > designated number. On Wed, 5 Mar 2008 09:00:01 -0800, carlos24mar <carlos24mar@discussions.microsoft.com> wrote: >I am new to acc...

Forms Toolbar vs. Control Toolbox vs. Data Validation for drop dow
BACKGROUND: I am trying to create a drop down box that will give the user an option to select a year. From that year selected, I will then use the output with the OFFSET formula to go grab data from another tab. QUESTIONS: 1.For a drop down menu, when should I use the Forms toolbar options vs. when do I use the Control Toolbox options vs. when should I use Data Validation to create my drop down menu? 2.In general, when would I use the Forms toolbar and when should I be using the Control Toolbox? They seem similar. Thanks in advance for your help. -Scott There is no reason you ...

Chart Format generated from a Pivot Table Data
After I Format a Chart generated from a Pivot table Data, and I change a selection, for example "Plant".. Al the information on the chart changes, according to the new plant selection, but, why the Chart Type and the format also changes too... I need to keep the same chart type and format to keep reviewing the information for the rest of the plants.... with the same chart type and format. Microsoft admits they goofed that one up: XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/?id=215904 They suggest you record a macro, so you can r...

Is there a limit to the number of points in a chart source data
I'm trying to put in a value range in a chart. The cells are every other cell. When I start selecting the cells I can only click on 10 cells. On number 11 I get a bell sound and the value source data block goes back to displaying only a =. Any Ideas would be greatly appreciated. Thx Hi, There is a limit to the number of data points, 32k, but that is not the limit you are hitting. The series formula has a limit. The series formula looks something like this. This is for a chart based on data in B2:B5 with axis labels in A2:A5 and series name in B1. =SERIES(Sheet1!$B$1,Sheet1!$A$...

importing address OE book to new computer
Hello, I'm having trouble importing my OE address book to my new computer. When I export from the old computer as a *.csv file it only saves the idenities contacts, but not the subfolders. As that didn't work I tried a straight cut and paste of the *.wab file. However, an error occurs when I cut and paste the associated *.wab file to the new computer and then attempt to import to OE. Can anyone help me? Cameron Hi Cameron, Please take look at Move contacts to another computer http://office.microsoft.com/assistance/preview.aspx?AssetID=HA011053931033&C TT=1&Origi...

Adding a sheet to a workbook
I have a Development Notes template that I want to add to the front of larger models that I build. Having saved the template as a file, I have code that is run from an icon that should - identify the activeworkbook - open the template file - copy the notes sheet into the activeworkbook - close the template file It keeps failing on the second of these two lines, with a 'Subscript out of range' error cSheet = Workbooks(cMainWin).Sheets(1).Name Workbooks(cNotesFile).Worksheets(cNotesFile).Copy Before:=Workbooks(cMainWin).Sheets(cSh...

Transfer into budget account
When I make a transfer from an IRA account into a bank checking account how do I set it up to be shown as a "Transfer into budget account" as an IRA Distribution so that it appears as income in my Budget? The transfer into budget account part should be easy. Either add it to the budget explicitly or schedule a transfer and make sure the IRA cash account is set as not in budget. (Sadly you cannot schedule the investment sale/transfer.) Making it appear as an "IRA distribution" is harder as I don't know what you mean. Making it appear as income is the hardest. It'...

Feeding Worksheet Data Into a Query
If I have 4 values that reside in a worksheet in cells F1:F4, how do I take these values and specify these as criteria in my where clause: WHERE ID IN (F1:F4) I need a comma separated list of IDs so that the T-SQL statement can be executed correctly, WHERE ID IN (F1,F2,F3,F4) There's always more than 1 way to skin a cat ... One way to solve your problem would be to dim 5 variables. Example: (I am going to assume your values are string values.) Dim strA As String, strB As String, strC As String, D As String Dim strWhereSQL As String strA = Range("F1").Value strB = Range(&...

Single Data Point on a new Series
I am starting a new Series of data points and have only one data point and it is not appearing on a line graph If there's only one point, there is no line. You need to use the Line with Markers chart subtype. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ bocjoel wrote: > I am starting a new Series of data points and have only one data point and it > is not appearing on a line graph ...

Remove Space on Bar Charts for Missing Data
I am trying to create a bar chart that shows student achievement results across a number of years. For some of the schools, I do not have data in certain years. I do not want the chart to leave a big space for this missing data - I just want it to display the data that I have without leaving this huge gap for the empty cells in the table it is linked to. I tried putting =NA() in the empty cells but the chart is still leaving gaps. Is there any way to have the chart wizard ignore the empty spaces so that it will just display the data that I have? Thanks! EXCEL 2007 I hop...

sorting and getting calculations in other columns use new rows
I have got a worksheet where column A is the date, but not in order. Other columns have finance information, and contain calculations using data from other cells. eg A12=10/10/2005 B12=C25*1.175 If I now sort on column A (the date column) I find that B12 refers to some other cell in column C, whereas I want it to take the value from where C25 has now moved to. What am I doing wrong? (hope this makes sense) Thanks Rod Hi You need to mark your whole range of data before choosing Sort, then the other data will move in line with the sorted column(s) Regards Roger Govier reades wrot...

Formatting Data the smart way
Hi I have 3 columns and lets name it as A,B,C and 6 rows. I am trying to pull all those data in the 6 rows to the first row. I still want to keep the data in the same format as they were in seperate rows and seperate colums but I want them in one row a b c abc 23 2 abc 3 54 abc 78 67 I am trying to figure out a way in which i can have a b c 23 2 abc 3 54 78 67 I want t...

how do I chart something if my workbook is "shared" ?
-- TG You can't create a chart in a shared workbook. Unshare the workbook, create the chart, then turn sharing back on. tgc-inc wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks, Debra! -- TG "Debra Dalgleish" wrote: > You can't create a chart in a shared workbook. Unshare the workbook, > create the chart, then turn sharing back on. > > tgc-inc wrote: > > -- > Debra Dalgleish > Excel FAQ, Tips & Book List > http://www.contextures.com/tiptech.html > > ...

Transfer on Electronic Bank Reconciliation
My customer Corporación Quiport suggest to include in future versions of Dynamics Great Plains, the chance to conciliate Transfers made on Great Plains. We can only conciliate Deposits, Checks, Debit memos or credit memos, but we cannot conciliate Transfers from Bank transactions. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based...