Hello all! I've been trying to figure this out but have gotten nowhere. I'm trying to pull data from multiple worksheets if there is any in that column, and place it in a master worksheet, all in the same column with no spaces between them.
Is that possible at all?
EDIT:
Also, if there is an instance where there is a repeat of the same exact info being pulled, it should be skipped.
Last edited by bcas77; 07-10-2013 at 03:26 PM.
Anyone have any ideas where I could start?
Are the worksheets in the same workbook?
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]
Yep. All the data is coming from the 'L' Column in each of the worksheets, and is being placed into the 'B' Column in the next worksheet.
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
What do I need to change the Summary to? Would it help if I posted my workbook?
Would it help if I posted my workbook?
Yes and you will get a speedy solution.
Testbook.xlsx
There is a sample of my workbook. All my sheets that I will be pulling data from are labeled 'Section X' where X is a number from 1 - N which is the last number in the workbook.
Have you tried Arlu's code? You seems to have lots of merged cells and tend to create problems when you loop through a range.
Yeah, I got a debug error from ".Range("L2LL" & 1row).Copy Worksheets("Summary").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)"
Alright, I just changed that code to read
and I got a debug on the Worksheets("Recommendations") line before MsgBox "Done". It told me the problem was that I have merged cells.Please Login or Register to view this content.
Is there any way to to have the remove duplicates start from B6 and work its way down? Or better yet, when it pulls data from the Sections, if it could start from L10?
Last edited by bcas77; 07-11-2013 at 11:16 AM.
I knew that. Merged cells are the work of the devil.
I do not think this will solve the issue, you still need to unmerge the cells first.Please Login or Register to view this content.
Last edited by AB33; 07-11-2013 at 12:10 PM.
Try this code
Please Login or Register to view this content.
The merged cells are not in the required area. They are above row 10. So if you need row 10 onwards to be copied, try the code in post 13.
In which sheet? The Recommendation or the Sections?
I'm still getting a "To do this all merged cells need to be the same size" from 4th to last line (worksheets("rec.."))Originally Posted by arlu1201
In Sections.
When you get the error, which row of code is highlighted in yellow?
Try this code
Please Login or Register to view this content.
Worksheets("Recommendations").Columns("B:10")....
It's also presenting my data to me in a strange format. I get 4 spaces and then the data from section 3, then 14 spaces then data from section 1, space, 2nd data from section 1.
Try the updated code in post 18 and see if it helps.
I did, and got the same error message. The formatting from the code in post #13 actually works better for some reason. I get the data from section 1, then space, then the 2nd data and data from section 3, so that's a lot neater. But I still get the error message for both on that same line.
Thank you so much for helping me so far by the way, I've been dreading trying to do this on my own and you're being a great help!
Try changing this line fromtoPlease Login or Register to view this content.Please Login or Register to view this content.
The second line of that edited code turned yellow with the same error message
I tried this code just now and it worked -
Please Login or Register to view this content.
I no longer have an error message, but when I tested by placing the numbers 1 - 5 in sections 1 - 5 and each one goes down one row, I am left with 5 and then 1 in the recommendation column, with a blank space above them.
Okay, I did some more testing. I put a - g in section 1, h - n in section 2, 1 - 7 in section 3, and o - u in section 4. I got these results in order: space, o, p, q, r, s, t, u, a, b, c, d, e, f, g.
It only went to line 20 and then stopped adding in the results.
Can you please upload this file?
Oh! I got it to work! I kind of mixed your code from #13 and #24 and it was perfect.
Please Login or Register to view this content.
Actually this code is working fine now - it was my mistake...i had missed out the sheet name - Recommendations while incrementing the counter -
Please Login or Register to view this content.
If you usev/sPlease Login or Register to view this content.you can always ask the code to start from a particular row instead of 2. If you use the 2nd one, it will start immediately after the header. But in cases, where you want it to start at row 10 or row 20, you can use the 1st approach.Please Login or Register to view this content.
Just a note for you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks