asked on

copy and paste - macro

Hi

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.

Thanks
query-log.xlsx
query.xlsx

Roy Cox

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

mikes6058

ASKER

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.

Thanks
query1.xlsx

ASKER CERTIFIED SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikes6058

ASKER

spot on. Nice work!

Roy Cox

Thanks