Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Values into an existing Row
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? -- Carlee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Values into an existing Row
On 15 Jul., 17:29, Carlee wrote:
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? -- Carlee 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Values into an existing Row
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 "Per Jessen" wrote: On 15 Jul., 17:29, Carlee wrote: 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? -- Carlee 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting Values into an existing Row
On 15 Jul., 20:51, Carlee wrote:
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 "Per Jessen" wrote: On 15 Jul., 17:29, Carlee wrote: 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? -- Carlee 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- Skjul tekst i anførselstegn - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting MROUND into existing data | Excel Worksheet Functions | |||
Inserting Dates in pre-existing data list | Excel Programming | |||
Inserting Dates in pre-existing data list | Excel Programming | |||
Inserting additional data into an existing cell? | Excel Discussion (Misc queries) | |||
inserting a row between two existing rows | New Users to Excel |