+ Reply to Thread
Results 1 to 14 of 14

copy data from closed files to multiple sheets and create report

  1. #1
    Forum Contributor
    Join Date
    04-11-2021
    Location
    TRI
    MS-Off Ver
    OFFICE2016
    Posts
    200

    copy data from closed files to multiple sheets and create report

    hello
    I have two files REPORT1, INV1 and the result in file REPORT in sheets(stock,data,report) sheets stock should brings all of data from file INV1
    and sheet data should bring specific data for headers( GOODS,TYPE ,PR) and the last column contain values but the last column every time changes in location and the sheet report should create all of data based on sheets (stock, report)
    note : I would create all of data in all of sheets in file Report automatically when open file without open rest of files or press button
    by the way all my files in the same folder
    for more explanation i put some comments for each sheet
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: copy data from closed files to multiple sheets and create report

    Here is an option. Run the code from an empty file. You will be prompted to select the two files.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    04-11-2021
    Location
    TRI
    MS-Off Ver
    OFFICE2016
    Posts
    200

    Re: copy data from closed files to multiple sheets and create report

    thanks but it gives me error " application defined or object defined " error in this line
    HTML Code: 
    Last edited by KalilMe; 05-03-2021 at 08:35 AM.

  4. #4
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: copy data from closed files to multiple sheets and create report

    Try:

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-11-2021
    Location
    TRI
    MS-Off Ver
    OFFICE2016
    Posts
    200

    Re: copy data from closed files to multiple sheets and create report

    thanks but it shows odd values in col F in sheet report after run macro it doesn't show marks right and wrong it shows letters O , p
    and you forgot the point in sheet stock should compare with sheet report if there are brands not existed in sheet report then should highlighted by blue color in sheet stock

  6. #6
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: copy data from closed files to multiple sheets and create report

    Here you go

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    04-11-2021
    Location
    TRI
    MS-Off Ver
    OFFICE2016
    Posts
    200

    Re: copy data from closed files to multiple sheets and create report

    thanks that's better , but I have some questions do you have way to do that without select the files from the browser ? just make the files are closed and import data directly just when open the file bring all of data like in event workbook open also I see the code is slow ,may you make it faster? also I want make the same border and formatting when create columns stock and case , plus, deficit like the rest of columns especially make the font in center not the left .

  8. #8
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: copy data from closed files to multiple sheets and create report

    See if this works for you

    Please Login or Register  to view this content.

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

    Re: copy data from closed files to multiple sheets and create report

    @maniacb

    Just a heads up...
    Please Login or Register  to view this content.
    The above declares...
    Dim lc as Variant, lr as Variant, i as Variant, lrr as Variant etc etc...Only u is actually declared as long....
    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!!!

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: copy data from closed files to multiple sheets and create report

    @Sintek, thanks for the heads up. Is there a way to declare many items as long?

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

    Re: copy data from closed files to multiple sheets and create report

    Check your locals window for declarations...
    In VBA, as you've observed, any variable declarations that aren't immediately followed by As will be Variant.
    Have to declare individually...
    Please Login or Register  to view this content.
    Can also use type declaration characters...Old School...
    https://docs.microsoft.com/en-us/dot...ype-characters
    Please Login or Register  to view this content.
    Last edited by sintek; 05-04-2021 at 02:45 PM.

  12. #12
    Forum Contributor
    Join Date
    04-11-2021
    Location
    TRI
    MS-Off Ver
    OFFICE2016
    Posts
    200

    Re: copy data from closed files to multiple sheets and create report

    @maniacb thanks but COL E when bring value it doesn't located in the center it is still in right
    and the code became very slow

  13. #13
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: copy data from closed files to multiple sheets and create report

    Not sure what else to do about the speed, your main file must be big. for alignment, change

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    04-11-2021
    Location
    TRI
    MS-Off Ver
    OFFICE2016
    Posts
    200

    Re: copy data from closed files to multiple sheets and create report

    @maniacb not a huge data the same attached files and I tested how does it takes from time it gives me 6.81 sec normally the codes take 0.3 or 0.4 but i think this value is a big

+ 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 specific sheets of multiple closed workbooks to new workbook
    By JACK JOUSH in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-16-2020, 12:47 AM
  2. Replies: 0
    Last Post: 04-21-2014, 03:42 PM
  3. [SOLVED] One Report, create multiple sheets, save each sheet in a seperate excel file
    By sammymalta in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-04-2013, 07:12 AM
  4. Replies: 2
    Last Post: 10-08-2013, 02:58 PM
  5. Create multiple sheets, copy data send a mail
    By raasukutti in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-22-2012, 11:48 AM
  6. Copy Data from Closed File to Existing File
    By srikanthbenoni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2012, 08:36 AM
  7. Copying data from multiple files (with mult. sheets) into one file and create a graph
    By Macedonia in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-30-2010, 07:37 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