paste/copy help

A

anthony warburton

Ok, I wish to use a macro button to collect the data in a
cell on one worksheet and paste it into a row in another
worksheet.
Each time the macro button is pressed the data is taken
and placed into the next row down in the other worksheet,
thus leaving a complete record of all the data that has
been collected from the first worksheet using the macro.

How can this be done????
feel free to e-mail me with help to
[email protected]
many thanks
 
F

Frank Kabel

Hi
try the following (copys cell A1 from sheet1 to column A of sheet 2):
sub foo()
dim wks1 as worksheet
dim wks2 as worksheet
dim lastrow as long

set wks1=worksheets("sheet1")
set wks2=worksheets("sheet2")
lastrow = wks2.Cells(Rows.Count, 1).End(xlUp).Row

wks2.cells(lastrow+1,1).value=wks1.cells(1,1)
end sub
 
D

Dave Peterson

I put a button from the Forms toolbar on a worksheet. I assigned it this macro:

Option Explicit
Sub testme()
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim toCell As Range

Set fromWks = ActiveSheet

Set toWks = Worksheets("sheet2")

With toWks
Set toCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

fromWks.Range("a3").EntireRow.Copy _
Destination:=toCell

End Sub

Change the toWks to the correct name.
I used column A to determine the next row on sheet2.
(and I copied row 3)
 
D

Dave Peterson

This was exchanged in private message:

Option Explicit
Sub testme()
Dim fromWks As Worksheet
Dim toWks As Worksheet
Dim toCell As Range

Set fromWks = ActiveSheet

Set toWks = Worksheets("sheet2")

With toWks
Set toCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With fromWks
toCell.Value = .Range("A1").Value
toCell.Offset(0, 1).Value = .Range("C10").Value
toCell.Offset(0, 2).Value = .Range("D7").Value
End With

End Sub

(but I'm not sure it's what Frank really wanted.)
 
S

SidBord

Sorry, but I don't get a clear picture of what you are
doing. I do know that you must have the cell to be copied
selected before pressing that button. That suggests that
you have to manually click it with your cursor, because
just entering a data value will cause the cursor to jump to
the right (or down 1, depending on how your options are
set), thereby selecting that cell. You lost me when you
said that the macro will write the value on the "next row
down" on the next sheet. Down from what? Does the macro
somehow know the address of the last entry it posted? It
IS possible to define a macro location that keeps a value
after the macro terminates execution (but I can't remember
how). Defining a variable as PUBLIC ahead of all other
macros on the module, I think . . . maybe.
Another thing you can do if you want the macro triggered
automatically when you make an entry into a cell is write
an event handling macro. It's too complicated to explain
here, but well worth learning about.
 
D

Dave Peterson

In another private email, Anthony said that he had enough to get started and
could make things do what he wants.
 
Top