1

and need help to accomplish a specific copy and paste. I have an active customer list That is always updating. And when the work is complete I have to prepare an invoice. I am trying with no success to create a button on my active costumer workbook that will copy the customer name that I select maybe by highlighting the cell which Is in column c, then transfer that name to a template named invoice in cell B3. The template already set up with Vlookup and if function to populate all information based on the customer name. I want the button to copy the cell that I select not a whole column or row. Is this code possible?

Community
  • 1
  • 1
user4251973
  • 19
  • 2

1 Answers1

1

Both these suggestions assume that you are in the Customer workbook/sheet with the cell selected ... button code could look like this:

Sub Copy_Cust()
    Workbooks("Invoice").Sheets("Sheet1").Range("B1") = ActiveCell
End Sub

or

Sub Copy_Cust2()
    Selection.Copy
    Workbooks("Invoice").Sheets("Sheet1").Range("B1").PasteSpecial
    Application.CutCopyMode = False
End Sub
Mike Powell
  • 260
  • 1
  • 6
  • Thanks Mike I'll try that and let you know – user4251973 Nov 14 '14 at 12:46
  • Hi I tried the second option and It accomplished the first part which is coping the selected cell. how would I combine it to now transfer the customer to the service work book. :Private Sub CommandButton1_Click() Dim CustomerName As String Dim serviceInvoice As Workbook Selection.Copy Worksheets("Active").Select Customer = Range("C1") = ActiveCell Set serviceInvoice = Workbooks.Open("C:\Users\Service Dept\Templates\Service Invoice.xls") Worksheets("Invoice1").Select Worksheets("Invoice1").Range("B3").Select Application.CutCopyMode = False End With serviceInvoice.SaveAs End Sub – user4251973 Nov 14 '14 at 15:11
  • Private Sub CommandButton1_Click() Dim CustomerName As String Dim serviceInvoice As Workbook Worksheets("Active").Select Customer = ActiveCell Set serviceInvoice = Workbooks.Open("C:\Users\Service Dept\Templates\Service Invoice.xls") Worksheets("Invoice1").Select Worksheets("Invoice1").Range("B3") = Customer serviceInvoice.SaveAs End Sub – Mike Powell Nov 14 '14 at 15:32
  • Awesome..It can now open the invoice on the click. but I'm not sending any data over. Seen like it's sending an empty cell. Not sure where I'm going wrong. – user4251973 Nov 14 '14 at 15:55
  • This script assumes that you are positioned on the Cell with the customer name on it. is the command button on the same page? ... if it is then remove the "Worksheets("Active").Select" line – Mike Powell Nov 14 '14 at 16:51
  • Yes the button is located on the active customer sheet. I removed it. I still open the location but the copy and paste part is not working. I this is what I have so far. Private Sub CommandButton1_Click() Dim CustomerName As String Dim serviceinvoice As Workbook Set serviceinvoice = Workbooks.Open("C:\Users\Service Dept\Templates\service invoice") Worksheets("Invoice1").Select Worksheets("Invoice1").Range("B3") End Sub – user4251973 Nov 14 '14 at 18:47
  • Thanks for you help Mike I found the next part of the puzzle. I added range("customers").end(xldown).copy and then activesheet.paste before end sub. – user4251973 Nov 15 '14 at 15:54