?
Solved

Simple Macro - Remove hardcoded Spreadsheet name

Posted on 2011-02-16
3
Medium Priority
?
272 Views
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).

Windows("BOOK_A").Activate
Sub TBImportIRE()
'
' TBImportIRE Macro
'
    Windows("BOOKB").Activate
    Sheets("IRE TB - Current").Select
    Cells.Select
    Selection.Copy
    Windows("BOOK_A").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
End Sub

Open in new window

0
Comment
Question by:Patrick O'Dea
[X]
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
3 Comments
 
LVL 11

Accepted Solution

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

    Set wkbTarget = ActiveWorkbook
   
    wkbTarget.Activate
   
   


End Sub
0
 
LVL 11

Expert Comment

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

Author Closing Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…
Suggested Courses

752 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