Copy to specific location

tomasj

New Member
Joined
Nov 19, 2009
Messages
10
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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?
 
Upvote 0
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 :confused:

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?
 
Upvote 0
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?
 
Upvote 0
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.
excelfile.gif


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?
 
Upvote 0
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?
 
Upvote 0
Instead of:

Code:
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:

Code:
    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).
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top