+ Reply to Thread
Results 1 to 14 of 14

Copy Active worksheet data and paste in closed workbook in specific sheet

  1. #1
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Post Copy Active worksheet data and paste in closed workbook in specific sheet

    Hi
    Seeking someone help to create VBA code. I have data in one worksheet (Active worksheet)

    starting from B5 (right column not fixed, end of row not fixed)

    I need VBA code to open specific folder ( desktop) and last modified Excel file, in last modified Excel file, I need to paste this data on Sheet "TB" on row C5.

    After completion of pasting,

    I need to rename the excel file with "-1"( example if file name is "sample", I need to rename as "sample-1"

    Please help me to create VBA code

  2. #2
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    I found the below code to find latest file in folder and its working fine for me

    Can someone help me to make little changes as per my above requirement,

    I have data in one worksheet (Active worksheet),starting from B5 (right column not fixed, end of row not fixed) i need to copy this data to latest modified file sheet "TB" opened with the below code. once the copy process done, the latest modified excel file to be renamed with +1. Please help


    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    From a few examples code that I gave you,
    I think you should try to do the above situation by yourself .

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    This will give you an idea...Will let you figure out the error trapping if no files exist!
    Please Login or Register  to view this content.
    Last edited by sintek; 05-24-2020 at 06:44 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    Hi Sintek. Thank you so much for your help

    I am getting runtime error "76" at the below line
    For Each File In Fso.GetFolder(Path).Files

    I am changing path at this line \Desktop\FolderName\. Please correct if i am wrong

  6. #6
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    Error in your path....
    Like I said change path to where you files are stored...

  7. #7
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    Hi Sintek i tried with existing code and few lines of your code its working fine. But while saving the file i am getting error on the below line. Can you please help me to correct.


    .SaveAs Fso.GetBaseName(Latestfile) & "-1"



    Please Login or Register  to view this content.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    My code works fine...You must get your actual path Right click on folder...properties...location is path...
    Don't wanna mess with other persons code...get them to fix for you...

  9. #9
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    I got it Sintek, i changed the path your code is working fine.

    thanks you so much for your help
    Last edited by SARAN89; 05-24-2020 at 08:46 AM.

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    in case no files in folder...Put this snippet...

    Please Login or Register  to view this content.
    After

    Please Login or Register  to view this content.
    And

    Please Login or Register  to view this content.
    before

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    Thanks Sintek found one more issue, after renaming the excel file, its by default saving in "Documents" folder instead the Folder Path given can you check that

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    You are never going to learn if you do not try ...
    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    05-01-2020
    Location
    CHENNAI
    MS-Off Ver
    EXCEL 2010
    Posts
    96

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    You are never going to learn if you do not try @ thanks for your advice

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,159

    Re: Copy Active worksheet data and paste in closed workbook in specific sheet

    All good...Tx for adding to rep +

+ 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 code to copy a specific sheet from a closed workbook to the active workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2017, 03:09 AM
  3. [SOLVED] vba copy specific sheet from closed workbook to active workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2017, 01:33 PM
  4. [SOLVED] copy specific sheet from closed workbook to active workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2017, 10:26 PM
  5. vba copy specific sheet from closed workbook to active workbook
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-09-2017, 02:34 PM
  6. [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
  7. Run Macro in closed workbook and copy specific coloumn in active workbook
    By shiva_reshs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2013, 03:43 AM

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