CODEWITHSUNDEEP
excelvba
Sara44
Sara44

Reputation: 422

Copy to next available line in a worksheet

I fill in a worksheet called "Info" with customer data. I then manually copy the same customer data to another worksheet "Orders". "Orders" contains invoice numbers and a structure like this:

      A           D           E               F            H         J
    INVOICE   FIRSTNAME   LASTNAME        DESCRIPTION   POSTCODE   EMAIL
1    1234       Fred       Smith         Red widget      AR225H   [email protected]
2    1235       Bill       Jones         Blue widget     EH66VT   [email protected]
3    1236
4    1237

I wish to create a macro to copy my data from "Info" to "Orders", cell by cell. I can do this but I need a conditional comment that says "If Orders column D is blank then select row 3 to copy data to". If I don't have this then I overwrite the last order line.

Note that the customer data changes on the "Info" sheet each time, this is why I manually copy it over.

I currently use this formula to pull in the next available invoice number into my Info sheet:

=INDEX(Orders!$A:$A,COUNTA(Orders!$D:$D)+1)

It allows me to have a cell (Say F3) on the "Info" sheet which already contains the right invoice number to allocate for the current customer data. Using this, I could possibly rewrite the condition to "Paste the information into the row where Info!F3 appears in Orders! column A".

Here is the Macro if it helps:

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("B6").Select
    Selection.Copy
    Sheets("Orders").Select
    Range("C735").Select
    ActiveSheet.Paste
    Sheets("Info").Select
    Range("B7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Orders").Select
    Range("D735").Select
    ActiveSheet.Paste
    Sheets("Info").Select
    Range("B8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Info").Select
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Orders").Select
    Range("E735").Select
    ActiveSheet.Paste
    Sheets("Info").Select
    Range("B12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Orders").Select
    Range("F735").Select
    ActiveSheet.Paste
    Sheets("Info").Select
    Range("B15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Orders").Select
    Range("G735").Select
    ActiveSheet.Paste
    Sheets("Info").Select
    ActiveWindow.SmallScroll Down:=-6
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Orders").Select
    Range("B735").Select
    ActiveSheet.Paste
End Sub

==================

Maybe there's too much info in that question? In its simplest form, all I need is to select a row based on the contents of a cell and then paste information to this row via a macro.

==================

Update - here is the "Info" sheet layout, note that not all rows match columns in the "Orders" sheet:

    A                             B                C

                          Invoice number:        1236

Peter 
Parker
12
Overflow Road
Stacktown
BB338J
0121 345 6789
0777 789 789
[email protected]

Order description:          Red widget

Upvotes: 0

Views: 1701

Answers (1)

Alex P
Alex P

Reputation: 12489

I like the fictitious address!

I tested this code and it works. You may need to change the cell references, especially in the Info sheet to get the right data.

In short, you start on the Info sheet, save the relevant data in variables, locate the invoice number in Orders and then paste the data.

Sub InfoToOrders()
    Dim InvoiceNo As Long, FirstName As String, LastName As String, Description As String, Postcode As String, Email As String

    With Worksheets("Info")
        InvoiceNo = .Range("D1")
        FirstName = .Range("A3")
        LastName = .Range("A4")
        Description = .Range("B13")
        Postcode = .Range("A8")
        Email = .Range("A11")
    End With

    Dim InvoiceNumbers As Range, invoice As Range

    With Worksheets("Orders")

        Set InvoiceNumbers = .Range("A1:A" & .Range("A1").End(xlDown).Row)

        For Each invoice In InvoiceNumbers
            If invoice = InvoiceNo Then
                invoice.Offset(0, 1) = FirstName
                invoice.Offset(0, 2) = LastName
                invoice.Offset(0, 3) = Description
                invoice.Offset(0, 4) = Postcode
                invoice.Offset(0, 5) = Email
            End If
        Next invoice

    End With
End Sub

Upvotes: 1

Related Questions