copy active cell to same cell in sheet 2.

[stavrosiona]

I want to copy active cell if sheet 1 to same cell in sheet 2 by using vba button. Active cell is not the same each time.
I use: Range (sheet1!a2).value = range(sheet2!a2) but this does 9nly for the cell a2 and not the active cell each time.

 

[stavrosiona]

May i ask you a question guys?
Insted of preset to add +1 if i want when i press the button to ask me to: "Enter value you want to add" and then add a value in a box and add it?

 

[DILIPandey]

Okay.. include below lines in your code:-

i = InputBox("Enter value which need to be added to the Sheet2's value", "Enter Value to be added")
Range("Sheet2!" & ActiveCell.Address).Value = ActiveCell.Value + i


Regards,
DILIPandey

 

[williamgowtama]

Sub Button1_Click()
Dim a1 As String
Dim a2 As Integer
a1 = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
a2 = InputBox("Enter Value", "Add")
Sheets("Sheet1").Range(a1) = CInt(Sheets("Sheet2").Range(a1)) + a2
End Sub

 

[stavrosiona]

Thank you both for the help.
The code of DILIPandey works, the code of williamgowtama unfortunately changes the price of sheet 1 instead of sheet 2. maybe i did a mistake, but because the other code works i am ok.
If i may, a problem occured is that if i do not want finally to add value and i want to cancel the command, i press the cancel button but rerurns to a runtime error.
Is there any way to cancel the command without return to an error?
thanks,

 

[williamgowtama]

Sub stav()
Dim a1 As String
Dim a2 As Variant
Dim a3 As Integer
a1 = Selection.Address(ReferenceStyle:=xlA1, RowAbsolute:=False, ColumnAbsolute:=False)
a2 = InputBox("Enter Value", "Add")
If Not IsNumeric(a2) Then
    MsgBox "Please Input Number"
    Exit Sub
Else
    a3 = CInt(a2)
End If


Sheets("Sheet2").Range(a1) = CInt(Sheets("Sheet2").Range(a1)) + a3
Sheets("Sheet1").Range(a1) = Sheets("Sheet2").Range(a1)
End Sub

 

[DILIPandey]

Okay.. stavrosiona,

Good that my code works.. to avoid error in case you cancelled the inputbox option, you can put a code line "On Error Resume Next" on top of my code.

Regards,
DILIPandey

 

[stavrosiona]

Hi DILIPandey
I have an issue with a code in this excel file I have done from the past.
Basically I have sheet 1 which is doing the deduction of cells in sheet 2 and sheet 3. i.e Sheet 1 = Pending Sheet 2 = Order, Sheet 3 =Deliver
So cell Pending cell B3 in sheet 1 is the pending amount of the order -deliberies and i am using in sheet 1 the code: =(ORDER!B3-DELIVER!B3)


What I want is that to use this code but when (ORDER!B3 = DELIVER!B3) and is not 0 then to write in cell B3 in pending the number of ordersheet or deliversheet which is the same. So whan i see in the pending sheet a cell filled yellow and with a number in i.e 3 then I know that that customers orderd 3 pcs and delivered 3 pcs to him.
The reason is that I use conditional formatiing to fill yellow colour the cell B3 is Order!B3= Deliver!B3 but i want instead of empty cell to write the number of order or deliver (which is the same)
So for example:
Order 5 Deliver 3 The Pending cell shows 2 with grey fill in (I used conditional when Order>Deliver to fill it grey)
Order 5 Deliver 5 The pending cell shows empry (I think i used vba to do it that) and filled with yellow (used conditional when Order=Deliver to be yellow and if not 0)


Thanks for your help in advance

 

[stavrosiona]

Hi,
I am using this excel file we have done that has 3 worksheets,
Main is pending sheet which shows the differnece between ordered and delivered sheets which are the other two. "Pending", "Deliver", 'Order"
I am trying now to do a sheet where I will take the orders: "OrderForm"
Basically I need a sheet where in column A to choose Customer (from a customer list that will appear) then in Column B to choose the Product and column C qty and COlumn D to write any note.
Then I need to be able this data to go to sheet "Orders" where in first Rows 1E,F,G.... I wrote the customer names and Column A1,2,3,4, I wrote the products, to check and for example If a customer name "Steve" order a product named: "Pensil" and customer steve in Order sheet is in row H, and Pensil is in Column A18 then to fill this box with the qty I order, then if in anothet line of the order form Steve Order again a Pensil to do the total of Order in the Order Sheet 2 etc.