Solved

Simple Macro - Remove hardcoded Spreadsheet name

Posted on 2011-02-16
3
271 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 500 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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