+ Reply to Thread
Results 1 to 6 of 6

Copy and Paste Cell Values between Workbooks on Separate File paths

  1. #1
    Registered User
    Join Date
    02-16-2018
    Location
    Lagos
    MS-Off Ver
    MS Office 2016
    Posts
    25

    Copy and Paste Cell Values between Workbooks on Separate File paths

    Hello Your 'Excel'lencies!

    Please help with this. I have a macro that can copy and paste values from one sheet to another on the same workbook with a control button as shown below:

    Please Login or Register  to view this content.
    However, I need assistance with a Macro that can replicate values on a range of cells on a worksheet for an open active workbook located on a directory based on corresponding entries or changes on an array of cells from another workbook located on another directory at the click of an embedded control button

    For instance, in the attached file,I would want any updates or changes done on Cell "A4","B4", "C4",.. to "AO4" of 'Billing' sheet to be replicated on corresponding cells "H13", "I13", "J13",.. to "AV13" repectively of the 'Main' sheet at the click of Active X control button in a case whereby the 'Billing' sheet is on a separate workbook (from the workbook with Main sheet) on a different directory

    Same for 'App' Sheet: to replcate update or changes on Cells "A4","B4", "C4",.. to "AO4" to apply changes on corresponding cells "H6","I6", "J6",.. to "AV6" accordingly

    Note:
    Main sheet is located at
    HTML Code: 
    \\server\common\SDLC\[Dashboard.xlsx]Main
    Billing sheet located at
    HTML Code: 
    \\server\common\Checklist\[Checklist.xlsx]Billing
    and App sheet located at
    HTML Code: 
    \\server\common\Checklist\[Checklist.xlsx]App
    I would really appreciate.
    Thank you in advance!
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copy and Paste Cell Values between Workbooks on Separate File paths

    this procedure assumes that workbook "Dashboard" will host the code and therefore will be open when the button is used. You are showing Workbooks("Dashboard") currently as a .xlsx file extension. That workbook needs to be saved as a macroenabled (.xlsm) workbook to preserve macros when it is closed. If there are formulas in the range on sheet 'Main' you woul need to use PasteSpecial method instead of the direct copy/paste method. Paste this code into the standard public code module and select your button from the Form Controls tool box under Developer/Insert. A dialog box will appear when you install the button so that you can attach the macro
    to the button. Again, be sure you save workbook Dashboard as a macro enabled workbook.

    Please Login or Register  to view this content.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Registered User
    Join Date
    02-16-2018
    Location
    Lagos
    MS-Off Ver
    MS Office 2016
    Posts
    25

    Re: Copy and Paste Cell Values between Workbooks on Separate File paths

    Thank you JLGWhiz, however, there are formula in the "App sheet" and I tried the PasteSpecial as shown below but getting error "Unable to get the PasteSpecial property of the range class".

    Please what could be wrong?

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,100

    Re: Copy and Paste Cell Values between Workbooks on Separate File paths

    Hello oloyejawnson,

    Please try;

    Please Login or Register  to view this content.
    Regards.
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  5. #5
    Registered User
    Join Date
    02-16-2018
    Location
    Lagos
    MS-Off Ver
    MS Office 2016
    Posts
    25

    Re: Copy and Paste Cell Values between Workbooks on Separate File paths

    Thank you so much JLGWhiz and Winon! I was to able to achieve the result with the code below:

    Please Login or Register  to view this content.
    Appreciate the help!

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Copy and Paste Cell Values between Workbooks on Separate File paths

    Quote Originally Posted by oloyejawnson View Post
    Thank you JLGWhiz, however, there are formula in the "App sheet" and I tried the PasteSpecial as shown below but getting error "Unable to get the PasteSpecial property of the range class".

    Please what could be wrong?

    Please Login or Register  to view this content.
    If you used paste special the destination has to be on a separate line
    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/Paste from separate workbooks based on criteria
    By villa123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2017, 03:28 PM
  2. how to split data into different worksheet
    By fluffyvampirekitten in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2015, 04:37 AM
  3. [SOLVED] Open multiple workbooks based on cell values and copy and paste information
    By egemencoskun in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-24-2014, 04:37 AM
  4. Copy and paste data between tables in separate workbooks.
    By catigo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-17-2013, 04:41 PM
  5. File paths for referenced workbooks
    By bruce_facit in forum Excel General
    Replies: 0
    Last Post: 10-24-2012, 07:02 AM
  6. copy/paste source cell paths
    By Alicewonderland0 in forum Outlook Formatting & Functions
    Replies: 5
    Last Post: 05-17-2011, 02:31 PM
  7. Copy and paste to separate workbooks
    By Natechamb in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-08-2011, 06:43 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