What am I missing here? (VBA Code)

[goldenvision]

Surely this shouldn't be so complicated? I am missing something obvious here. I just don't know what it is.

I am trying to copy a value from cell A1 in sheet From and paste it into the next available empty cell in column B of sheet To

Private Sub CommandButton1_Click()
Dim rownumber As Integer

Sheets("From").Select
Range("A1").Select
Selection.Copy

Sheets("To").Select
rownumber = 1
Do Until Cells(rownumber, 2) = ""
MsgBox rownumber

Range("B" & rownumber).Select
Selection.Paste
rownumber = rownumber + 1
Loop
End Sub

help

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt f8)

x = Worksheets("sheet2").Cells(Rows.Count, 2).End(xlUp).Row
Worksheets("sheet2").Cells(x + 1, 2) = Worksheets("sheet1").Cells(1, 1)

Run the macro. It will add cell A1 of sheet1 to col B of sheet 2
Ravi

 

[goldenvision]

i can see how that solution will work. go to the last cell and move up until you hit a blank. i really want to know why the solution i am trying to implement won't work.

 

[Andrew Poulsom]

You need to put your Paste otside the loop:

Private Sub CommandButton1_Click() 
   Dim rownumber As Integer 
   Sheets("From").Select 
   Range("A1").Select 
   Selection.Copy 
   Sheets("To").Select 
   rownumber = 1 
   Do Until Cells(rownumber, 2) = "" 
      MsgBox rownumber 
      rownumber = rownumber + 1 
   Loop 
   Range("B" & rownumber).Select 
   Selection.Paste 
End Sub

 

[goldenvision]

You need to put your Paste otside the loop:

Private Sub CommandButton1_Click() 
   Dim rownumber As Integer 
   Sheets("From").Select 
   Range("A1").Select 
   Selection.Copy 
   Sheets("To").Select 
   rownumber = 1 
   Do Until Cells(rownumber, 2) = "" 
      MsgBox rownumber 
      rownumber = rownumber + 1 
   Loop 
   Range("B" & rownumber).Select 
   Selection.Paste 
End Sub

As that well known philosopher Homer J Simpson once said "D'Oh!"

 

[goldenvision]

Private Sub CommandButton1_Click()
Dim rownumber As Integer
Sheets("From").Select
Range("A1").Select
Selection.Copy
Sheets("To").Select
rownumber = 1
Do Until Cells(rownumber, 2) = ""
' MsgBox rownumber
rownumber = rownumber + 1
Loop
Cells(rownumber, 2).Select Falls over here
Selection.Paste
End Sub

 

[DiscoPistol]

Try This -

Private Sub CommandButton1_Click()
Dim rownumber As Integer
Sheets("From").Select
Range("A1").Select
Selection.Copy
Sheets("To").Select
rownumber = 1
Do Until Sheets("To").Cells(rownumber, 2) = ""
' MsgBox rownumber
rownumber = rownumber + 1
Loop
Sheets("To").Cells(rownumber, 2).Select
ActiveSheet.Paste
End Sub

DP

 

[goldenvision]

That's done the trick. Cheers. What was wrong with it?

 

[DiscoPistol]

You know what, it beats me

All I know is that sometimes if you want to select a Cell you need to specify the Sheet as well.

Maybe someone can enlighten us as to why!

 

[Norie]

You really don't need to use Select.

And I don't see why you need a loop.

Private Sub CommandButton1_Click()

Sheets("From").Range("A1").Copy Sheets("To").Range("B" & Rows.Count).End(xlUp).Offset(1)

End Sub