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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,044
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,044
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,044
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,167
Messages
5,442,787
Members
405,196
Latest member
rmuir12

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top