+ Reply to Thread
Results 1 to 5 of 5

[Macro] Selecting + copying range and paste in closed workbook

  1. #1
    Registered User
    Join Date
    06-07-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

    Question [Macro] Selecting + copying range and paste in closed workbook

    Hi all,

    I'm working on a Macro that would be doing the following steps:
    1. Select a range of data in an open Excel sheet and copy it (Columns A till C should be fixed. The rows should be dynamic and search for the last one filled)
    2. Open a closed workbook in a fixed folder
    3. Paste the copied data to that workbook
    4. Close the workbook

    I have already a macro to select and copy a dynamic range of data and paste it into a new workbook. Can anyone please help me modify the code or give me a direction on how to solve my Macro?
    Thank You


    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: [Macro] Selecting + copying range and paste in closed workbook

    In your code you are creating a new workbook, not opening a new workbook

    I would record the code of opening and saving and closing the fixed workbook through the recorder, then you can add this code to your code in place of
    Please Login or Register  to view this content.

    but you have not said where you wish to paste the data in the workbook that you open

  3. #3
    Registered User
    Join Date
    06-07-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

    Re: [Macro] Selecting + copying range and paste in closed workbook

    Thanks for the recording tip, will try it now.
    I want to paste the data in the first sheet of the closed workbook.

    Could you give me a tip how i can select and copy a fixed range of columns but a dynamic range of rows? Thanks

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: [Macro] Selecting + copying range and paste in closed workbook

    You are already doing it, you need to find the last row, by whaterever criteria works for you. what defines the last row

    https://www.thespreadsheetguru.com/b...lumn-using-vba


    but many links for the copying dynamic range
    https://www.excelcampus.com/vba/copy...ther-workbook/

  5. #5
    Registered User
    Join Date
    06-07-2019
    Location
    London
    MS-Off Ver
    365
    Posts
    6

    Re: [Macro] Selecting + copying range and paste in closed workbook

    For the ones that are interested in my final macro i posted it below.

    This is what it does:
    1. Selects and copys everything in the active sheet.
    2. Opens an existing workbook
    3. Pastes the data into workbook
    4. Saves the data into a new workbook with the data from today
    5. Closes the workbook

    The only thing that i can't figure out is how to only select column A till C to copy

    Please Login or Register  to view this content.

+ 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. Copy dynamic range from closed workbook paste as table into specific sheet
    By Huskersippi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-16-2019, 11:35 PM
  2. [SOLVED] VBA Macro pull a certain cell in closed workbook and copy/paste into current workbook
    By Hoover5896 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2017, 01:36 AM
  3. Copying data from a closed workbook with macro
    By tpollard1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2016, 01:26 PM
  4. Macro to copy and paste information from one sheet to another (closed) workbook
    By V.Cell in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2015, 04:25 AM
  5. [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
  6. Copy selected range from email attachment and paste into closed workbook
    By Lisa4legin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-15-2013, 09:32 AM
  7. Questions on copying range from closed workbook to active workbook
    By stevebriz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2007, 09:45 AM

Tags for this Thread

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