Solved

Simple Macro - Remove hardcoded Spreadsheet name

Posted on 2011-02-16
3
268 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
  • 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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