Hey everyone,
I'm wondering if it is possible to consolidate data from multiple workbooks into 1 workbook through VBA. Every day, client files are dropped on to our server and right now we're having to manually open each file and copy and paste the column we need into a master workbook. My vision is to have a macro on the master workbook that will go through each file in the folder, and add column B from each workbook to the master file. Does this sound like a possibility?
Should the data be copied one below the other or side by side?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Data should be copied one below the other from 2 row of 2 file onwards..
What do you mean by 2 file? 2nd sheet of each file?
2nd work book should be copied one after the other by eliminating first row of the 2nd workbook
So the headers are in the 2nd row and the data from the 3rd row should be consolidated?
from 2nd rows of workbooks should be copied to at the end of first workbook.
Yes i understand that part. Can you explain how your source file looks like? Do you have headers in row 1 or row 2? Does your data start in row 3? In the absence of a sample file, i am asking you these questions directly. Once you give me the answers, i can help you with the code easily.
LOL anto_01, you just hi-jacked my thread!
Arlu, thanks for the reply, ultimately I would like to transpose the columns into rows and paste them to the master file one row after another. That would really bring everything I'm trying to accomplish full circle. Transposing to rows isn't required, but it sure would be a good way to keep my master file organized. If I can't get the columns transposed to rows in the master file, I would need the columns side by side instead.
Sorry! Got into confusion!, I am so sorry.
VBA FTW, i am sorry i didnt realise that you were the original poster. I just saw the reply from anto and thought its from you.
Book1.xlsxGuys i need help, Please very urgent if some body could HELP make a macro ...would reallly appriciate it ..thx
What i want is in the amount field there are amounts ...idf there a 100 dr and 100 credit should get highlighted ....attaching a file for ur reffrence ...
Please Need this
kartikey...
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Book1.xlsxGuys need a matching macro,
Attaching a file for reffrence please check..
would really appriciate it thanks
kartikeyadawar -
Are you having issues creating your own thread?
yeah , please advise how to do so ...?
Thx
Click on Excel Programming subforum. You will find a button at the top called "Post new thread". Click on that and proceed.
deleted my own post
VBA FTW,
Try this code -
Copy the Excel VBA code
Please Login or Register to view this content.
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button
Thanks Arlu, this works perfectly!!!! Thank you so much, you are a blessing!
Last edited by VBA FTW; 12-16-2012 at 11:12 AM.
Out of curiosity though, how would I tweak this code to insert the columns side by side on my master sheet? Just wondering in case I ever need it in the future
Code for your request -
Please Login or Register to view this content.
Last edited by arlu1201; 12-16-2012 at 12:25 PM.
Sorry, I may have gotten ahead of myself here, this new code to put the columns side by side isn't working. The msgbox pops up, but no data is being copied over
Last edited by VBA FTW; 12-16-2012 at 11:50 AM.
Sorry, i edited the code. Please try again.
So close, it's pasting the data into column D, but it isn't moving to the next column so it's replacing the data each time the loop is ran. If I have 3 workbooks, the data would preferably need to be in columns B,C, and D on the master file. If we can't start at column B on the master file, I can probably adjust my other project I intend to use this for to suit the needs of the macro. I really appreciate your help on this
Last edited by VBA FTW; 12-16-2012 at 01:33 PM.
Do you have headers in row1 and data starting from row2? If the data needs to be copied side by side, what do you want as the header for each column?
Ah yes, sorry for not mentioning that. The master file will have a header in each column. It's pulling an identifier number from B9 on the master file with the formula =$B$9 in B1 on the master sheet. So my data WOULD need to be pasted to the master file in column B, starting at row 2. I hope that doesn't complicate matters. Note, this is for a different project, I will still be using the 1st macro you provided that inserts via rows rather than columns...so don't feel like I'm running you in circles here. Thanks again, I greatly appreciate your help
Yes thats what i asked the code to do. Check in row 2 and populate the data. Is it not working when you tried again?So my data WOULD need to be pasted to the master file in column B, starting at row 2
It is populating the data starting at row 2, but, it's pasting all data into column D so it's overwriting everything. I've attached a workbook so you can see the results of the macro. In this instance, I only had 2 files in my folder, so there should be one data set in column B, and another data set in column C. As you can see, currently everything is being sent to column DOriginally Posted by arlu1201
Last edited by VBA FTW; 12-17-2012 at 10:51 AM.
I have corrected the code -
Please Login or Register to view this content.
Works great, thanks a bunch!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks