I'm new to VBA coding, and I'm trying to archive some data from our main job log. What I'm wanting to do is to have a pop up ask you what date range to archive. So if I have three years worth of work on the main log, and I want to archive halfway into the current year, all the way back to the beginning...or any other range... A window pops up, saying enter start date, then enter end date.
Once entered, it will go to the proper page, select the appropriate data by date range (columns B:L), and copy the data to a new sheet. Ultimately, I want the new sheet copied to a completely different workbook dedicated to archives.
I found some code on line, I don't recall where I got it...so if it's yours, please say so to get the credit. I'm not claiming.
Sub archive_date_range()Dim Startdate As Date, Enddate As Date ' assign the correct format to the variables
Sheets("MF_CS").Select ' ensures the correct sheet is selected
Columns("C:D").Select ' selects the correct start and end date columns
Selection.NumberFormat = "m/d/yyyy" ' assign the correct format to the column
Startdate = Application.InputBox("Enter the Start Date range for archive") ' pop up to input the start date range
Enddate = Application.InputBox("Enter the End Date range for archive") ' pop up to input the end date range
ActiveSheet.Range("C5:D1000").AutoFilter Field:=1, Criteria1:=">=" & Startdate ' testing date range
ActiveSheet.Range("C5:D1000").AutoFilter Field:=2, Criteria1:="<=" & Enddate ' testing date range
End Sub
So for right now, I'm just trying to get it to select the right date range, so that I can NEXT figure out how to pass that as a new variable to the select and copy functionality.
When I execute it, for now I'm physically selecting the log sheet "MF_CS", and then executing the macro. I get
Run-time error '1004'
Application-defined or object-defined error
Would love someone that knows what the heck they are doing to tell me what I'm doing wrong after they stop laughing.
And ultimately, if someone knows of a better way to do this... I would love to hear it.
Thanks!