Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more


Simple Macro - Remove hardcoded Spreadsheet name

Posted on 2011-02-16
Medium Priority
Last Modified: 2012-06-21
See attached code.
This code was generated by "Recording" key strokes.  (I think)

The sequence is as follows;
1. Open workbook "BOOK_A".

2. Run the macro below which opens workbook "BOOK_B" (and copies data from "BOOK_B")

3. The data from "BOOK_B" is then pasted into "BOOK_A".

Question: I do not want to hardcode the "BOOK_A" workbook in the VBA.  Since I begin in BOOK_A how can I simply re-activate this book without specifying the hard-coded name.

This is the problem VBA line (I merely want to re-activate the sheet that I started in).

Sub TBImportIRE()
' TBImportIRE Macro
    Sheets("IRE TB - Current").Select
End Sub

Open in new window

Question by:Patrick O'Dea
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 11

Accepted Solution

Runrigger earned 2000 total points
ID: 34906511
Private Sub test()
Dim wkbTarget As Workbook

    Set wkbTarget = ActiveWorkbook

End Sub
LVL 11

Expert Comment

ID: 34906518
ActiveWorkbook = your BOOK_A, I am assuming that the macro is run from BOOK_A?

Author Closing Comment

by:Patrick O'Dea
ID: 34906779
Great , thanks

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

647 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question