Moving home

  • Thread starter Thread starter Robert Couchman
  • Start date Start date
R

Robert Couchman

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
 
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
 
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
 
thank you for your advice but this is a slopy way of
programing!
Click to expand...
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.
Click to expand...
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
 
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
 
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
 
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.
 
Back
Top