LinkBack URL
About LinkBacks
I was given the following code...
I want to change the pasting destination. I am not sure how to change this code to do this.
Please Login or Register to view this content.
Thanks!!!
Hard time changing this code
I was given the following code...
I want to change the pasting destination. I am not sure how to change this code to do this.Please Login or Register to view this content.
Thanks!!!
The destination for the paste is the cell selected by this line:
So the start point for the paste will be column A in the row after the last row of data in column A.Please Login or Register to view this content.
What do you want to change it to?
Hello there,
This is the line of code that is stating where to paste the information
Please Login or Register to view this content.
The below line of code is selecting the first worksheet (tab order in your workbook)
Please Login or Register to view this content.
The below line of code states that the variable LastRow now references the first empty cell in column A's row
Please Login or Register to view this content.
Finally the below line of code selects the cell in the last empty cell in column A and that's the destination it is set to paste into.
Please Login or Register to view this content.
If you let me know where you are trying to paste I can alter this code or you if you are still not able to do it after reading this.
Thanks!
what do you want to change it to?
btw, it's a bit extreme using doubles for row/column/sheet counters!
there's also no need to select anything here
Please Login or Register to view this content.
Last edited by JosephP; 05-31-2012 at 08:40 AM.
Josie
if at first you don't succeed try doing it the way your wife told you to
Ok...
1) The cell destination of "A" is fine
2) I want to give it a specific worksheet destination...ie.Master Data Collection
3) If there is a better way than this, please let me know.
My goal is to create a Master Pivot table on a Summary Report Tab. I do not want any other data on this tab. I was trying to use the shortcut "ALT+D+P" to create a Multi Range Pivot table but could not get the layout I was looking for. I was then told to copy all of the data from each sheet to a Master sheet and then create a pivot table from that.
If anybody has a better process or can improve on this, please share with me...I am open to any and all suggestions!
Thanks!!!!
change Worksheets(1) to Worksheets("Master Data Collection")
but be aware that if that is not the first sheet in the workbook, the code will copy its data too.
Is there a way to resolve that?
yeah-I reckon this oughta do it
Please Login or Register to view this content.
Maybe try
Please Login or Register to view this content.
Sorry, took way too long to respond - problem already solved
What are your opinions or ideas about creating a Master Pivot from Multiple worksheets? Is this the best approach or does anyone have a better process?
I gave you my thoughts in your other thread so will just wait and see what anyone else adds. ;-)
I am not familiar with the ADO process you mentioned in the other thread...can you explain how this would work?
this thread has an example: http://www.mrexcel.com/forum/showthread.php?t=315768
personally I would prefer to have one large table and get rid of the separate sheets but that's your call.
I have attached the sample workbook I am experimenting with.
If I get rid of the individual pivot tables and do your suggestion, what would the code be?
which suggestion are you referring to?
Just creating a single master pivot table instead of pivot tables on each worksheet. I think you were also talking about using ADO.
but still keeping the separate data sheets?
For accuracy purposes and because theses sheets are used for additional processes, I have to keep the individual sheets.
ok then. something like this oughta do it.
I cleared the data and tried to run the Macro you created but get the following error...
"Method 'PivotTables' of object'_Worksheet' failed"
How do I resolve this error?
Please Login or Register to view this content.
the code assumes that there is already a pivot table (even if it's blank) on the master sheet.
I guess I'm confused! What is this code supposed to do?
it replaces the existing pivot table on the master sheet with one built form all the other sheets. did you test the example workbook? (what did you mean by "I cleared the data"??)
I thought your code would create the pivot table from all of the worksheets...not replace an existing table. So I cleared the worksheet and ran the code but got the previously mentioned error.
it does create a pivot form the existing data but I set it up to remove the existing pivot table first. here's a version that won't bug out if there isn't a table already there
Please Login or Register to view this content.
This works great!!! I have only one last question...So we have a column that shows the difference between Serial Rcvd and Serial shipped. Is there a way that if this difference is 0 then that data row gets removed or omitted from the table? I would like to see a table that only shows devices owed.
I could probably change the table so that it only shows data for owed devices-i.e. only extracts data where the Shipped column is blank? you wouldn't be able to see overall totals of shipped and received though-would that be a problem?
The main reason for this master table is to show the number of devices owed. So as long as I still see the Item, RSA and then the # of devices owed, that would be ok. I just do not want to see a whole bunch of "0"'s in the table for devices owed. So if there is a way that if serial rcvd - serial shipped = 0 this row is hidden, deleted or omitted, then that would be AWESOME!!!!
try this one
note: since it only selects data where the shipped info is blank, the last two columns are fairly unnecessary!Please Login or Register to view this content.
This works well....My only issue is that I would like to see any balance owed to the customer. This code only shows blanks, doesn't take into consideration the items that have been partially shipped. Is there a way this code could be changed where it ignores the data in which the serial rcvd and the serial shipped are the same value?
I reckon so-gimme a few minutes as my sql is a little rusty.
how about this-it just autofilters the table?
Please Login or Register to view this content.
I assume there is no way of accomplishing this same thing without the filters?
possibly-what's the issue with the filters?
Truthfully....Im lazy and just wanted something quicker.
ya lost me-the code does the filtering for you
Sorry for the confusion...if I wanted to accomplish the same thing without the filters, is that possible...I'm just weird and would like to avoid filters if possible.
most anything is possible but it'll take a lot of sql I reckon. you'd have to union all the tables together, aggregate the data to check which have more than 0 owed overall and then pull the detail rows for the matching aggregates to put into the pivot table (otherwise you wouldn't have anything to drill down to). be much easier in a database, or with one table of data...
When you say one table of data...do you mean this current table with the filters?
no, I mean one source data table (i.e. all the data on one sheet) not pivot table.
Gotcha...I will work with what you provided me...the pivot table with filters....Thanks!!!!
Joseph,
One last thing...can you look at some of my other posts and see if you can point me in the right directions with them?
Thanks,
Dan
any in particular? ;-)
I have a couple threads about trying to pull an attachment from my outlook and add to an existing workbook, and I am having a hard time finding information clear enough that I can walk through it and learn it. As you can tell, I am not the best with this code writing and would like to learn as I move forward with this.
if you provide the links I'll take a look when I can but I have some client work that'll likely keep me occupied for the next few days.
I understand...with all of the help you have provided I will wait. I will bump this post again in 3-4 days, if I haven't heard from you or if I haven't found the answers. Here is the link http://www.excelforum.com/outlook-pr...-to-excel.html
Re: Hard time changing this code
Joseph need your help!!!!
I have gotten the code mentioned in early threads formatted and working that way I wanted it to on my sample workbook. However, when I moved to my live workbook and ran the macro, I got the following error message...
"Run-time error '-2147467259 (80004005)': Query is too complex."
What do I need to do to resolve this?
When I select the Debug option this portion of the code is highlighted...
What do I need to change to resolve the above error message?Please Login or Register to view this content.
I need some help...someone mentioned setting... objRS.Open Join$(arSQL, " UNION ALL ") as a variable but I am not real clear on how to do this...ideas?
how many sheets do you actually have?
I currently have 134 worksheets, but I add a new worksheet during Mon-Sat. Now, I have some worksheets that show that all of the devices have been shipped and I could move those to a new workbook...is there a way to code this...every time the serial shipped column and the the serial rcvd column counts are equal, then the worksheet gets move to a workbook named something like...Closed RSA's?
Does the qty of worksheets really matter with the code you created?
time to end this. your database structure is wrong and is maiming your reporting. fix the database and all else falls into place. if you don't, everything will be a battle and I won't enable that.
Can you help me or point me in the right direction to fix this database issue?
PS on a fee basis I would charge you $200 an hour for this and you would be wasting money. rethink how you do what you do and everything is easier and cheaper. :-)
Re your last, yes. Again you need one table. How you get there will depend on the situation.
Thanks...I guess i will have to figure out some other way of collecting all that data and then create a pivot table from that. Thanks for your help!
Is it a problem that these worksheets have pivot tables on them? Does that matter?
That's what I was trying to do...just thought from our previous discussions, using this ADO code would be best thing to do, but somehow thongs got all messed up.
seriously if you have 134 sheets of data, you absolutely HAVE TO rethink what you are doing now. I don't care what you think your options are, it has to be done
I don't disagree that this worksheet needs to change...short of saying screw it...I'm not sure what I am going to do. I have been looking for a better way to do this since I have been in this position last year. I just haven't been able to get a good replacement process...this sucks!!!
Ok, so I've made my point. ;-)
(to the mods, no I'm not touting for business)
You already have code to create one table from all your sheets- I suggest you start there for this pivot table. In reality, the whole process needs rethinking from the point of data extraction, as it seems hugely inefficient at present and most of what you have could be done far better in a database.
Agreed....guess I need to find and dust off my access books and learn access again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks