move cell to another worksheet

everscern

Board Regular
Joined
Oct 10, 2006
Messages
56
Hi.. sorry to bother you guys again. But VBA is some tedious work.
:confused:

anyway, is there any macro to move the active cell value to another worksheet with a command button?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
Yes, of course:

As long as the command button is on the same sheet as the value of the active cell you want to move over, and replacing my reference to sheet2 range A1 with the reference of your sheet name and the target cell you want the value to go to:

Code:
Private Sub CommandButton1_Click()
Sheets("sheet2").Range("A1").Value = ActiveCell.Value
End Sub
......should do the trick.
 

everscern

Board Regular
Joined
Oct 10, 2006
Messages
56
is it possible to move it to the next blank cell in a specific column?

for example: column A is for black files, column B for blue files.
A dialog box will ask if its black/blue files. if let's say, it's a blue file, the input value will be placed at column B in the next blank cell.

I'm sorry if it's too complicated. Thanks in advance.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,759
Office Version
  1. 365
Platform
  1. Windows
It's not too complicated, but would have been better if you'd asked the whole question in the first place.

We'll use an inputbox to test for black or blue, and the output from that to drive the cell value to the correct place.

Use this code in the commandbutton's click event, in place of the other code:
Code:
Private Sub CommandButton1_Click()
Dim black_col As Integer, blue_col As Integer

black_col = Sheets("sheet2").Range("A65536").End(xlUp).Row + 1
blue_col = Sheets("sheet2").Range("B65536").End(xlUp).Row + 1

test = InputBox("Is this a black or a blue file?" & Chr(10) & "Please type black or blue." & Chr(10) & "Please use lowercase only.", "FILE TYPE CHECK")
    
    If test = "black" Then
        Sheets("sheet2").Range("A" & black_col).Value = ActiveCell.Value
    Else
          Sheets("sheet2").Range("B" & blue_col).Value = ActiveCell.Value
    End If
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,919
Members
410,712
Latest member
jhgeorge
Top