Loop thru folder of workbook using cell value and copy/paste values beween files

    Please bear with me as I am very new to VB and currently stumbling my way through.

    I have a set of data on a sheet with a date in column A (static), various locations in colum B, various repeated sub-headings in C (repeated for each location), and then some column headers where the data is populated in the relevant matching cells below (in line with each location and sub-heading).
    What I need is a script to open a workbook for location 1, find the tab relevant to the sub-heading, once open find the date in the relevant row, match the column headings in the first sheet with the second, and enter the data accordingly. Obviously if someone could help on the first line I can replicate per sub-heading, workbook etc. Each workbook populated needs to save and close, then move to open the next location workbook and repeat.
    The data in the first sheet is a collation from a report that will be run daily to then populate all files/sheets when ran.
    Thanks

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Visual Basic locate and paste


    You'll need to attach a sample workbook. One with the data as it's captured daily (as you indicated) and a sample of what you expect the results to look like. All of your requirements seem pretty straight forward but without sample data, it would be impossible to come up with a working solution.

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    Hello freddi0,


    Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:


    Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your [COLOR="blue"]thread content or overall objective[/COLOR] using ONLY search friendly key words. That is, your title use as search terms would return relevant results.


    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different


    Your title of "[COLOR="red"]Visual Basic locate and paste[/COLOR]" does not describe your thread or objective and is not helpful to those searching the forum for a solution to a similar need.


    [COLOR="darkred"]Please note the change to your title, which is based on the objective stated in your thread.[/COLOR]


    If the new title still does not accurately describe your thread you may make further edits as needed per the above guidelines.

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    forum.ozgrid.com/index.php?attachment/38434/forum.ozgrid.com/index.php?attachment/38433/

    Right, I have put some anonymous data in to try and give some indication of the problem. A report will bew placed into the 'data' tab of the template which will the pull the relevant figures into the 'template' field by look-ups or formula etc. In here you will see highlighted row headers that are needing to be copied accordingly. The scrgipt will have to look up 'London', open the London file (sample attached), check the template for the 'sub', e.g. Jones, choose the 'Jones' tab in the London file. Then match the date, and populate the figures relevant to the highlighted row headers which will be named. The position of the rows is not static so it will not be possible to count in 'x' number and place the data, it will have to look up the 'text' in that box. Then it will move to the next sub, open the relevant tab, and repeat the data copy, etc. It will then save and close the file (London), and look for the next file (e.g. Leeds), and repeat until finished.
    As I said, if I can get one folder and sub then it will be repetition from there.
    Thanks

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    So where does it get the data for the headings to start with (London, Leeds, etc.)? Or is the sample you have in Template what it will look like at the start - IOW that you will fill in the dates/locations/names?

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    the data in the template draws from a different tab where a report will be dumped. There are different sorts and formulae to pull the data into the version that is shown. You can put random figures in here for now, all cells will be populated with figures, and it si these figures that need to go into the other workbooks/tabs/folders, repeated on a daily basis.
    I have highlighted the main columns needed, but as I said, they may not be fixed in the folders to be saved in

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    Please fill in representative sample data in the columns to be copied as this may impact how the data is captured (string, int, long, date, etc.).

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    sorry, should have said - the 'template' xls is where the data will start, and the 'London' xls is a sample of where it will need to populate relevant to date and heading title
    Thanks

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    One thing to make this simpler and less susceptible to errors if the workbooks change is to have the columns in the template match those in London (the a-n columns). Is that possible? If not, it will require a bit more work to ensure the correct data is copied from the correct source to destination column.

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    It is hoped that they would be in the same columns, but in order to 'future proof' the files this may not be possible.
    The headings will be the same though - if that's any help. Not forgetting that the destination will move down each day due to changing date.
    Had it have been just one folder to enter the date then I could have worked out some sort of Vlookup, but there are many folders with these tabs. Also, putting lookups in the cells would make the folder huge given the amount of data required.
    That is unless there is a script that could be used to open the folder, look for the next detination cells, enter a vlookup, save as values, then close?
    A I said, new to this
    Thanks

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    Used this with your sample London file. Please look at the code to try to understand what's going on. Tried to make it future proof.

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    Getting to look into this today, but getting a debug in the 'Main' at the folowing line

    If ThisWorkbook.Names(arr_Heading(0)).RefersToRange.Offset(lp - 1, 0) = "" Then 'first data column is empty so collect starting here

    Thanks

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    Ahhh, had another look at this and worked out what was stopping it.
    Thinks it's down to my explanation. It runs ok, but is the opposite way round to what I'm looking for. What is attached takes data from each of the location files (one for London, one for Leeds, etc....) and populates into the template. What I need is for the data that is in the template cells to populate into the relative Files (London, Leeds etc) bearing in mind that each files has a 'Smith' tab, a'Jones' tab etc.......
    One of the guys I work with says 'hats off' to you for the in-depth coding - which he will be grabbing a copy of!
    Hopefully I have managed to explain a bit more clearly, look forward to what comes next
    Thanks

    I've found that the problem usually lies inbetween the keyboard and the chair

    Re: Loop thru folder of workbook using cell value and copy/paste values beween files


    Guessing you nor he can see easily how to reverse the copy/paste logic? I don't know if I can get around to this today so if you don't see something by next Wednesday, ping me.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login