Hello
Im trying to consolidate (merge) data from 20 worksheets onto a summary sheet.
here is the code
however, its giving me an error on the part below:
Please Login or Register to view this content.
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
its saying "application defined or object error"
can anyone pls help me out here. im completely lost/confused. pls and thx you.
Last edited by jw01; 01-20-2011 at 11:35 AM.
Sheet names cause a lot of grief. A sample workbook you run this macro on including sheets it won't work on is usually a huge help.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hello
Thanks for your help.
I have attached a sample workbook. For some odd reason, when I run the macro, it runs only the first desired row in sheet "rep1" and then it shows me an error.Im trying to show all the data from all worksheets into the "summary" sheet with live links.
I do have a another question; I have a "sort" button on each worksheet, I noticed when I created a sheet w.o a macro that consolidated all the worksheets with live links to each cell, when I sorted on i.e. "rep1" sheet, it also sorts in the consolidated worksheet i created. any way to avoid that from happening, if this macro cant be fixed?
Again, thxs for your input. I hope this can be resolved cause its taking me forever.
Your macro is actually running for all sheets but it's putting formulas all the way out row8 until it hits the end of the worksheet. Not sure you noticed that.
The use of "formulas" to connect all your sheets is a losing process. Since you're using VBA anyway, there's no need for that.
This is how I would collect all the data into the summary sheet without formulas, but still make sure the Summary stays updated... this macro goes into the Summary sheet module itself and is active all the time:
Since you have to switch to the other sheets to make your changes, coming back to the Summary sheet will cause it to "reassess" in realtime, no formulas needed.Please Login or Register to view this content.
Last edited by JBeaucaire; 01-20-2011 at 10:59 AM. Reason: corrected macro
Hello
Thx you again, it's working exactly how I would like it too, awesome!
thx you very much, i truely appreciate it! God bless!
Last edited by jw01; 01-20-2011 at 11:35 AM.
quick question
because I have the sort button on each page, it's sorting per requirement - which also sorts on the "summary" sheet.
is it possible for it to still refresh the data, but not sort if i.e. i sort by oppor. name in any of the other worksheets? kindly let me know. thx you.
I had to change the Ws.sheet "R*" bc once I changed the worksheet to i.e. John Doe, the macro was no longer pick it up...so just made it
It works, but I hope that is the proper way, thxs!Please Login or Register to view this content.
also, if you can kindly help me one last time with:
"quick question
because I have the sort button on each page, it's sorting per requirement - which also sorts on the "summary" sheet.
is it possible for it to still refresh the data, but not sort if i.e. i sort by oppor. name in any of the other worksheets? kindly let me know. thx you."
Last edited by jw01; 01-20-2011 at 12:27 PM.
The technique I've shown you recreates the Summary sheet each time you bring it up onscreen to refresh all values from the other sheets since you change things.
The new Summary will have the data in the order it appears on the other sheets.
The sort buttons on the Summary sheet will apply the sorting as needed on that page.
Hello thxs for your prompt response. and yes your correct, the sort button do function how i would like them to on the summary page.
final question
-is it possible for the macro to show only the top 50 results?
right now it rolls up all the worksheets (tables that have data) which is great! but is it possible for it to only top 50?
-is there a way to conditional format it or anything? thxs for all your help.
50 rows from each ws?
No, not 50 rows from each worksheet
I need:
-top 50 of all the worksheets
i.e. if all sheets have 20 results so that is about 4000 results in total, ...is it possible to show just the top 50 based on forecasted revenue (even better, the three buttons that i have)? thxs for the help.
Hello
I was given an updated sheet and for some apparent reason, when i implemented the macro to consolidate into the summary sheet, everything is thrown out of line.
I have changed the row references and column references, but to no avail. can you kindly have a quick glance and let me know whats going wrong, thanks.
The headers are all located in range (A8:T8)
The data is in rep worksheets is in (A9:T59).
In the sampe attached, it's taking all the headers? If you can kindly help and thx you.
Summary is password protected.
The insertion of those blank lines at row 9 you know is the cause of all this. Why in the world...? *sigh*
Ok, try again. Remove the blank rows at Row9 and everything should work as designed. Never put blank rows inside a table, it breaks so much simple functionality forcing you to add more plumbing... for what? White space? Put a white border around the row8 cells if you really need white space.
Hi,
Thxs for looking into it. I have tried to remove the blank spaces in the table, but for some wierd reason, the header field is following down with it. i have unprotected the workbook (there is also a macro to unprotect it, placed in there). kindly let me know, thxs you!
Interesting, the inline paste command doesn't want to work, even with UserInterfaceOnly turned on.
I've cleaned up the protection macros, the sorting macros and got the main "activate" macro working now. You were trying to use the same macro on Summary as for all the other sheets even though the columns weren't in the same place. I fixed that.
Hello
I sincerely appreciate ur help with this matter. it's definitely made me learn something new for sure. one final question, if the rep1 etc worksheets are blank, the header appears on the worksheet, is it possible to make it show blanks or not show that worksheet? thx you.
Like so:
Please Login or Register to view this content.
Thx you very much! your great, have a wonderful day. God bless.
Cheers
Hello
I need ur assistance one final time.
in the "summary" sheet, in column A, i need to display # (i.e. 1-100 etc).
I tried to modify the macro but it's making things go out of place and not showing the data in range A:A in the summary sheet.
basically i inserted a new column so everything is moved to the right once. can you kindly assist me with that? pls and thx you..much appreciated.
Hello
I have managed for it to drop the data in the correct columns (see below)
im column A, it keeps erasing the data when the sheet refreshes....ur thoughts, thxs!Please Login or Register to view this content.
There's no need to unprotect/protect. I fixed the protection macros on your sheet and in the ThisWorkbook module so it gives permission to run macros on protected worksheets when the workbook first opens. So you can take that back out.
Here's your workbook back with my fixes back, a new numbering column A and the J2 references you asked about in a separate thread. The sheet will run macros even while protected due to the wb_open macro in Thisworkbook.
thx you so so much!...ur help was amazing, thx u so much. God bless you!
Hello
hope your doing well.
i have a quick question for you. i have had to make an adjustment. if you recall, on the rep1-20 worksheets, column "c" was the rep name, but now i have changed it to "GPO" with a drop down selection + added a condition, i.e. if it equals "Air" and "Pack" then make the range (A9:T58) etc green.
it works perfectly, however on the summary sheet, the lines are not coming out in the green. anyway to add that in, if you can kindly let me know, thx you.
Please attach an updated wb with any new ideas.
Hello
Thxs for your response. for some wierd reason, the macro is thrown off and its put me in a hole.
column "A" in any of the worksheets is not doing the simple 1-50 # showing, it goes out of order.....it's also showing "0" in the summary sheet".
can i kindly and pls get ur feedback. thxing you a ton!
also, added a new sort called"gpo". please note there is a ifstatement formula in column "y" on each worksheet for the conditional format. thx you.
Last edited by jw01; 01-25-2011 at 01:33 PM.
hello
ive managed to sort all the issues i was having relating to sorting and all but if yu can so kindly show me how i can add in the conditional formatting to show on the summary page, that would be great.
basically, ive set two conditions, if the gpo column shows "premier or amerinet", i want it to show the lime green i had ------> if that can appear on the summary, taht would be great. i have attached a copy. have a great day.
ive also chnged the reference frm dist. summary to sheet23 as i noticed the reason my sort buttons werent working was due to the fact the sheet name changed. let me know ur thoughts. thxs
Last edited by jw01; 01-25-2011 at 03:34 PM.
also, if i delete all the data in the worksheets, i.e. have a blank template, when i go to the summary sheet, the order 1-100 etc shows an error and thus, that portion does not calculate. it does the =n(a9)+1 but does not show 1-10000 etc it should or not show,if you can kindly help with that, thx you.
There's no need for the column Y thing. You can put the conditional formatting directly into the CF formulas you're using in the main sheet. Look at what I changed your on SE1.
I've added the CF formulas into the Summary sheet. Also, I think the changes I made fix your "blank template" issue, too.
Hello
its saying object defined error on the following line
?Please Login or Register to view this content.
That may be a difference in Excel 2003 vs Excel 2007. Try recording a macro of you putting conditional formatting (any formatting really) on a cell and posting up that code. We can compare for differences. Sure we can work it out.
Hello
it didnt give me an issue with 2003, it did on 2007.
however, on the summary sheet, it's still not coloring the line or applying the condition on it, for some odd reason? any ideas why? thx you, again.
See post #30 for my suggestion to proceed.
please see below entry
Last edited by jw01; 01-27-2011 at 12:43 PM.
Hello
I have managed to make it work, however.....on rep2 sheet, if i create an entry and call it "amenet"...on the summary sheet, it disregards the entries on rep1 sheet and only condition formats the item from rep2 sheet on the summary sheet....below is my code:
Please Login or Register to view this content.
Last edited by jw01; 01-27-2011 at 12:40 PM.
Use this instead, the CF stuff is too different between versions.
Please Login or Register to view this content.
Hello
I have another worksheet using the similar set macro from previous that i modified with your help.
On this new workbook the "amerinet" and "premeire" appear on column F
this is my code that is not picking up the conditional format...any suggestions, pls and thxs
Please Login or Register to view this content.
Using the R1C1 notation, column E is referenced as C5 (column5). So look for the RC5 reference (means "this row, column 5) and change that to RC6.
Last edited by JBeaucaire; 01-27-2011 at 07:47 PM.
Hello
One final request.
please see attached file.
in the summary sheet, i have flagged a column "red". how can i not make that column appear? i dont want to hide it bc the sales ppl in the field will copy/paste into another workbook, which will create issues as the "1" or w.e # will appear as well.
I'm just not sure in the macro, so if you can kindly assist me one final time. thx you and have a great day. pls let me know.
1) Delete the column from your layout.
2) Find the line of code that COPIES in the macro, notice it copies from A9:U????, change that to B9, right?
3) That's going to move your AMERINET/PREMIER column, too, so you'll have to adjust the RC5 reference again like I showed above. I don't see that correction in this workbook.
Post your code changes here so we can review them together. I'm making you figure this out so I'm sure you're able to decode what is going on properly.
hello again,
i created another workbook but got into some trouble.
the sort on the non summary tab are not working :S...im not sure at all. the sort on the summary sheet works perfectly but not sure what is causing this error. can you kindly have a look at it for me, pls and i really appreaciate it!
The hidden column C is empty, thus it's breaking the CURRENTREGION method employed in the sort command. Just put a value in C8 and it will work.
thx you for your help again.
Last edited by jw01; 01-29-2011 at 01:58 PM.
42 posts... (*phew*)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks