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!
Bookmarks