Macro newbie - simple copy/paste function

[Guest]

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

 

[Guest]

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.