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
 

tomasj

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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).
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top