What am I missing here? (VBA Code)

goldenvision

Board Regular
Joined
Jan 13, 2004
Messages
234
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

Code:
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
Paste the following codes in the macro window ( Alt f8)

Code:
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

Board Regular
Joined
Jan 13, 2004
Messages
234
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You need to put your Paste otside the loop:

Code:
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

Board Regular
Joined
Jan 13, 2004
Messages
234
You need to put your Paste otside the loop:

Code:
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

Board Regular
Joined
Jan 13, 2004
Messages
234
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

Active Member
Joined
Jun 6, 2006
Messages
261
Try This -

Code:
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
 

DiscoPistol

Active Member
Joined
Jun 6, 2006
Messages
261
You know what, it beats me :eek:

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
You really don't need to use Select.

And I don't see why you need a loop.
Code:
Private Sub CommandButton1_Click()

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

End Sub
 

Forum statistics

Threads
1,181,418
Messages
5,929,793
Members
436,694
Latest member
dpatete

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top