passing data from one sheet to another

[billabong]

Hello

I have data in a1 and a2 of sheet 1
how would you pass that data to a single cell in sheet 2 a1

Thanks

 

[Thomas]

in sheet2 A1:
=sheet1!a1&sheet1!a2

 

[billabong]

How would you place this in vb editor with a space

Have tried putting & " " & in between but did not work
here is my code:

Private Sub CommandButton1_Click()

Sheets("Sheet2").Select
ActiveCell = "=Sheet1!A1 & Sheet1!A2"

End Sub

I will be using a for loop to bring data from one sheet to the other
as the user inputs. How would I include iteration variable "i" in
Sheet1!A1


Thanks for your answers

 

[J.E. McGimpsey]

One way:

Sheets("Sheet2").Range("A1").Value = _
Sheets("Sheet1").Range("A1") & " " & _
Sheets("Sheet1").Range("A2")

which can be shortened a bit:

With Sheets("Sheet1")
Sheets("Sheet2").Range("A1").Value = _
.Range("A1").Value & " " & .Range("A2").Value
End With

or even

With Sheets("Sheet1").Range("A1")
Sheets("Sheet2").Range("A1").Value = _
.Value & " " & .Offset(1, 0).Value
End With

Note that you almost never need to select or activate a range in
order to work with it. Using the range object directly makes your
code smaller, faster and, IMO, easier to maintain.

 

[Tom Ogilvy]

Private Sub CommandButton1_Click()

Sheets("Sheet2").Select
ActiveCell.Resize(1,10) = "=Sheet1!A1 & "" "" & Sheet1!A2"

End Sub

You would need a more complex formula if you want to go down the column on
sheet2 and each cell would reference two cells down in sheet1.

Regards,
Tom Ogilvy