Activate workbook without using file name

**DamienC** · 07-22-2008, 03:01 AM

Good morning,

I have another silly question. In a macro, is it possible to open a workbook without using the path name?

I get one sheet with data. This sheet will have a different name every day. I'll call this one NewSheet. From this I wish to transfer data to a fixed sheet, for which I do have a file path (FixedSheet). Moving from NewSheet to FixedSheet is easy, beacuse I can use the path, but how can I go back to the other sheet? There are no other workbooks open.

I have worked out an alternative, which is to save the NewSheet as TempSheet in a fixed location, replace the previous one and use that file path, but I am wondering if there is an actual command to do this.

Does anybody know?

**royUK** · 07-22-2008, 03:51 AM

You don't need to switch workbooks. Your code would be in the master workbook, so you open the new book and leave it active, then run this macro (amend it to suit) from master workbook by using the Tools menu >-Macros

Option Explicit
Sub grabData()
    Dim SourceWb As Workbook

    Set SourceWb = ActiveWorkbook

    SourceWb.UsedRange.Copy ThisWorkbook.Sheets.Cells(1, 1)
    SourceWb.Close False
End Sub

Activate workbook without using file name

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Activate workbook without using file name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions