Copy Initial Date with airSlate SignNow

Copy initial date

hey Dan from Dan Wagner Co with a quick tutorial on how to take date filter data from one worksheet inside a workbook and move it to a new worksheet inside the same workbook so little backstory here this one is actually a question related to a tutorial I did earlier and you can grab that link out of the code comments which are included in the article which is down in the description of this video but this is a little bit of a smaller case then the original article was written about in this case we only have a single worksheet with dates in one place rather than many worksheets and we actually want the data to go to a new worksheet in the same workbook so before we dive into the code let's just make sure everything works as we expect so first things first I'm gonna run what I would call a happy path I'm going to pick two dates that exist within the range and make sure that they come out as expected on a new worksheet so I'll pick the 5th of March to say the 10th of March awesome we get a little message box confirming that the data was transferred our dates match from the 5th of March to the 10th of March and we can see that we're on a new worksheet so that's good stuff now let's check what happens when we pass in dates that don't actually make sense for the data that's available let's say if we try to get April 1st of 2016 to say April 15th of 2016 I'm going to start this one again and when we run the script there we get a nice warning that says oops those dates filter out all the data and you can see here on sheet 1 the original data source all we have left is the header column so when I click OK the script undoes those filters and we return to a nice jumping-off point again so now let's let's get into the code a little bit here the prompt user for input dates is actually a routine that we wrote in the original article and I use that one word for word for word line for line and nothing makes me happier than reusing a routine like this it's very very portable and very handy the create subset worksheet however is the the interesting place where we're doing our work here I'm going to skip the setup and exploration steps of the four step VBA process because those are detailed really nicely in the article and you can grab that information out of the article again from the link in the youtube video description box below I'm going to spend my time focused on the execution step which is where things really get interesting here so first things first we start off with a context manager in place on range full so range full is essentially column a to column J Row one to row sixteen we call the auto filter method on range full and for the field parameter we pass in the date column we know it's column eight which is column H as in hotel here the first filter criteria we want to apply is greater than or equal to the start date and the second filter criteria we want to apply is less than or equal to the end date so by setting up these bookends we are going to end up with exactly the rows that were interested in that match our users input to the prompt user for input dates subroutine all right so moving right along here we have what's called a guard clause up first in this if statement and what we're checking for here is a situation where the filters are a little bit too pervasive and we wind up with only the header row left so we're checking to see if this long statement evaluates to 1 because if it does way to one that means that all we have left are our headers and that's not really an interesting case so in that situation we throw out a message box we clean up our filters and we exit the routine but assuming that the count is anything greater than one we go ahead assign all the visible cells to a new range we create a new worksheet using the worksheets dot add method right here we set a target range which is simply cell a1 on the newly created worksheet and then we copy the result range which again is just the visible cells from the filtered range to the target range which is cell a1 on the newly formed worksheet and then finally of course we clean up all of our filters in a safe way because leaving filters executed on a worksheet is a recipe for disaster you're gonna get run time errors sooner or later and then finally we throw a message box to our user to let him or her know that the data has been transferred and so if you have any questions or concerns about how this works please don't hesitate to ask drop me a comment here or even shoot me an email that information is all handy in the linked article so thanks so much and have a good one