Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by greggpetersen75; 09-12-2013 at 09:58 AM.
Thank you Fotis, I made the updates you suggested and am reading the rules. :-)
Is there a simpler way to construct this With statement?
edit: question now added
perhaps
Please Login or Register to view this content.
Last edited by JosephP; 09-12-2013 at 10:05 AM.
Josie
if at first you don't succeed try doing it the way your wife told you to
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thank you JosephP, that worked perfectly
So I know this is a lot, and it works, but I was wondering if you were to code this would you construct it like this, or would you do something else? I am trying to learn to code like a pro.Please Login or Register to view this content.
I am no pro but I would write it like this
Please Login or Register to view this content.
Joseph,
I will definitly check that out! A couple of questions:
1. What does this command do:
2. Can you make it so that you can recycle that code? So if I was going to do 3 similiar Pivot Tables and I just wanted to change the data, "TimeKeepingDataTbl" or what would really be cool is somehow based on the Sub calling a Private Sub with this Pivot Table data, and some variants (depending on the Sub) containing this Macro I could change the variables within the Pivot Table.Please Login or Register to view this content.
For Example: If for Sub 1 I wanted the Pivot Expression to say:
But if Sub 2 called on the Pivot Table Private Sub the code would look like:Please Login or Register to view this content.
There is probably an If Statement that could do that, or is it just easier to copy and paste the pivot language for each Sub routine and make the tweaks for each one?Please Login or Register to view this content.
1 prevents the pivot table from trying to update while you are manipulating it
2 if you have common code you can put that into a routine and call it but in my experience there is rarely a case for a generic routine to create a pivot table-I do use several for applying formats or rearranging the layout of existing tables though
3 my name is actually Josie not Joseph :-) (or Josephine when I'm in trouble)
Thanks Josie!! I hope I am not boring you with my questions. I really want to do this the right way.
not at all-I wish my kids were as eager to learn! ;-)
So, I have x amount of records on Data worksheet. Part of my macro is to append records to this data worksheet on a monthly basis.
I do not want them to double append the data. So right now I run a macro to determine it by Pivoting on the Department (DRange) and the Month (MRange). If the user records are greater than 1 (I have a dummy record there), then I know the user, already appended the records once, so I run a routine that filters on the records based on DRange and MRange and use this command to delete the records:
There are 61 rows of dummy data including the headers.Please Login or Register to view this content.
2 questions, is this the best way to delete the records for the repaste, and is there a better way to count the records that exist for the current department and current month, so that I don't have to create a pivot to do it?
hard to say without seeing the workbook itself but you could probably use a countifs formula to check the data before you append it in the first place
This is the code I came up with. It worked pretty well.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks