What could be the VBA for copying some specific cell from one worksheet to another in Excel?

Question

I am posting here again and need very much working and specific codes for making a VBA macro for copying some specific cells from one worksheet to another using one button.

Here you can see my current invoice format -

And here is the Database sheet -

Problem here is - Using the New Invoice button, I want to move to a new invoice while the data from specific cells, Invoice#, Order#, Sale#, Date#, Client's Name, Subtotal, Order Type, will be copied or moved from Invoice sheet to Database sheet. Also, the contents of these cells will be cleared as well.

I have the code for clearing contents and adding new invoice number -

Sub NewInvoice()

Range("H8").Value = Range("H8").Value + 0.00001
Range("D8:D10").ClearContents
Range("C13:C23").ClearContents
Range("H9:H10").ClearContents
Range("H25:H27").ClearContents
UserForm1.Show

End Sub

I need to add the codes for copying data from one worksheet to another worksheet inside this same code, for the specific cell data.

I hope I could explain my situation here.

Waiting for your reply and thank you for your time and consideration.

With regards Imran

Gass Gass · Accepted Answer · 2021-01-07T17:59:13

This should do it...

Sub moveData()
    
Dim db_next_row As Long
Dim invoice_n, order_n, sales_n, date_n As String
Dim c_name, subtotal, order_type As String
    
'CONFIG HERE 
'set the location of the cells in the invoice sheet
    '------------------------------------------
    invoice_n = ""
    order_n = ""   'example: order_n = "B3"
    sales_n = ""
    date_n = ""
    c_name = ""
    order_type = ""
    subtotal = ""
    '-----------------------------------------
    db_next_row = Sheets("Database").Cells(Rows.Count, 2).End(xlUp).Row + 1
    
    'move info to database
    With Sheets("Database")
        .Range("B" & db_next_row) = Sheets("Invoice").Range(date_n)
        .Range("C" & db_next_row) = Sheets("Invoice").Range(c_name)
        .Range("D" & db_next_row) = Sheets("Invoice").Range(invoice_n)
        .Range("E" & db_next_row) = Sheets("Invoice").Range(order_n)
        .Range("F" & db_next_row) = Sheets("Invoice").Range(sales_n)
        .Range("G" & db_next_row) = Sheets("Invoice").Range(subtotal)
        .Range("H" & db_next_row) = Sheets("Invoice").Range(order_type)
    End With
    
    'clear content in the invoice
    With Sheets("Invoice")
        .Range(date_n).ClearContents
        .Range(c_name).ClearContents
        .Range(invoice_n).ClearContents
        .Range(order_n).ClearContents
        .Range(sales_n).ClearContents
        .Range(subtotal).ClearContents
        .Range(order_type).ClearContents
    End With
    
    End Sub

What could be the VBA for copying some specific cell from one worksheet to another in Excel?

1 Answers