passing data from one sheet to another

  • Thread starter Thread starter billabong
  • Start date Start date
B

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
 
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
 
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.
 
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
 
Back
Top