Combining Data from various workbooks

[kchm_2000]

Hi,

I have several columns of data that I retrieve from the worksheet CS from the various workbooks known as Values. These values are consolidated into the Combined Sheet Found in the Values Combined workbook. The folder name which files that need to be combined in are found on cell I2. The end result has also been placed in the Values Combined workbook. Thanks for the help anyone can provide.

[arlu1201]

Try this

Please Login or Register  to view this content.

Thanks to GeneralDisarray. Found some code from one of his earlier posts and modified it for you.

[Kyle123]

Hi arlu,

Good post, in general, try to avoid copy and pasting however, it gets messy and it's slow.

Consider the below:

Please Login or Register  to view this content.

[arlu1201]

Thanks Kyle123, a good learning indeed.

[kchm_2000]

hey the codes works fine. however I have a merged cell and text just below. However there a space between the data I need and the merged text box. Is there anyway to avoid capturing the unwanted cells? Like stop copy when it sees a space and then proceed on to the next workbook? I have attached a file with the relevant formating. Thanks for your help

[arlu1201]

Just to clarify, you need the data to move to a different workbook when a space is encountered? or the next section of the file, after the space?

[kchm_2000]

I just need the data between the heading and the space, nothing else afterwards from each worksheet. Just to confirm I have highlighted the region i need. Thanks very much for ur continued help

[arlu1201]

I have another question (sorry, i just need to know so that i can give you a good solution).

If the data crosses the first sheet and encounters the space, you would like it to move to the next tab. Is the format in the next tab the same ? Can the data directly be copied over to the 2nd row? Or should a sheet be inserted and the headers inserted too?

[kchm_2000]

same tab as all have the same headings thanks a lot

[arlu1201]

But on the same tab, the space comes in the way. Where should the data be put? After the space?