+ Reply to Thread
Results 1 to 7 of 7

Paste Text into any Active workbook from a set workbook

  1. #1
    Registered User
    Join Date
    12-29-2017
    Location
    Seattle
    MS-Off Ver
    Office 2016
    Posts
    5

    Paste Text into any Active workbook from a set workbook

    Hello Excel Experts,

    I have been attempting to create a Macro that inserts text into any open worksheet. I run into the same barrier every time. Excel is requiring me to name a specific workbook to be activated, but the whole point is that I need to be able to run the macro in any desired workbook.

    Sub biweeklyholdings()
    '
    ' biweeklyholdings Macro
    '

    '
    Windows("Bi-Weekly Disclaimer.xlsx").Activate
    Range("A1:L15").Select
    Range("L15").Activate
    Selection.Copy
    Windows(ActiveWorkbook).Activate
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    End Sub

  2. #2
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Paste Text into any Active workbook from a set workbook

    Quote Originally Posted by bachmap View Post
    Excel is requiring me to name a specific workbook to be activated, but the whole point is that I need to be able to run the macro in any desired workbook.
    How is it requiring you to do so? Do you get an error message? Can you post a sample file that replicates the issue? What is the desired behavior (exactly)?
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  3. #3
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Re: Paste Text into any Active workbook from a set workbook

    Please Login or Register  to view this content.
    Not sure if this is what you mean but it might do what you are looking for. If you want to copy from a workbook you need to have that file open in order to do that. If you want there are ways to reference a files from its file path and have it automatically opens you can do that as well.

    Would need more info on what you want to help more.

  4. #4
    Registered User
    Join Date
    12-29-2017
    Location
    Seattle
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Paste Text into any Active workbook from a set workbook

    This is the error that I run into each time,

    Error message.JPG

    I then proceed to open up the debug script in an attempt to fix it. Where I run into this text,

    Error message.JPG

    My Apologies if I am not describing my desired outcome in enough detail. I will try and write it out step by step.

    1. Copy a disclaimer, Cells A1:E15, in Workbook 1 (Titled Bi-Weekly Disclaimer)
    2. Insert copied disclaimer into Cell A1 of Workbook 2 (Shift cells down, as opposed to pasting over the top of the cells)
    3. Done.

    The issue I run into, is that when I create this Macro, it wants to activate a workbook by name. Where I need it to run the macro in any number of open workbooks.

    I hope that was a more concise explanation.

    PS I apologize for the attached image, I cannot seem to delete, but it is unrelated.
    Attached Images Attached Images
    Last edited by bachmap; 01-03-2018 at 03:08 PM.

  5. #5
    Registered User
    Join Date
    12-29-2017
    Location
    Seattle
    MS-Off Ver
    Office 2016
    Posts
    5

    Re: Paste Text into any Active workbook from a set workbook

    @Dougman824

    I appreciate the response. The solution you have offered would work for 1 workbook. However, I need to be able to use this Macro in 28 separate workbooks. Am I asking for something impossible?

  6. #6
    Forum Contributor
    Join Date
    06-12-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    167

    Re: Paste Text into any Active workbook from a set workbook

    Not sure what the sheet name is for the bi-weekly disclaimer so you'll need to add that between the "". Then if you put this in a module on the Bi Weekly Disclaimer and have it open with the file you want to have the data copied to then you can pick the sheet you want in that target book > go to the developer tab on the ribbon > click macros > make sure the drop down says all open workbooks > find the macro you want (currently I have named this test)> click run.

    Please Login or Register  to view this content.
    If you are looking for it to automatically go through lets say 10 open workbooks and do it to all of them automatically the code would need to be changed but it could be done.

  7. #7
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Paste Text into any Active workbook from a set workbook

    You would typically not use the macro in the fashion you have mentioned. You would have the macro loop through say a folder with all of the files in it you want to update, open each, copy the data over, close it and move on to the next.

    Please Login or Register  to view this content.
    The above may work but its sloppy code. You are better off declaring variables and wirtting a loop that will go through your desired workbooks to make the change.

    Please Login or Register  to view this content.
    Select and activate should be avoided at almost all costs. They are rarely ever required and more often than not can behave strangely under various circumstances.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need a Macro that can paste text from one workbook into any open workbook
    By bachmap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2017, 09:36 PM
  2. [SOLVED] Code to copy data from a closed workbook and paste in active workbook using named range.
    By paullie1912 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-28-2014, 02:38 AM
  3. [SOLVED] Code to detect previous active workbook instead of current active workbook
    By kosherboy in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-25-2014, 01:58 AM
  4. Cannot paste into an non-active workbook
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-17-2013, 11:34 AM
  5. Open workbook, filter values, copy/paste into Active workbook.
    By niceguy21 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2013, 12:17 PM
  6. [SOLVED] Copying worksheet from another open workbook using variables, paste to active workbook
    By sanpedro_nz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-08-2012, 08:25 AM
  7. Paste data from active workbook to another workbook using code.
    By 2funny4words in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2009, 04:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1