VBA Help for Copy Paste Date Range (14 days previous from today)

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    I'm needing some help on VB programming. Right now I have an excel sheet that i'm using for data scrubbing and paste into SharePoint there are 3 tabs. One tab ("Daily Data Dump")is where I dump the daily data, run a macro that removes a bunch of crap (Titles, total rows, sorts, adds few columns for sorting needs) then it pastes the information into the main data sheet ("Actual Data").


    Then I have another tab ("Sharepoint Upload"). What I would like to do is copy and paste the last 15 days worth of data from "Actual Data" into "Sharepoint Upload". The date column is B and its formatted to MM:DD:YYYY. I would also like to remove some columns in the process as well but that part is super easy. I'm just struggling to get the the past 15 days of data. Which is equal to about 13728 rows (1056 or 1057 rows per day). I would prefer us the date field then do it by number of rows from bottom so we can add to this sheet later.


    Also I would like to leave the Actual Data alone as its the raw data. And another wish list item would be an easier way to import that into SharePoint 2007 and overwrite an existing list each day. The list is used to feed reports that the users in the field click update on there excel sheet and it would update all the information on there dashboard for 2 weeks. Each day they can refresh it to see the new data (Adding a more current day but removing older days). Thanks in advance for all your help and Please let me know if there is anything else you need.

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    does:

    Code
    Set xx = Range(Cells(Application.Match(CLng(Date) - 14, Range("B:B")), 1), Cells(Rows.Count, "B").End(xlUp))
xx.Offset(1).Resize(xx.Rows.Count - 1).EntireRow.Select

    select the right rows to copy?

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    What I would like to do is select all the data from the last 15 days prior to today's date in the "Actual Data" tab (Including the header Row 1), Then copy and paste all of it into the "Sharepoint Upload" tab. Then I can handle the removal of 10 columns once its in that tab.


    But then I would like to import all that information to a SharePoint List and have the system overwrite the same file each day. I'm not sure how to do this or if its even possible? The list needs to be set up as a table and imported into sharepoint. Unless someone knows an easier way..

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    Yes, with the Actual data tab active, do the two lines of code select the right data to copy? Headers and stuff later (I'm not overly familiar with sherepoint but you've already given me a clue. (I put 14 in the code, it might be different) and you also say prior to today's date - so there is today's data rows there too; do you want to include today's data in the copying process?

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    There is not data for today. Its all in a rears, so we would not have today's data until tomorrow.

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    again,

    Quote from p45cal;647818

    with the Actual data tab active, do the two lines of code select the right data to copy?

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    Yes sorry. This worked perfectly. Thanks for all your help.. Now its a sharepoint question.. Any ideas of how to update sharepoint?

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    OK. For the copying; assuming you're copying to a blank sheet Sharepoint Upload, then try:

    Code
    Set DestSheet = Sheets("Sharepoint Upload")
With Sheets("Actual Data")
  Set xx = .Range(.Cells(Application.Match(CLng(Date) - 14, .Range("B:B")), 1), .Cells(.Rows.Count, "B").End(xlUp))
  .Rows(1).Copy DestSheet.Rows(1)
End With
xx.Offset(1).Resize(xx.Rows.Count - 1).EntireRow.Copy DestSheet.Range("A2")

    (untested)
    Now I'll look into what's special about uploading to a sharepoint location..

    Re: VBA Help for Copy Paste Date Range (14 days previous from today)


    Quote from p45cal;647839

    Now I'll look into what's special about uploading to a sharepoint location..


    To convert to a table:
    DestSheet.ListObjects.Add(xlSrcRange, DestSheet.Range("A1").CurrentRegion, , xlYes).Name = "List1"
    adjust name of course.


    A bit of Googling implies that updating a Sharepoint list is easier using vba than using the GUI, however, methods used are very dependent on which versions of Excel/Sharepoint and even Access you may be using. I don't know much about Sharepoint!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login