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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Roy CoxGroup Finance ManagerCommented:
Try this

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
spot on. Nice work!
Roy CoxGroup Finance ManagerCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.