Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Copy range worksheets to master workbook?

    Hello all,

    I have different workbooks that i want to set into one masterworkbook. so from the masterworkbook i want to run a macro that loop trough a selected folder where my workbooks are stored and copy some cells.

    It has to copy cells like this:

    C4 to A7
    I6 to worksheet2 (masterworkbook ) AI7
    I7 to worksheet2 (masterworkbook) AJ8
    K6 to worksheet1 (masterworkbook) AJ7
    K7 to worksheet1 (masterworkbook) AJ8.

    loop trough files and always add 1 row to copy the cells in. so i6,i7,k6 and k7 are always the same cells so is C4 on the worksheets



    thx in advance for the help

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Welcome to the Lounge as a new poster.
    It would be a great help if you could attach a sample file or two, then we could add the code and post it back.

    zeddy

  3. #3
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    here you go
    Attached Files Attached Files

  4. #4
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    anyone ? sorry to spam this

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Will post my solution tomorrow - need to do some test runs first.

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..what about 'Accessories' data? Include that too?
    ..how many files do you expect to process?
    ..would it be a maximum of 16?

    zeddy

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi peterroq

    See attached sample files.
    Copy the files into a folder of your choice, for testing.
    Open the Masterworkbook, with macros enabled.

    A calendar has been provided to allow you to use file dates as part of choosing which files to include for processing. This can easily be removed if not required.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2016-03-25 at 12:37.

  8. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    peterroq (2016-03-26),RetiredGeek (2016-03-25)

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Very nicely done!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    zeddy, thx a lot man. i posted sometimes on other forums but this is the best i found. if i have some other problems i will def come back here

  11. #10
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Zeddy,

    i appreciate your work. But for this file i could use a extra feature .

    If you look at the different sheets ( colour, mono, and others) you have row 3 with different models in it.
    i see you are doing an import off the files to the sheet data. is there a way to see if there is a match between the data sheet with the other sheets ( inkjet, colour, mono) and then copy row the QTY to the desired column of that match?


    in easy words.
    you have on the DATA sheet a colum with name QTY and a Column with name SAP code.
    look if there is a match SAP code with other sheets ( row 3) and then place the QTY in the column off the founded match.

    if HL-L8250CDN is found on DATA sheet then copy QTY into the column off HL-L8250CDN ( on sheet Colour)

    I hope you understand like this?

    As you will see there is not a exact match but only the numers are exact.
    Attached Files Attached Files
    Last edited by peterroq; 2016-05-31 at 05:47.

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi peterroq

    It could easily be done if the SAP codes used in the Sales datafiles matched the SAP code in other sheets ( row 3).
    As you said, there is not an exact match. For example, in your posted files, we would need to
    In date file: >> in Master file:
    HL1212WRF1 >> HL-1212W
    MFCJ480DWB1 >> MFC-J480DW

    HLL8250CDN >> HL-L8250CDN
    DCPJ562DWRF1 >> DCPJ562DW

    It would be simple to remove spaces and the "-" chars for cross-matching, but that still won't give us a 'rule' we can use for matching the codes.

    If the Sales Rep uses a dropdown to select a SAP code in the sales file, then we could use the same dropdown values in the new Master Workbook that processes the Sales.

    zeddy

  13. #12
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Zeddy,

    And if i change the names to the SAP codes? So i put in the mono laser sheet hl1212wrf1. Can you fix it then wihout case sensitive? I can change the reference into the exact SAP code?

  14. #13
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    we are using a dropdown menu for the workbooks. but in the master workbook it's product name and not SAP code. I can change the product code to SAP code in the masterworkbook if needed. it's not Always the same masterworkbook that we use ( the product names can have a different position but normally the same row 3) but i guess that is no problem to find a match string in row 3 and place the QTY in the correct row and column with the correct dealer? i already needed 3 times and i changed the macro a bit so everything is in the correct column . i hope you can fix it when i set the masterworkbook correct with SAP code

    thx in advance zeddy
    Last edited by peterroq; 2016-06-01 at 09:42.

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi peterroq

    OK, unzip the attached sample files to a folder of your choice (I suggest a new test folder, just for these sales order sample files).
    Copy the attached master file to another folder of your choice.
    Open the master file, select the folder where you put the sample order files, and click the [Start] button to process the files.

    1. I made all of the report sheets in the master file same layout i.e. providing for 16 'Customer' rows.
    2. Row 1 on these report sheets have 'hidden' formulas which are used to get the 'count' values for the SAP products.
    (these formula cells are 'hidden' by applying the custom number format of ;;; (that's three semicolons). This format makes the cell 'invisible and non-printing.
    3. The formulas to get the qty counts (hidden in row 1) assumes that the order form codes used in the sales datafiles match the codes used exactly as in the product row 3 of the report sheets.

    Run the file with the sample datafiles as attached, and then report back if this works for you.

    zeddy
    Attached Files Attached Files

  16. #15
    New Lounger
    Join Date
    Mar 2016
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    i will test now

Page 1 of 2 12 LastLast

Posting Permissions

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