Macro newbie - simple copy/paste function

[Nat]

Hi,

I have absolutely no knowledge of macro programming and am usually able to
find how to do things with the Office help but this time: not!

Here is what I would like to do:
I created an excel form (not using forms functions but simply locking
cells). I send this form to be filled-out and I would like to compile it
automatically in another xls.

I need a function that would copy cell C2 from "survey.xls" and paste (w/o
format) to the next empty line of row A in "results.xls" and so forth and so
on.

Can anyone point me in the right direction please? I am at a lost here.

Also, can someone suggest a simple book for a macro-newbie like me?

Thank you.

[Graham Whitehead]

Hopefully this should get you started:

Sub test()

Dim x As Long
Dim ValueC2 As Variant
'get value from cell C2
Windows("survey.xls").Activate
ValueC2 = Range("C2").Value
'activate workbook to put value into
Windows("results.xls").Activate
'find next empty row
With ActiveSheet
x = .Range("A65536").End(xlUp).Row
End With
'put value in
Range("A" & x).Value = ValueC2

End Sub

"Nat" <Nat@discussions.microsoft.com> wrote in message
news:558B328A-F87A-43F8-B50A-30F353118E75@microsoft.com...
> Hi,
>
> I have absolutely no knowledge of macro programming and am usually able to
> find how to do things with the Office help but this time: not!
>
> Here is what I would like to do:
> I created an excel form (not using forms functions but simply locking
> cells). I send this form to be filled-out and I would like to compile it
> automatically in another xls.
>
> I need a function that would copy cell C2 from "survey.xls" and paste (w/o
> format) to the next empty line of row A in "results.xls" and so forth and
> so
> on.
>
> Can anyone point me in the right direction please? I am at a lost here.
>
> Also, can someone suggest a simple book for a macro-newbie like me?
>
> Thank you.

[Tom Ogilvy]

Sub MacroTocopy()
Dim shResults as Worksheet, Dim shSurvey as Worksheet
Dim icol as Long, rw as Long, cell as Range
Dim rng as Range
set shResults = Workbooks("Results.xls").worksheets(1)
set shSurvey = workbooks("Survey.xls").Worksheets(1)
set rng = shSurvey.Range("C2,C7,C12,C20,D30,F40")
icol = 1
set rw = shResults.Cells(rows.count,1).End(xlup)(2).row
for each cell in rng
shResults.Cells(rw,icol).Value = cell.Value
icol = icol + 1
Next
End sub

However, it seems like you would want to process many different workbooks
with the macro and they all woudn't be named Survey.xls. If so, then change

shSurvey = Activesheet

and run the macro with the sheet to be processed as the activesheet.

--
Regards,
Tom Ogilvy


"Nat" wrote:

> Hi,
>
> I have absolutely no knowledge of macro programming and am usually able to
> find how to do things with the Office help but this time: not!
>
> Here is what I would like to do:
> I created an excel form (not using forms functions but simply locking
> cells). I send this form to be filled-out and I would like to compile it
> automatically in another xls.
>
> I need a function that would copy cell C2 from "survey.xls" and paste (w/o
> format) to the next empty line of row A in "results.xls" and so forth and so
> on.
>
> Can anyone point me in the right direction please? I am at a lost here.
>
> Also, can someone suggest a simple book for a macro-newbie like me?
>
> Thank you.