Dear Gurus,
I got the below code that it combine sheets from all the files in the folder copy to another workbook, the codes work well. But i want to copy specific columns from all the files, I tried i cannot able to get. Can you help me here please.
I think there is no need for attachment.
Please Login or Register to view this content.
Last edited by JaffarAhamed; 09-10-2023 at 12:04 PM. Reason: grammer mistakes
below is the column header should consider from all the files in the folder
otherwise the code could be the list where i can add column headers in other sheetPlease Login or Register to view this content.
Have you considered using Power Query? If you wish to post some sample data, with a mocked up solution, I may be able to demonstrate. Make sure to keep the number of records for each file to a minimum (8-10 records each).
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Dear Alan, Thanks for your quick reply. I want this in VBA, but it came in the power query. Can you please assume that there are 25+ files in the folder in other path, and result should come in the sheet1 in the active workbook. If you still want sample data then i can. And every files having one sheet to combine. Thanks
Last edited by JaffarAhamed; 09-10-2023 at 12:10 PM. Reason: grammer
Cannot assist with VBA. If you want a PQ solution then you need to upload files as there is no such thing as a generic solution in PQ. Otherwise, good luck.
I have moved this from the Excel General subforum. If you want to change the subforum, please click Report and request a move. Do not just post a duplicate in another subforum.Originally Posted by JaffarAhamed
Jeff
| | |·| |·| |·| |·| | |:| | |·| |·|
Read the rules
Use code tags to [code]enclose your code![/code]
Hi Jazzer,
My Apologies. I dont know how to move the post from subform. Can you help this post.
I am sharing the multiple files and the file with the code.
Basically i have files in a folders, and columns in all files are random, my code just copy the sheet and paste in to the active worksheet and so on for other files in the folder.
But what i actually want is, copy and paste activity should follow the below column order for all files.
Serial No.
Country
Domain Owner
Other communications
Overall status
I have attached the file with code along with sample files.
I hope you understand, please let me know if unclear. thanks
This will not import the data from the workbook;
1) doesn't have "owssvr" sheet.
2) any column heading missing/doesn't match to heading in destination sheet.
Please Login or Register to view this content.
Hi Jindon,
Unfortunately i got 'Mismatch error:13' for the below line
Please Login or Register to view this content.
Are you running from the file I attached?
That error occur when you have no headings in destination sheet.
change "test" sub to
Please Login or Register to view this content.
Hi Jindon,
I have added the column header still is not working.
for the second piece of code, i getting runtime error. It says problem with one or more formula references in this worksheet.
Is the code search for random columns in the attached sheet?
I have tested the code before I posted, so it should work.
Hi Jindon,
Nothing required, your code works now. I have copied your code in to new workbook, it works now.
Is it possible to have list of column headers in other worksheet, instead of going to module and add? you amend in your first code. thanks so much for your help.
In order to avoid any misunderstanding, you'd better to attach the workbook with the header somewhere and the sheet that receives the data.
You cannot move a post. I moved it for you. In the future, do not duplicate a post. If you would like us to move a post, click "Report Post" under your post and request that we move it.
Hi Jindon,
PFA, i have added the 'Column header' sheet. Thanks again for you help. I can add or remove any column headers, based on that it should select the columns in base files.
try change one line in "test" sub
toPlease Login or Register to view this content.
Please Login or Register to view this content.
Hi Jindon,
I can able to get output only for first 3 workbooks not for remaining workbooks, there could be more than 30+ files in a folder. But the macro picking only 3 files. Do you know why?. i just followed the same procedure for all other files. I do not have any clue. please help. Thanks.
Like I said earlier in post #9
Most probably other files would match to above.
If you upload a workbook that is not imported, I can tell.
1. The sheet name for all workbook is "owssvr"
2. i have changed the columns in code based on my requirement, and ensure the columns are matching both in code and in all the files. I have done a check for one column by giving extra space to know whether it copy or not. But it is not copy all other columns because of one column error. I thought the code would pick other columns without the wrong column.
Last edited by JaffarAhamed; 09-13-2023 at 03:15 AM. Reason: Grammer
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks