Hi there to everyone, this here is my first post after my introduction post.

I will explain what I am trying to do, basically I need a macro (name of the macro: ImportData) in my current opened workbook ("MyCurrentWorkboook.xlsm") that does following in chronological order;

1. Open a database file (workbook "DATABASE.xlsm")
2. Copy a range from a sheet in that workbook
3. Go back to my current opened workbook ("MyCurrentWorkboook.xlsm")
4. Paste the range in a range I need
5. Close the databasefile

Nothing too difficult one might think.

Sub ImportData()

'Clear the contents of sheet "DATASHEET" in active (this) document
Sheets("DATASHEET").Range("A8:Z1000").Clear
     
'Declaration - declare an object for setting the workbook
Dim wkb As Workbook

'Open Workbook and set an object(wkb)
Set wkb = Workbooks.Open("D:\Test Folder\DATABASE.xlsm")

'Copy and PasteSpecial between workbooks
    Workbooks("DATABASE.xlsm").Worksheets("DATASHEET").Range("A8:Z1000").Copy
    Workbooks("MyCurrentWorkboook.xlsm").Worksheets("DATASHEET").Range("A8:Z1000").PasteSpecial Paste:=xlPasteFormats
    Workbooks("MyCurrentWorkboook.xlsm").Worksheets("DATASHEET").Range("A8:Z1000").PasteSpecial Paste:=xlPasteFormulas
    
'Close opened workbook using object
Application.DisplayAlerts = False
wkb.Close SaveChanges:=False
Application.DisplayAlerts = True

End Sub
I got the code working but only when I hardcode the filenames. The databasefile is not a problem for it is a static document, always. But I don't know the name of my current opened workbook, the file itself gets copied over and over again so the name changes depending on the project. So how do I cange the code in order it uses the current workbookname?

This is where it becomes a drama:

Workbooks("MyCurrentWorkboook.xlsm").Worksheets("DATASHEET").Range("A8:Z1000").PasteSpecial Paste:=xlPasteFormats
Workbooks("MyCurrentWorkboook.xlsm").Worksheets("DATASHEET").Range("A8:Z1000").PasteSpecial Paste:=xlPasteFormulas
Any help on this one is much appreciated thanks in advance!