copy and paste - macro


Attached are two sheets, query and query log.

As a company we receive lots of queries containing information like on the (query) sheet attached. The same fields will be completed every time a query is received. Currently I would have to copy the row containing the query info (not the column headings) received and then copy and paste the row into the next available row in our query log sheet. In this example I would copy row 2 from the query sheet into the next available row in the query log sheet (row 10). It is important that the information copied from the query sheet matches the fields in the query log sheet. e.g. member name info would be copied into column E on the query log sheet. I would then have to populate the date (the date the query is copied into the query log) into column b.

Can any produce some code which will allow me to open a newly received query sheet, then click a button, this will then automatically copy and paste the appropriate info from the query into the next available row in the query log sheet and will automatically populate column B with the date.

Who is Participating?
Roy CoxGroup Finance ManagerCommented:
Try this
Roy CoxGroup Finance ManagerCommented:
It would be much simpler if the headings matched in each workbook. This code will allow you to open an excel file then copy specific cells to the next row in the log. As I said if the headings matched the code could be considerably improved.

Place the code into a Standard Module in the Log file

Option Explicit

Sub ImportData()
    Dim oWb As Workbook
    Dim lRw As Long
    Dim sFilter As String, sTitle As String, sFile As Variant

    sFilter = "Excel Files (*.xl*),*.xl*"
    sTitle = "Please Select an Excel File"
    sFile = Application.GetOpenFilename(sFilter, , sTitle)

    If sFile = "False" Then
        MsgBox "No file selected", vbCritical, "Cancelled"
        Exit Sub
    End If

    If LCase(Mid(sFile, InStrRev(sFile, "."), 3)) <> ".xl" Then
        MsgBox "Excel File not selected", vbCritical, , "Excel rerquire"
        Exit Sub
    End If

    Workbooks.Open Filename:=sFile
    Set oWb = ActiveWorkbook
    With ThisWorkbook.Sheets(2)
        lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(lRw, 2).Value = Format(Date, "short date")
        .Cells(lRw, 5).Value = oWb.Cells(2, 2).Value
        'add other cells to be copied here

    End With
End Sub

Open in new window

mikes6058Author Commented:
I have copied the headings so they now match in each workbook (see attached). Would it be possible for you to insert the coding into the appropriate workbook and assign it to a button.

mikes6058Author Commented:
spot on. Nice work!
Roy CoxGroup Finance ManagerCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.