+ Reply to Thread
Results 1 to 5 of 5

VBA script to copy sheet with in document at specific time

  1. #1
    Registered User
    Join Date
    11-13-2020
    Location
    United States
    MS-Off Ver
    2016
    Posts
    3

    VBA script to copy sheet with in document at specific time

    Hey all,

    Relatively new to scripting in VBA. I am trying to write a script that will run at 4:00pm each day. What it will do is clone a master sheet and rename the clone to the current date. What I have right now is not right currently and I know that, so i was going to see if anyone out there could support me in writing and understanding this script I'm trying to write.

  2. #2
    Registered User
    Join Date
    11-13-2020
    Location
    United States
    MS-Off Ver
    2016
    Posts
    3

    Re: VBA script to copy sheet with in document at specific time

    Here is what I have so far


    Here is the script that needs to run at 4:00pm each day

    Sub copyWS()

    Set ws = Sheets("Master Sheet")
    ws.Select
    ws.Copy after:=Sheets(2)
    Set ws = Sheets("Master Sheet (2)")
    ws.Name = Format(Date, "mm-dd-YY")
    Worksheets("Master Sheet").Range("A2:A500").ClearContents
    ActiveWorkbook.Save

    End Sub


    And here is my start at the process for running it each day



    Private Sub CheckTime()

    ScheduledTime = Now + TimeSerial(0, 15, 0)
    Application.OnTime TimeValue("16:00:00"), , TimeValue("23:59:00")



    End Sub


    My biggest confusion is what to insert here
    Application.OnTime TimeValue("16:00:00"), #########, TimeValue("23:59:00")

  3. #3
    Registered User
    Join Date
    11-13-2020
    Location
    United States
    MS-Off Ver
    2016
    Posts
    3

    Re: VBA script to copy sheet with in document at specific time

    After Clicking around for a a while longer i have worked towards this. It is still not all the way there so if anyone has any pointer.

    Sub copyWS()
    Dim CurrentTime As String

    CurrentTime = Time

    If CurrentTime > ("16:00:00") Then
    Set ws = Sheets("Master Sheet")
    ws.Select
    ws.Copy after:=Sheets(2)
    Set ws = Sheets("Master Sheet (2)")
    ws.Name = Format(Date, "mm-dd-YY")
    Worksheets("Master Sheet").Range("A2:A500").ClearContents
    Sheets("Master Sheet").Activate
    ActiveSheet.Range("A2").Select
    ActiveWorkbook.Save
    Else
    Application.OnTime Now + TimeSerial(0, 5, 0), "copyWS"
    End If
    End Sub

  4. #4
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    274

    Re: VBA script to copy sheet with in document at specific time

    Hello,

    Step 1
    Put a line of code in your workbooks Open event (Workbook_Open) that says this:

    Application.OnTime TimeValue("16:00:00"), "CopySheet"

    That is 24 hour format time so that would be 4:00 Pm.

    Step 2
    Add a module and put a macro in it named CopySheet ie..

    Sub CopySheet()

    'Whatever code you want to run at 4:00 pm here

    End Sub

    When you open the workbook it will initiate the OnTime command to run the macro named 'CopySheet' at 4:00 pm
    If you don't want that to happen when you open the workbook, then just put that line of code in whatever event you want to happen in, or a button or whatever you want.

    Hope that helps.

  5. #5
    Forum Contributor
    Join Date
    01-02-2020
    Location
    Idaho, USA
    MS-Off Ver
    365
    Posts
    274

    Re: VBA script to copy sheet with in document at specific time

    So this could be your code in your module (Keep in mind you can only run this once each day unless you delete the new sheet it creates because it tries to rename a new sheet to the same name as the last time you ran it which is the date of that day).. also you don't need to activate sheets to copy them.

    Sub CopySheet()
    Dim ws As Worksheet


    Set ws = Sheets("Master Sheet")
    ws.Copy after:=Sheets(2)
    ActiveSheet.Name = Format(Date, "mm-dd-YY")

    Worksheets("Master Sheet").Range("A2:A500").ClearContents
    Sheets("Master Sheet").Activate
    ActiveSheet.Range("A2").Select
    ActiveWorkbook.Save


    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VB script or macro to check rows of sheet 1 and match with sheet 2, if found copy to shet3
    By adityamishra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-27-2017, 12:50 PM
  2. How to use script to print the specific sheet of Excel?
    By woshichuanqilz in forum Excel General
    Replies: 1
    Last Post: 07-08-2017, 02:25 PM
  3. [SOLVED] Copy a specific range to another sheet adding all the workdays for a specific time period
    By MariaPap in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-05-2014, 02:19 AM
  4. [SOLVED] VBA Script to open a specific File adn activate a specific sheet within that sheet.
    By Kramxel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-05-2013, 01:31 PM
  5. Script for sheet name containg specific text
    By vimalanathk in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-08-2013, 01:44 PM
  6. Find specific value from one document and copy it to another document
    By v_nastey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2013, 09:02 AM
  7. Make the script run on a specific Sheet
    By datahouse in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2011, 05:41 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1