Double click cell on one worksheet, copy to another cell on different worksheet

[ssauk]

Hi All,
Although I have been using Excel for years, I am new to the developer side of it.

I am looking for assistance with a problem I have with a project I have done for my wife's shop.
The project is an invoice workbook I have designed which is working great, but I have been asked to improve it slightly.
The workbook has 4 worksheets in it, Customer, Products, Invoice and Register.
I am wanting to be able to double click a cell in column A on sheet Products and have this value enter into a cell on the sheet Invoice, I have managed to do this and it populates the cell A13, but once it has done this when I double click on another cell in column A on worksheet Products, I need it to move down to cell A14 on the worksheet Invoice and so on. At present what I had done will only keep changing the cell A13. I have deleted it all as I just cannot seem to get my head around this.
Hope I have explained it well enough for anyone to understand.

Thanks in advance
Daz

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      Target.Copy Sheets("Invoice").Range("A" & Rows.Count).End(xlUp).Offset(1)
      Cancel = True
   End If
End Sub

 

[ssauk]

Hi Fluff,
Thanks for that it works at transferring it over to the sheet, but I need to enter it into a specific area on the invoice, cell A13, then A14 and so on up to A31. Your suggestion is adding it at the last row which is A43, then the next etc...

 

[ssauk]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      Target.Copy Sheets("Invoice").Range("A" & Rows.Count).End(xlUp).Offset(1)
      Cancel = True
   End If
End Sub

Hi Fluff,

I played about a bit with the code you supplied, probably totally wrong, but got it to position it in the correct target cell, just trying now when I double click again on Product worksheet that it goes down to next cell on target worksheet Invoice.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 1 Then
Target.Copy Sheets("Invoice").Range("A13").End(xlUp).Offset(1)
Cancel = True
End If
End Sub

 

[Fluff]

Is there any data in A12 and also in A32?
Also what should happen if all cells between A13 & A31 are filled?

 

[ssauk]

Is there any data in A12 and also in A32?
Also what should happen if all cells between A13 & A31 are filled?

Hi
I have attached an image of the target sheet
A12 has data in it and is locked as sheet is protected, the same with A31.
At present we fill these cells from a dropdown or by typing in the products id from Product sheets Column A, so when we have printed , then saved the invoice we just manually delete the cells A13 to A31. (I will probably look at clearing this range for the next new invoice, as i learn more)
I have been watching lots of tutorials for things similar to this, but cannot get the end result working so far, pitfall of trying to self teach myself.
Hope this helps and thanks for the help.

 

[ssauk]

Here is the products sheet too, when I double click on any cell in Column A ( Product_ID), it is currently placing the contents into the Cell A13 on Invoice Sheet.
Hope this helps

 

[Fluff]

Ok, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      On Error Resume Next
      Target.Copy Sheets("Invoice").Range("A13:A31").SpecialCells(xlBlanks)(1)
      On Error GoTo 0
      Cancel = True
   End If
End Sub

 

[ssauk]

Hi
Thats great its working when I unprotect the sheet, but does not work with the sheet protected. Not sure why, as your first code worked on the sheet when protected.
Thanks for the help

 

[Fluff]

You can unprotect the sheet like

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.Column = 1 Then
      With Sheets("Invoice")
         .Unprotect "Pword"
         On Error Resume Next
         Target.Copy .Range("A13:A31").SpecialCells(xlBlanks)(1)
         On Error GoTo 0
         .Protect "Pword"
      End With
      Cancel = True
   End If
End Sub

Just change the password to suit