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

Eskild

New Member
Joined
May 6, 2019
Messages
5
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,177
Office Version
2007
Platform
Windows
Try this:

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

New Member
Joined
May 6, 2019
Messages
5
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,177
Office Version
2007
Platform
Windows
in which of the 2 macros?
or is it a single macro?
 

Eskild

New Member
Joined
May 6, 2019
Messages
5
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,177
Office Version
2007
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,733
Messages
5,446,188
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top