Indsæt kopiret område i første ledige række

[Eskild]

Hej.
Jeg har et problem, og jeg kan ikke lure, hvorfor den melder fejl.
Jeg er ved at lave en VBA-kode, som kan åbne en excel fil, kopiere et bestemt område, og derefter lukke igen.. Denne del virker vidst optimalt?
Det næste er, at jeg skal kopiere et område i et ark og sætte det over, hvor den første ledige celle/række er i kolonne B. - Denne del er problemet.
Endvidere skal jeg have den til at kopiere det, sådan at der kun indsættes værdien (altså ikke formlen)

Jeg har indtilvidere denne stykke kode:
Jeg beklager at den er så rodet, men jeg har bøvlet med det et stykke tid.


Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Find den journal du ønsker at importere", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("A1:L40").Copy
ThisWorkbook.Worksheets("Ark3").Range("A1:L40").PasteSpecial xlPasteValues
OpenBook.Close False

End If
Application.ScreenUpdating = True

Sub FindTomogindsætdata()

Sheets("Ark3").Activate
Range("O13:AB25").Copy

Sheets("Ark2").Activate
Range("B15:B1000").Find(Empty, LookIn:=xlValues).Select
Selection.PasteSpecial xlPasteValues
End Sub

End Sub

 

[DanteAmor]

Try this:

Sub Get_Data_From_File()
  Dim FileToOpen As Variant
  Dim OpenBook As Workbook
  Application.ScreenUpdating = False
  
  FileToOpen = Application.GetOpenFilename(Title:="Find den journal du ønsker at importere", FileFilter:="Excel Files (*.xls*),*xls*")
  If FileToOpen <> False Then
  Set OpenBook = Application.Workbooks.Open(FileToOpen)
  OpenBook.Sheets(1).Range("A1:L40").Copy
  ThisWorkbook.Worksheets("Ark3").Range("A1:L40").PasteSpecial xlPasteValues
  OpenBook.Close False
  
  End If
  Application.ScreenUpdating = True
End Sub

Sub FindTomogindsætdata()
  Sheets("Ark3").Activate
  Range("O13:AB25").Copy
  
  Sheets("Ark2").Activate
  Range("B" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
End Sub

 

[Eskild]

Hej Dante.
Thanks for reply!
My subscript says out of range...
Is it possible to get it to open, and copy the cells, then close it again, and paste it in the already open sheet?

 

[DanteAmor]

in which of the 2 macros?
or is it a single macro?

 

[Eskild]

If it could be one macro, it could be nice, but i cant figure out how to do it all in one macro... But 1 macro is the dream.

It schoul open an excel file, copy a range in the opend.
Then insert it in a sheet called "rammeliste" in my opened woorksheet.
Then it need to copy another range, and insert it in a sheet called "Ark 2", in the first empty row in column b.

Everytime i do it, it inserts it in the bottum of my sheet.

 

[DanteAmor]

Then insert it in a sheet called "rammeliste" in my opened woorksheet.
Then it need to copy another range, and insert it in a sheet called "Ark 2", in the first empty row in column b.

Your 2 macros don't do that.
Better explain step by step what you need
(book-sheet-range) .copy
(book-sheet-range) .paste