Copy to specific location

[tomasj]

Hi! Need help with the following. Have two workbooks one (Avvik_databas.xls) is the "database" and avvikelse_rapport_fixa.xls/sheet 1. I extract data with vlookup from the "database file" (all values are from the same row) and paste the values in to avvikelse_rapport_fixa.xls/sheet1. I need to find the way back to the right row in the "database" and paste a new value in to one of the cells i that row. The reference for the row is the first value in that row this value was collected earlier.The "database" is normally closed. As a newbie any help would be appreciated. Thank you Tomas

 

[Andrew Poulsom]

Welcome to the Board.

If you have a macro please post it.

 

[tomasj]

I have a macro that is used to fill in the "database" from the beginning so to say. Would like to use the same with the requested adjustments.

Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("Formulär avvik.xls") Then
Set DestWB = Workbooks("avvik_database.xls")
Else
Set DestWB = Workbooks.Open(ThisWorkbook.Path & "\avvik_databas.xls")
End If

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("Fyll i").Range("b13:g18")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("Blad1")


Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange

Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

DestWB.Close Savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

I hope that this helps.
Tomas

 

[Andrew Poulsom]

Sorry, I don't see any vlookups in that code. You are just copying B13:G18 into the next blank cell in column A. What do you want to do next?

 

[tomasj]

Here is a link to a excel file that explains a bit better.(http://www.iso9000.nu/avvikelse_rapport_fixa.xls) It should work like this. In the red cell you type in a number and then the white cells are filled automaticly by Vlookup.Values are from a file (Avvik_databas.xls) In the green cell you type in your conclusion and by pressing the blue button the new value in the green cell should be sent to Avvik_database.xls to the same row as the rest of the values where collected from. My problem is that i don not know how to find the right row and to copy the data in to the right column. Thanks. Tomas

Sorry, I don't see any vlookups in that code. You are just copying B13:G18 into the next blank cell in column A. What do you want to do next?

 

[Andrew Poulsom]

I can't open that link (it seems to require Avvik_databas.xls to be available). Can you post one of your VLOOKUP formulas please?

 

[tomasj]

Here is the link again. http://www.iso9000.nu/avvikelse_rapport_fixa.xls
And here is the vlookup.
=LETARAD(E1;[Avvik_databas.xls]Blad1!$A$2:$L$1000;9)
And here is a picture of the file.

I hope that this works.
Tomas

I can't open that link (it seems to require Avvik_databas.xls to be available). Can you post one of your VLOOKUP formulas please?

 

[Andrew Poulsom]

OK, so E1 contains a date and time and you want to find that date and time in column A of Blad1 in avvik_database.xls. In which column do the want to put what's in E18?

 

[tomasj]

In column K.

OK, so E1 contains a date and time and you want to find that date and time in column A of Blad1 in avvik_database.xls. In which column do the want to put what's in E18?

 

[Andrew Poulsom]

Instead of:

Set SourceRange = ThisWorkbook.Sheets("Fyll i").Range("b13:g18")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("Blad1")


Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange

Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

try:

    Set SourceRange = ThisWorkbook.Sheets("Fyll i").Range("B13")
'    Change the sheet name of the database workbook
    Set DestSh = DestWB.Worksheets("Blad1")
    Lr = WorksheetFunction.Match(CDbl(ThisWorkbook.Sheets("Fyll i").Range("E1").Value), DestSh.Columns(1), False)
    Set DestRange = DestSh.Range("K" & Lr)
    DestRange.Value = SourceRange.Value

I think you only need to copy B13 rather than B13:G18 (merged cells).