Inserting Values into an existing Row

[Carlee]

Good Morning All,
I have a staff member at an external site that sends me a sheet with data
(25 columns, in a single row, with a unique date0. I have a master book here
at my office, that I enter a new row in each day. I want to be able to
import the data sent to me from site, and insert the data into the
appropriate columns, for the specific date. Several days often go by, before
i get the sheet from site, so the code needs to be able to find the right
date and insert the data into the correct colomns.

How can I do this?

 

[Per Jessen]

Good Morning All,
I have a staff member at an external site that sends me a sheet with data
(25 columns, in a single row, with a unique date0.  I have a master book here
at my office,  that I enter a new row in each day.  I want to be ableto
import the data sent to me from site, and insert the data into the
appropriate columns, for the specific date.  Several days often go by, before
i get the sheet from site, so the code needs to be able to find the right
date and insert the data into the correct colomns.

How can I do this?

Hi Carlee

You need macro to do the job.

You have a master book where you enter a date in column XX ?

Will there be more than one row of data in the sheet from site than
needs to be copied to appropiate row?

Once the right row is found, can data be pasted into master book as
one range or does it has to be pasted as several ranges?

Regards,
Per

 

[Carlee]

Hi there,
To answer your questions:

Periodically, site will send me a sheet containing a single row of data. I
will have that row of data imported into a sheet called 'Results Log'. the
'Results Log' is in the Master Workbook.

Also in the Master Workbook, is the 'Master Log' sheet.

I want the system to take the last row of data in the 'Results Log' and
insert that data in teh correct columns, in the correct row (where the dates
match)

The data being pasted into the Master log will be a single range (Columns
AA:AH)

 

[Per Jessen]

Hi there,
To answer your questions:

Periodically, site will send me a sheet containing a single row of data.  I
will have that row of data imported into a sheet called 'Results Log'.  the
'Results Log' is in the Master Workbook.

Also in the Master Workbook, is the 'Master Log' sheet.

I want the system to take the last row of data in the 'Results Log' and
insert that data in teh correct columns, in the correct row (where the dates
match)

The data being pasted into the Master log will be a single range (Columns
AA:AH)

--
Carlee











- Vis tekst i anførselstegn -

Hi Carlee

I assume data to import is in A1:I1, and that dates in Master Log is
in column A.

User is asked to select the workbook to import data from.

Try this:

Sub Carlee()
Dim MasterSH As Worksheet
Dim rLogSH As Worksheet
Dim TargetDate
Dim TargetRow As Integer
Dim DestinationRow As Integer
Dim rLogDateCell As String
Dim MasterLogDateCell As String
Dim MasterLogDateColumn As String
Dim MasterWB As Workbook
Dim ImportFrom As Workbook
Dim found As Variant

Set MasterWB = ThisWorkbook
Set MasterSH = Worksheets("Master Log")
Set rLogSH = Worksheets("Results Log")
rLogDateCell = "A1"
MasterLogDateCell = "A1"
MasterLogDateColumn = Range(MasterLogDateCell).Column
TargetRow = rLogSH.Range(rLogDateCell).End(xlDown).Row + 1

fileToOpen = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox "No file was selected" & vbLf & "Exit macro"
Exit Sub
End If

Set ImportFrom = Application.Workbooks.Open(fileToOpen)

Range("A1:I1").Copy
MasterWB.Activate
rLogSH.Paste Destination:=rLogSH.Cells(TargetRow, 1)

ImportFrom.Close

rLogSH.Activate
TargetDate = Range(rLogDateCell).End(xlDown).Value


With MasterSH
Set found = .Columns("A").Find(what:=TargetDate,
After:=.Range("A1"), _
SearchOrder:=xlByColumns, LookAt:=xlWhole)
End With

DestinationRow = found.Row

Range(Cells(TargetRow, "B"), Cells(TargetRow, "I")).Copy _
Destination:=MasterSH.Cells(DestinationRow, "AA")
End Sub

Best regards,
Per