how to write a new macro

[Scott]

Hi there,

i already have a macro which grabs required date from a folder on my
machine, and places it into a spreadsheet called mydata, it also sorts the
list etc.

what i would like to do know is to open another document and run a macro to
grab data from mydata and place it into specific fields in a doc called
statement.

i can work the logic out of the macro but do not know where to start.

my thoughts on the macro are as follows

open the document called 'mydata' from the following directory
d:\data\mydata

check column 'A3' and if name contains 'sam' then do the following: (B3 is
the forst cell to contain useful data)

copy data from Cell 'E3' and place it into A24
copy data from Cell 'F3' and place it into B24
copy data from Cell 'G3' and place it into E24

when it has done the first lot of data then it checks the next cell A4 and
if it contains the same name then it will do the following

copy data from Cell 'E4' and place it into A24
copy data from Cell 'F4' and place it into B24
copy data from Cell 'G4' and place it into E24

then it will check A5 etc until cell 'Ax' doesn't contain the required data.

does this make sense to anyone?, if so any advice would be useful.

Regards

Scott

 

[Arvi Laanemets]

Hi

You don't need a macro for this - use links.

Some ways to create links:

1. Open the source workbook, and copy cell(s) you want to be linked.
Activate the target workbook, select the leftmost cell of range, you want to
put links into, and then PasteSpecial.Links. Close source workbook.

2. Open the source workbook. Activate Target workbook. Into target cell
enter '='. Activate source workbook and select the cell you want to be
linked. Press Enter. Close the source workbook.

3. Open the source workbook. Activate Target workbook. Into target cell
enter p.e.
=[MyFile.xls]Sheet1!A1
Close source workbook.

4. Into cell in target workbook enter a formula like
='C:\My Documents\[MyFile.xls]Sheet1'!A1

To avoid a 0 displayed when the source cell is empty
=IF('C:\My Documents\[MyFile.xls]Sheet1'!A1="","",'C:\My
Documents\[MyFile.xls]Sheet1'!A1)

To get the sum of range of cells in source workbook
=SUM('C:\My Documents\[MyFile.xls]Sheet1'!A1:A10)
etc.

 

[Scott]

Hi Arvi,

thanks for your reply, however i have another question, which is

If my sheet called My data which contains the data, changes i.e. the number
of cells which contain 'Sam' changes will that still work

Hi

You don't need a macro for this - use links.

Some ways to create links:

1. Open the source workbook, and copy cell(s) you want to be linked.
Activate the target workbook, select the leftmost cell of range, you want to
put links into, and then PasteSpecial.Links. Close source workbook.

2. Open the source workbook. Activate Target workbook. Into target cell
enter '='. Activate source workbook and select the cell you want to be
linked. Press Enter. Close the source workbook.

3. Open the source workbook. Activate Target workbook. Into target cell
enter p.e.
=[MyFile.xls]Sheet1!A1
Close source workbook.

4. Into cell in target workbook enter a formula like
='C:\My Documents\[MyFile.xls]Sheet1'!A1

To avoid a 0 displayed when the source cell is empty
=IF('C:\My Documents\[MyFile.xls]Sheet1'!A1="","",'C:\My
Documents\[MyFile.xls]Sheet1'!A1)

To get the sum of range of cells in source workbook
=SUM('C:\My Documents\[MyFile.xls]Sheet1'!A1:A10)
etc.

--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

Hi there,

i already have a macro which grabs required date from a folder on my
machine, and places it into a spreadsheet called mydata, it also sorts the
list etc.

what i would like to do know is to open another document and run a macro to
grab data from mydata and place it into specific fields in a doc called
statement.

i can work the logic out of the macro but do not know where to start.

my thoughts on the macro are as follows

open the document called 'mydata' from the following directory
d:\data\mydata

check column 'A3' and if name contains 'sam' then do the following:

(B3

is
the forst cell to contain useful data)

copy data from Cell 'E3' and place it into A24
copy data from Cell 'F3' and place it into B24
copy data from Cell 'G3' and place it into E24

when it has done the first lot of data then it checks the next cell A4 and
if it contains the same name then it will do the following

copy data from Cell 'E4' and place it into A24
copy data from Cell 'F4' and place it into B24
copy data from Cell 'G4' and place it into E24

then it will check A5 etc until cell 'Ax' doesn't contain the required data.

does this make sense to anyone?, if so any advice would be useful.

Regards

Scott

 

[Arvi Laanemets]

Hi

If my sheet called My data which contains the data, changes i.e. the number
of cells which contain 'Sam' changes will that still work

???

Please recast your question - I didn't catch it at all. An example will be
even better.

 

[Scott]

I have a spreadsheet call mydata which is populated by a macro which grabs
data from an 'x' number of spreadhseets in a folder.

the data in mydata is laid out as follows: (please note that all fields are
variables and can change)
A3 = name 'x'
b3 = contact
c3 = addy
d3 = susburb
e3 = date
f3 = number
g3 = amount

this continues until no more files can be found in the directory.

so the next line is as follows
A4 = name 'x'
b4 = contact
c4 = addy
d4 = susburb
e4 = date
f4 = number
g4 = amount

and then a5, a6, a7 etc etc etc

what i want to do is open another spreasheet and do the following:

If the name in Cell 'A" (irrespective of the number it could be a3 or a33 or
a1 or a51) matches the name of sam then i want it to copy the following
cells from 'mydata' (E 'x', F 'x', G 'x' ((X represents a number))) and
place that data into the following cells in the new sheet called statement
(eg A24 (for E3), B24 (for f3) & E24 (for G3)

I need this to happen so long as the name in cell a equals sam (or any other
name i choose)

if it finds there are 2 or more cells with the same name then it copies the
first line and then adds 1 to the count and then copies the next lot of data
to A25, then B25 etc and this will keep happening until it cannot find a
match in cell A'x'

i hope this makes sense

Thankyou so much for your help so far, i really appreciate it

Scott

 

[Arvi Laanemets]

Hi

What's 'Sam'? A named range? A cell reference?

You probably can do it using VLOOKUP() function anyway. Something like:
=IF(OR(ISERROR(VLOOKUP(Sam;'C:\My
Documents\[ContactList.xls]Contacts'!$A$3:$G$100;5;FALSE));Sam="");"";VLOOKU
P(Sam;'C:\My Documents\[ContactList.xls]Contacts'!$A$3:$G$100;5;FALSE))
to retrieve date etc.

 

[Arvi Laanemets]

You probably have to replace all semicolons in formula with commas

 

[Scott]

Hi Arvi,

sam is a random name, all the names in the Cell A'3' to A'x' are random in
both number of characters etc (i.e. it could be bob, sam, autosalon, superb
furniture etc)

i will copy & paste the formula and make the necessary changes.

Thanks Arvi

Scott

Hi

What's 'Sam'? A named range? A cell reference?

You probably can do it using VLOOKUP() function anyway. Something like:
=IF(OR(ISERROR(VLOOKUP(Sam;'C:\My
Documents\[ContactList.xls]Contacts'!$A$3:$G$100;5;FALSE));Sam="");"";VLOOKU
P(Sam;'C:\My Documents\[ContactList.xls]Contacts'!$A$3:$G$100;5;FALSE))
to retrieve date etc.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

I have a spreadsheet call mydata which is populated by a macro which grabs
data from an 'x' number of spreadhseets in a folder.

the data in mydata is laid out as follows: (please note that all fields are
variables and can change)
A3 = name 'x'
b3 = contact
c3 = addy
d3 = susburb
e3 = date
f3 = number
g3 = amount

this continues until no more files can be found in the directory.

so the next line is as follows
A4 = name 'x'
b4 = contact
c4 = addy
d4 = susburb
e4 = date
f4 = number
g4 = amount

and then a5, a6, a7 etc etc etc

what i want to do is open another spreasheet and do the following:

If the name in Cell 'A" (irrespective of the number it could be a3 or

a33

or
a1 or a51) matches the name of sam then i want it to copy the following
cells from 'mydata' (E 'x', F 'x', G 'x' ((X represents a number))) and
place that data into the following cells in the new sheet called statement
(eg A24 (for E3), B24 (for f3) & E24 (for G3)

I need this to happen so long as the name in cell a equals sam (or any other
name i choose)

if it finds there are 2 or more cells with the same name then it copies the
first line and then adds 1 to the count and then copies the next lot of data
to A25, then B25 etc and this will keep happening until it cannot find a
match in cell A'x'

i hope this makes sense

Thankyou so much for your help so far, i really appreciate it

Scott


will
be