Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
Hello,
here is a troublesum one, i am looking for a piece of code that will look down column G and if it finds a value in the cell (in this case it will be a date), then it will copy the entire row and paste it into sheet 2, then continue down sheet 1 till it has reached the bottom. any help anyone? thank you, Robert Couchman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
Hi,
use a Filter an Colum G set to "non blank cells", copy the whole range and insert it in sheet 2. You can record a macro to see what's required for VBA. arno "Robert Couchman" schrieb im Newsbeitrag ... Hello, here is a troublesum one, i am looking for a piece of code that will look down column G and if it finds a value in the cell (in this case it will be a date), then it will copy the entire row and paste it into sheet 2, then continue down sheet 1 till it has reached the bottom. any help anyone? thank you, Robert Couchman |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
Hi,
thank you for your advice but this is a slopy way of programing! what i would like is a strict piece of program that will search the correct field and if it returns the correct value, then it will select the entire row and copy all the contents into sheet 2. if this is not possible or nobody has a tidyer way of doing this please can someone consider only copying specific data as this is only to be used as a mail merge. i have considered using the offset command and the if not function. any suggestions please, Thank you, Robert Couchman |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
thank you for your advice but this is a slopy way of
programing! your data and copying from one sheet to another is slopy. my suggestion is _fast_. another way would be using ADO and an sql-statement based on your sheet1, however, in this case selecting by sql is the same as using the filter (a condition on column G). Another way would be to import the data into excel already in the correct format, eg. with ADO+sql, MS-query etc. what i would like is a strict piece of program that will search the correct field and if it returns the correct value, then it will select the entire row and copy all the contents into sheet 2. you can loop throug the whole column and eg. delete empty (not wanted) rows. this will take ages if you have lots of records. arno |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
Not really "a slopy (slopPy) way of programing!"
To get more than a couple of dates this is a GOOD way. You can also use FIND next. See the example in vbe HELP. To find just one then best to just use FIND -- Don Guillett SalesAid Software "Robert Couchman" wrote in message ... Hi, thank you for your advice but this is a slopy way of programing! what i would like is a strict piece of program that will search the correct field and if it returns the correct value, then it will select the entire row and copy all the contents into sheet 2. if this is not possible or nobody has a tidyer way of doing this please can someone consider only copying specific data as this is only to be used as a mail merge. i have considered using the offset command and the if not function. any suggestions please, Thank you, Robert Couchman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
Thank you for correcting my spelling, but the information
MS macro puts into the macro is often the users way of selecting and copying data! i am after a systematic way! i will be dealing with anything upto 600 records and over half the records may have passed the first stage. therefore i need the system to only copy the files with a pass mark (that can be altered on a different part of the program) wich is high enough to get a congratulations letter. thank you, Robert Couchman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving home
In general, what you say is true. But using the autofilter is a single
command with multiple arguments. The easiest way to get the right syntax is to turn on the macro recorder, apply the filter and set the conditions. Once the data is filtered (turn off the recorder), then the next line of code would be set rng = Activesheet.Autofiter.Range rng.copy Destination:=worksheets("Whatever").Range("A1") ActiveSheet.Autofilter ' turns off the filter Only the visible rows, the ones you want, will be copied. Using the macro recorder to build complex commands is smart. Another method would be to use the advanced filter, which has an option to copy the results of the filter to another location. This requires a criteria range, however and is a little more complex (but not much). Again, using the macro recorder is a good first step. -- Regards, Tom Ogilvy "Robert Couchman" wrote in message ... Thank you for correcting my spelling, but the information MS macro puts into the macro is often the users way of selecting and copying data! i am after a systematic way! i will be dealing with anything upto 600 records and over half the records may have passed the first stage. therefore i need the system to only copy the files with a pass mark (that can be altered on a different part of the program) wich is high enough to get a congratulations letter. thank you, Robert Couchman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set home to A1 instead of K1? | Excel Discussion (Misc queries) | |||
Moving text around cells without moving boarder lines | Excel Discussion (Misc queries) | |||
Arrow Keys Moving Window Frame instead of Moving Between Cells | Excel Discussion (Misc queries) | |||
Home key | Excel Discussion (Misc queries) | |||
Shortcut keys: CNTRL+HOME vs. HOME | Excel Discussion (Misc queries) |