VBA - Excel - Help! RRS feed

  • Question

  • File Description:

    Copying data from outside files into a spreadsheet and creating a new sheet for each section using a macro (done this bit)

    The problem I have is that there are repeated files in the Control page which need to be copied and I want to create them all at the same time rather than having the macro close and re-open the file multiple times. I want it to just paste them all in one go rather than copy and paste it multiple times. I would rather it could find the parts in the document that needed it to be pasted into and then have them be pasted and not copied multiple times.

    I would preferably like an answer by tomorrow morning. So please if you have any suggestions feel free to help me!

    I want to find if a certain cell repeats itself in a range of cells for example

       A   B  C

    1  Z  Y  X

    2  W L  U

    3  T  S  R

    4  Q  P  O

    5  N  M  L

    It would then tell the spreadsheet, not the user, that there is a copied value in B2 and C5 this would mean that the path directory allocated to the value "L" needs to be pasted twice in the relative place in the main document.

    If needs be I will be able to provide the original file that the macro to copy the parts into the document is.

    Contact me via email or Skype below;

    miles.fraser @ contractor . xlgroup . com

    MrAserFiles (Skype User ID)

    Wednesday, August 7, 2013 2:56 PM

All replies

  • Feed each cell value into a Collection object or Dictionary object.
    Duplicate values are automatically rejected.
    Jim Cone
    Portland, Oregon USA
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Thursday, October 20, 2016 3:03 PM
    Thursday, August 8, 2013 3:41 AM
  • The thing is i still want the duplicate values to still count as a value because they are still used as a file name in the full document. I am taking files, opening them, copying a bit of it, pasting it into a larger document and then closing the file again. I want to make it so that i can copy the parts where the file repeats itself without closing and reopening the file mutliple times.
    • Proposed as answer by James Cone Thursday, August 8, 2013 11:46 AM
    Thursday, August 8, 2013 9:55 AM