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
259 Views

Similar Articles

[PageSpeed] 56

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:

Inserting charts into many workbooks
Hi, I'm using Excel 2007 and am pretty new to VBA. I have a folder with 40 or so workbooks- all with the same worksheet table format. The worksheets are huge (20 000 + rows, 50 or so columns). I need to open a workbook, make several fully formatted graphs, then insert the same graphs into every workbook , updated them with that workbooks' data. Any ideas on code for this? This isn't fully automated, but it will save some time. In one workbook, create the chart and format it to your specs. Then paste the chart into another workbook, and use the utility linked to i...

Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

Just installed new hard drive
I just installed a new hard drive and used the drive's utility program to copy everything from my old drive to the new one. Now Outlook isn't going out to my ISP's server and getting my new messages, and when I send a message I get the "no transport provider available" error. (My connection's just fine.) I looked online for some help. One recommendation was to delete the Personal Folder and create a new one. I did that, but then all the messages I had disappeared and it freaked me out so I didn't want to mess with anything else. How can I make it work ...

New emails with attachments not being delivered
When I send a NEW email with an attachment to my husband, it is NOT getting delivered, however, when I FORWARD an email with an attachment, it IS being delivered. I've tried sending different types of attachments (Word, JPG, Acrobat) and it doesn't seem to matter. None of them (as new messages) are delivered. I can send the same message to his home computer and it gets delivered, it's just his work Outlook that seems to be the problem. My email address is on his list of safe senders. He can receive emails with attachments from others, including his home compute...

Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...

how do i recover data in publisher
i have been entering addresses to set up a mail merge. i cllicked the "ok" button in the window and lost all data . can i recover it Look in a folder in My Documents named "My Data Sources". Publisher data is saved as .mdb(Access) file. Did you try selecting "Edit Address List" in the Mailings and Catalog menu (Tools)? -- Mary Sauer http://msauer.mvps.org/ "dee" <dee@discussions.microsoft.com> wrote in message news:690430F1-36DE-47EE-8B7D-DD12A096C075@microsoft.com... >i have been entering addresses to set up a mail merge. i cllicked ...

Cells Fill Automatically on Another Workbook
I've created what we'll call a seed worksheet to be used over and over for different clients. I have linked its cells to another workbook. As the originating seed worksheet directs its cell data to a specific cell on another workbook, how can I accomplish the workbook data not being overwritten but the new incoming data default to the next unused cell in the column? i.e. If the original seed worksheet cell B1 links to the worksheet cell A1, I would like the next instance of creating a new customer and his B1 information on his use of the seed worksheet to populate onto th...

Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...

How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...

Import directory data into Excel 2003
I have over 1000 media files that I would like to extract information from and put into an Excel spreadsheet. Using Explorer, I have defined the fields I would like to see, such as title, duration, comment etc. Now, I need to import this data into Excel. So far, I've not been able to find a way to do this. Can someone offer some suggestions please? Thanks, Nigel -- www.myoldcontacts.com - Tell your friends to tell their friends www.sysadmininc.com - Consultancy, Service, Sales, Networking... www.british-expats.com - Connect with British Expats World Wide www.kxez.com/shows_britishinv...

show last data point in chart
Hello, I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? Thank you. Nathan - > I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? < Click the charted data once to select the entire data series. Pause. Click the single point to select it. Then use the Format menu. - Mike www.mikemiddleton.com Thanks for your reply. Well, that would work if I knew which point on the chart ...

Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...

Import excel data to outlook calendar
I have found lots of tips to import excel data to the address book, etc, but can't find how to "custom map" or how to import data from an excel spreadsheet into the outlook calendar. Could anyone make any suggestions? Hi Tracy, normally you are in the wrong newsgoup, but I try to help you. - First export the dates from your OL calender to an excel file. - In this file, you can find all the headlines for importing. - If you try to import date, be sure that the headline matches as described before. - Then to the normal job for import in Outlook -- Ich hoffe, das hilft / ...

Registration Entry for External Data Refresh Prompt
Hello, I have several Excel Workbooks with external queries, pivots, etc. I have "ASK TO UPDATE AUTOMATIC LINKS" checked in TOOLS - OPTIONS. But it seems like I still stometimes get asked whether or not I want to update. Particularily I notice when I close the workbook I may get prompted if I want it to automatically update. Is there something I can do so I do not get prompted? Something in the registry perhaps? Thanks for any assistance! ...

Showing the perimeter of a set of (X,Y) data!
Good day all, I need to plot the perimeter of a set of data. I have a set of (X,Y) data with error bar and it is a nice mess so I just actually need to see (show) the area were the data can be found. Then hopefully overlay an other set of (X',Y') data and show that they both cover the same surface of existence. i.e this is a set of metrology measurement in X and Y of a part build from different mould. Obviously you get a nice cloud of X and Y but does the new material offer the same 'cloud' ? Thank you I think the easiest way to do this is plot the data on a XY Scatter cha...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Transfer automation
Hi all: I want to transfer the entire content of an entire excel book into a powerpoint presentation but I need to do this with an automated method (macro, batch file, etc). Does anybody have ideas on how can I do it? All your help will be greately appreciated. Thanks a lot. The easiest method could bo to insert an object into powerpoint using the insert>object method. Be sure to select the 'link' option. Changes to the spreadsheet will be atoumatically shown in powerpoint. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...

Error Exporting data in HQ Admin
Hi, I have HQ 1.2 (SP3) and two stores. I have previously exported new stores with other customers without any problems. However, I am now getting an error when it reaches the table supplier - An error was encountered while exporting data. Your Store Operations db is not complete and may not work correctly. Error 0: I have followed instructions as per another post ( query to check for NULL values and reinstall SO in case 'only me' option was selected) but still have same problem. Would anyone have any ideas on where to go from here? Thanks in advance. John. John, First I w...

Pivot table Data field question
I create a pivot table from three columns of data, using the Wizard.In the Layout dialogue, I drag one of my fields to the Rows; I drag anther field to the Columns. I need to drag a third field to the Data area. There's no conflict there, each Data cell of the resulting matrix would be unique. Excel doesn't let me do this. It insists on using a calculation, e.g. Sum of [field3], Count of [field3], etc. How do I convince Excel to insert just the value of Field3 in the Data? -- Regards Gershon Shamay The field you drag to the data area will always be summarized. However, if the v...

Data in Fields Changing Automatically
We use a simple Access table to keep track of resolutions adopted by our City Council. It's always worked just fine until today. When I add information in the fields of a row and click in the next row, the information I just added changes. There doesn't seem to be a consistent pattern to how this happens. There is a column for the number of the resolution. Sometimes this will change to the number of the row above, or two rows above. I tried rebooting. Thanks in advance for any clues! ?Version of Access? ?Working directly in the table instead of, as recommended, in a fo...