code to get selected data from different files to one workbook

Hello experts,
This is my second stage of the project. There are multiple files in a folder. Each file has multiple sheets of data of which the required sheets are only 2 of them namely B2B abd B2B -CDNR. I have a working sheet in a different location. I want the code to extract selected rows of data from each file and paste it in the working file (Destination folder) with just one condition. When the code extracts the selected rows data from B2B-CDNR sheet, if any, in the sheet then the figures of that sheet should be negative figures in the destination sheet. I have created a table of plan how to get the extract as suggested by one of the members along with the expected result sheet. (2B Combined). In the module I have inserted a few lines to make it much easier. Hope someone understands my requirement and shares a full single code. As there are multiple sheets to extract from I am sharing only 3 sheets. The loop function will extract the data of the other sheets hopefully.
Thank you in advance.

That is very cool Marc L. Everything explained in just a few lines. Thanks man. Next time I will try to explain like that. My requirement explanation in the steps sheet is way to long. I hope there is someone who can follow the above steps and help me create the code. This time I have not shared any module. I can create a 2B Combined sheet if it doesn't exists but getting the filter data from the source sheet to the destination sheet one below the other of all the files in the address link would be very tough for me.
There is one more difficult part. B2B can be copied as it is but B2B= CDNR columns you will have to avoid column D and get the amount values in negative figures.(columns F, J to N)

Hey guys, I need to consolidate source data workbooks to my 'macro' MAIN.xlsb workbook :

1. Create B2B Combined sheet in the destination workbook if not created.
Headings to paste in the B2B combined sheet.
2. Search for all source data workbooks in the address path mentioned in the destination workbook steps sheet cell A1.

3. Open a source data workbook and copy data only so without the header row selected rows containing Rajesh India* in B2B sheet and paste to required columns
Open B2B-CDNR worksheet in the source sheet and paste in B2B Combined data sheet to required columns with amount figures converted to negative.

4. Close the source data workbook. Process the same for all other source data workbooks.

5. in column W enter the state code which depends on the name of the source sheet (8th and 9th character in the file name)
6. In column X combine the values of columns W, A , C and I.
workbook and source sheets shared in post #1 along with the header titles to paste, look up array and converted state code array.

1. There are multiple files in a folder from where the data is to be extracted. The address link of that folder to be pasted in A1 Cell of the steps sheet. I have shared only 3 files in the thread. There can be any number of files in the folder from 1 to 25 all containing B2B and B2B-CDNR sheets along with other sheets in the same order.
2.From the B2B and B2B-CDNR sheets I want to extract only rows which contain Rajesh India* in column B to the 2B Combined sheet.
3. The multiple files in the folder from where the data is to be extracted contains 2 digits in the 8th and 9th character. They are the state codes which the code will match the numbers to the state code and print in column W of each file in the destination sheet after B2B and B2B-CDNR sheets data are combined from the first row to the last row.

If you download and copy the 3 files to a folder, you can paste the address link of the folder containing the files in cell A1. This way the code will check the address link open a file extract the required data and close it again. Then It will open the next file and so on......

The code will have these lines to convert the number to state code.

Please Login or Register  to view this content.

The code to create the 2B Combined sheet with headings is like this.

Please Login or Register  to view this content.

The rest of the code is not in my league to write.

Marc L. In the source book(Workings) the 2B Combined is the expected result.

From the B2B sheet (files folder) copy all the filtered (Rajesh India*) columns and from B2B-CDNR copy all but one column but in the same order as mentioned in the result sheet with negative figures. (columns F and I to N).

Only the difference will be 2 additional columns in the destination sheet the values of which will be derived by the code (Column W and X).

From B2B sheet all columns to be pasted to 2B combined in the same order. But from B2B-CDNR sheet columns A B C and D to be pasted in 2B combined sheet under A B C D and columns F to W to be pasted to columns E to V in the 2B combined sheet. From the B2B-CDNR sheet the columns G K L M N and O to be pasted in 2B combined sheet in negative figures. Please see that the dates format in column E and P are the same when pasted to destination sheet.

Originally Posted by RAJESH SHAH

Please see that the dates format in column E and P are the same when pasted to destination sheet.

Not explained in your initial post neither in your post #4 ! You must better elaborate …
From both source worksheets or only from B2B-CDNR ?
What is the expected result ?
As again the exact result according to your source workbooks is still expected …


Another confusing point :

Originally Posted by RAJESH SHAH

3. Open a source data workbook and copy data only so without the header row selected rows containing Rajesh India* in B2B sheet and paste to required columns
Open B2B-CDNR worksheet in the source sheet and paste in B2B Combined data sheet to required columns with amount figures converted to negative.

So it seems only B2B needs to be filtered on 'Rajesh India' so not B2B-CDNR

but in another post you wrote

Originally Posted by RAJESH SHAH

2.From the B2B and B2B-CDNR sheets I want to extract only rows which contain Rajesh India* in column B to the 2B Combined sheet.

clearly meaning both worksheets need to be filtered

but as in a later post you wrote

Originally Posted by RAJESH SHAH

From the B2B sheet (files folder) copy all the filtered (Rajesh India*) columns and from B2B-CDNR copy all but one column but in the same order as mentioned in the result sheet with negative figures. (columns F and I to N).

then again it seems only B2B needs to be filtered …


If someone shares a VBA procedure but with a wrong guessing interpretation are you able to fit / modify the code yourself
according to what you forgot / misexplained ?‼
If not why don't you create at least a thread with a crystal clear elaboration of the need
with nothing missing neither nothing to guess and far above all with an exact expected result according to the source data.

If you again still continue to create such threads don't be surprised to not receive help anymore …


Last point :

Originally Posted by RAJESH SHAH

1. Create B2B Combined sheet in the destination workbook if not created.
Headings to paste in the B2B combined sheet.

Why over complicating again ?!
According to the workbook purpose why not starting from an already created result worksheet
with the exact headers row rather than hardcoding them ?

Maybe the day when you will create the initial post at the level of what any Excel forum expects for

but

as this part is already demonstrated in your threads
so in case you well share what here I expect for
then I will start only from the already created result worksheet which simplifies the VBA coding
which will be an 'one shot' code …

Hello Marc. Thank you for taking interest in my query. I am sharing the expected result sheet which is in the working workbook again.
1. When I press the button in the workings sheet which is this, the exact extracted data result should be 2B combined sheet.
2. The result is extracted from both the sheets B2B and B2B-CDNR.
3.The 2B combined sheet is created by the code with the extracted data.

Still data not matching the source data attachment, missing answers : I can do nothing, I'm out, good luck …

Marc. I am so so sorry. I wrongly edited the data properly before sharing it. I have filtered the rows containing Rajesh India* in the source folders and also corrected the destination sheet result. Edited the workings sheet with the correct expected result.

Doin it manually there is a possibilty of making mistakes. The destination sheet sheet contain all the rows containing Rajesh India* in all the source sheets. Once again I made one mistake in copying and pasting manually by adding Rajesh Industries in the destination result sheet. The result should have rows containing text Rajesh India* only.

A1 is the address link where the 2B folders are available to extract from. All the folders have an xlsx extension and there are only those folders from which the data is to be extracted.

If it helps I have a similar code which extracts data from an address link and combines the data of all the files in the folder. The problem is the sheets are different in this code and it combines 4 sheets from each xlxs file wheras in this case the sheet names are different and only 2 sheets data is combined.

When extracting data from the source sheet to the destination sheet sometimes the data format changes from dd-mm-yyyy to mm-dd-yyyy. So, I mentioned to get the correct date format. In the result if the dates have not changed the format then it will be good or else I have to post again if I have a date issue.

How it is possible when copying data - rather than 'extracting' - as an Excel date stays a date versus a text stays a text
and as all about Date versus Text is yet explained in your threads so nothing to do …

As you wish. If you want the 2B combined to be in the workbook and not created with the code, it will do unless I get the result correct.

So, I will have to create the 2B combined sheet with headings in advance and then run the code. Right ???

Right as the VBA procedure must be located within the result worksheet module …

Originally Posted by RAJESH SHAH

code to get selected data from different files to one workbook

According to your thread title and according to your post #18 source data workbooks :
does your 'selected' means in fact source data already filtered ?!
In that case only the already filtered source data worksheets will be copied to the result worksheet ?

No. The data was filtered by me to show you the rows to be extracted from them. All the files in the folder will have full data and will not be filtered. The code has to open each file, filter the data, select the rows with the criteria and paste them in the 2B combined sheet and finally close the file.

Right as the VBA procedure must be located within the result worksheet module …

Yes, The button will be in the steps sheet and the code module will be assigned to it.

Phew!!! That is great. Now that you have understood the query, I will leave it to you now. See you tomorrow. Good Night.

According to the initial post attachment

• once satisfied with my VBA demonstration you should remove the useless module you have renamed.
  
  
• Sheet2 is used to lookup the States Codes - nothing hardcoded - and its column F lists any non matching rejected workbook.
  
  
• A starter VBA demonstration to paste only to Sheet3 (2B Combined) worksheet module :
  

Please Login or Register  to view this content.

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

@Marc: Trying to understand your code (nearly there) , this statement gave an "error" - wrong random value

Please Login or Register  to view this content.

-

@Marc: Running from the file posted in post #1 still gives the error.

And as for "bad reader trap": as it took 30 posts for you to understand the requirement, you are in no position to criticise others: and yes, I did post the code in the sheet module as INSTRUCTED.

Good morning everyone,

I tried following the instructions:

• I took the original files from post #1;
• I inserted the code proposed in post #37 with the changes;
• I changed the contents of cell "A1" in the "Steps" sheet to the path where I saved the files;

and everything worked correctly on my pc.

Bye

Tested after a restart with original file and still the same issue: USEDRANGE is known to be "vunerable" to errors but I accept it might be my Excel 2010,

My own "VBA for Dummies" version runs without any problem so i'll stick to the "KISS" VBA I use!

And I see little merit in putting code in the worksheet vs standard module - what are the advantages, if any ?.

Just to clarify: it was the FIRST "R" that was incorrect: all others were OK and finally results were totally correct so my m/c or Excel "glitch". QED.

Another view .....

As a general rule, the sheet modules and the ThisWorkbook module should contain only event procedures, and nothing else. All other code should reside in regular code modules. Some would argue convincingly that private procedures used only by the event procedures in a module should be contained in the same sheet/thisworkbook module in which they are used. There is no practical limit to the number of modules allowed in a project, so you should organize your code into multiple modules, based on the type of functionality provided by the code.

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com
Cordially,
Chip Pearson
Excel MVP 1998 - 2014
Pearson Software Consulting, LLC
www.cpearson.com

Originally Posted by Marc L

Grazie Max !

But I'm not sure what exactly means « with the changes » ?

Excusez-moi, ... by the word changes I meant applying your three tips.

As far I can judge from the VBA posts I have viewed , you are the only VBA coder who uses worksheet code in this way - which does not make you wrong .....

As you another POV ...so let's leave it at that.

Marc. As discussed earlier, I have inserted the 2B Combined sheet manually in the destination workbook. I assigned your macro to the button in the steps sheet. I pasted the link address of the source files in cell A1. When I play the code I got an error 424. Did I miss anything which I am not aware of.
Object required error at this line UsedRange.Offset(1).Clear

I think I have missed something to understand. I inserted the code is sheet3 as suggested. When I run the code it is running without any error, but, the result sheet is empty.

Did you put Marc's code in the "2B Combined" worksheet ?

Yes. John Topley. At first I inserted it in a module. But later when I read the post of Marc I noticed that he had asked to put his code in the 2B combined worksheet. Now I am not getting any data in the 2B Combined sheet at all even when the code has run without any error.

Look to see if data starts at a lower row e.g 42 as I had a problem that the code did not place the results from row 2 as expected.

I think I missed something which I am not aware of. If maxpit could get it right that means I have missed something.

Originally Posted by JohnTopley

Look to see if data starts at a lower row e.g 42 as I had a problem that the code did not place the results from row 2 as expected.

Which data source or destination ? The source data all the sheets start from the 7th row and destination sheet where the data is to be extracted in row2.

Look at "2B Combined"and just scroll down

The whole sheet is blank except for the headings.

Are you sure you selecting the right version of the code ? delete the code from the "module" if it is still present, close Excel and restart Excel.

and/or shut down your computer, reboot and then open Excel.

Or .... try this... change code to

..

Please Login or Register  to view this content.

John Topley. Are you getting any result in the 2B Combined sheet.? I deleted the code from the module, closed excel and restarted the computer. I once again opened and pasted the code in the 2B Combined sheet and still I am not getting any result in the 2B combined sheet.

Originally Posted by JohnTopley

Or .... try this... change code to

..

Please Login or Register  to view this content.

After replacing with the above line too, I am not getting any result.

See post #66 and apply this code. I get results in all cases BUT with the code change, they start correctly at row 2 in "2B Combined".

Hi RAJESH SHAH,

did you enter the path of the files to be imported into A1 of the Steps sheet?

Maxpit. Of course Yes. I did that.

I played the code using F8 and I think this line has to paste the data of a selected sheet which it is not posting the data. F = Dir$

The attached has results running Marc's macro with my minor change. I have'nt a clue why it does not work.

Use F8 and stepping through the code should identify the issue.

After deleting the data in 2B combined data in your workbook and changing the address link, I played the code and it is displaying only the headings in the destination sheet.

Please Login or Register  to view this content.

should show the next file to be processed so if files are 02,06.07 (state codes) it will show 06 then 07 Xlsx Files

It is finding the files going to the designated sheets closing the file and everything is fine. But no data is copied to 2B Combined sheet.

Then there is somthing radically wrong in what you are doing as I have run Marc's code numerous times without a problem.

In w/book I posted try running "Create2BCombinedSheet" macro.

As I already said I am missing something. Once again I Will try again from the start and check and revert back.

No idea why it is not displaying the result in 2B Combined sheet. I downloaded the file from Post #1 and retested that file too. But the code runs without any error but no result.

PHEW!!! Finally got it right. My Bad. I was checking with the original data and I didn't replace the criteria *"RAJESH INDIA*".
Thank you Marc L. I really appreciate your unconditional support. Thanks man.

One thing I have to keep in my mind is that the steps sheet should always be sheet1 and State code should be sheet2. No matter what ever the 2B combined sheet is the code will work. Right Marc L..

This is the beginning of a new project and I have already created 3 modules earlier. How will I call this module as the code is inserted in a sheet.? If you can please replace the sheet1 and sheet2 with actual sheet names so that I can insert the code in a module and call it along with other modules without any issue. There are at least 20+ more modules to create in the whole project and this is just the 4th module. If you could convert the code compatible to play in a module I would not have a problem in future.

Do you mean ...

Please Login or Register  to view this content.

Macrox are your "other" macros

Demo1 has to be defined as

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

so macro "MAIN" is the one which calls ALL your other macros

Marc L. The code is super. I had 10 files with me to test and it took about 15 seconds to extract the required data from them. 25 files will take around less than a minute to extract from. I went through the code and try to understand it using F8. I need 2 things to understand from the code.
1. why are these lines added I have no idea. ?

Please Login or Register  to view this content.

2. Which line in the code is extracting the values for column X.?

Please Login or Register  to view this content.

[/CODE][/CODE]

I hope it will not be a problem if I call the 3 modules in the beginning of the code and the rest of the modules at the end of the code. That time will say when I have created the last module in the project. (As suggested by John Topley)

Originally Posted by Marc L

Well, well, well, as written in post #37 :

Yes post #37 is perfect.

Originally Posted by JohnTopley

Do you mean ...

Please Login or Register  to view this content.

Macrox are your "other" macros

Demo1 has to be defined as

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

so macro "MAIN" is the one which calls ALL your other macros

Thanks John Topley. I will try out once this is completed.

PLEASE do NOT put unnecessary quotes in your replies: they are clutter and not required.

There is a glitch in post #37 code, correction in progress …

The speed is very good. It is faster than my expectation.
Sorry, I wanted to ask about the column W and not X. Which line prints the value in column W ? (The one which is printing the Location state code).

This one

Please Login or Register  to view this content.

given

Please Login or Register  to view this content.

is the State

obviously cannot work out that column W is column 23

Okies. Now I understood it. Thanks John Topley.

But still trying to understand this line where it clears the data of columns F and the following columns in the State code sheet. Is it needed ..??

Please Login or Register  to view this content.

.

Originally Posted by Marc L

There is a glitch in post #37 code, correction in progress …

What glitch..????

Did you actually look at post #37 ? Perhaps code highlighted in RED

Post #37 is perfect. I am already using it without any issues.

with the change ? If not, I recommend you add the change.

I checked and compared the code in post #37 and I have rightly used the edited Post #37. Thanks for the recommendation.