Moving info from 1 tab to another depending on date??
Hi everyone!!
I think it is a macro i am after for the attached?? I want to take money outstanding by a certain team for 1 to 30 days 31-60 days etc and move this to a totals tab i suppose i would call it....
Not really explained very well but if you could have a look at the attached and any help would be much appreciated.......
Should the data be taken from the outstanding tab? Should it be 1 to 30 days from the current date?
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]
Yes and yes, thanks...
Any idea's with this then arlu1201??Originally Posted by arlu1201
I have updated the code that you had in the Outstanding sheet to thisThe code will insert a formula in column G which will give you the time intervals. Then you can create a pivot to get the summary you need. (Pivot can be automated too).
Please Login or Register to view this content.
The following are the fields for your pivot -
Row Labels: Team
Column Labels: Time Intervals
Values: Sum of Invoiced (Gross)
The code is great thanks!!
You say the pivot can be automated, does this mean the pivot can be generated automatically?? If so how do i do this please??
Cheers
Frazz
I have tried the code you gave and it is great but it conflicts if the date is exactly 30, 60 or 90 days prior to today's date, every other date works find just not when they fall on either of those 3???? (when it is 30, 60 or 90 days the G column says 90+ days as if its conflicting)
Ok, i will check into the conflict and let you know.
Regarding the pivot, you can have a code that creates / refreshes it at the bottom of your above code.
Sorry to keep asking but do you know the code i need?? If it could generate the pivot on a new tab that'll would be great???
Thanks for all your help!!!!
I checked it again by changing some dates and it works perfectly fine. Can you check it again at your end?
Regarding the pivot, you can create it the first time manually. Then we can have one line of code at the end of the previous code to refresh the pivot, each time the outstanding tab gets refreshed.
Re: Moving info from 1 tab to another depending on date??
I've added a pivot into a new tab which works fine, only issue is that the data from the PIVOT TAB then goes onto the outstanding tab at the bottom??? It must be picking that up too??
I've attached a copy....
You have this line of codeAdd the pivot tab to itPlease Login or Register to view this content.Please Login or Register to view this content.
Use this updated code. It will refresh the pivot for you as well.
Please Login or Register to view this content.
The conflict actually happens when the Invoice Date is the same as Today's date, anyway round that??
Last edited by arlu1201; 04-21-2012 at 07:12 AM.
Just change this linetoPlease Login or Register to view this content.Please Login or Register to view this content.
I'm pretty sure it is all sorted now, thanks for all your help!!!
I've added some data on to the April tab which goes into the outstanding tab ok and the pivot table refreshes with this new data but it deletes the older data (from the January tab). I'm assuming this is because the pivot is only picking up data from the initial RANGE i selected when i made the initial Pivot?? I cant select the whole column for the pivot range because the pivot won't work if it has blank cells i believe?? Is there anyway round this??
Thanks
Frazzfreeman
I have sorted it now!! just changed the field of the pivot to cover the whole column and took the blanks off the table!!
Glad it worked for you.
Thanks for all your help!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks