Marco for copying data, clearing data, moving data.

**jimcclure** · 04-19-2024, 04:02 PM

I am looking for a macro that would do the following:

On specific date or say the 25th of each month if the spreadsheet is opened up the macro would copy A1-A14:L1-L14 then paste it in a specific workbook name PR. Each month it would make sure to just copy and paste under the previous months data. Then the macro would take H2:H14 and copy the current values to G2:G14 then save the file.

Basically I am trying to move data to another workbook for tracking and history and then use the main workbook for a month tracker with updated data.

Is this something I can get help writing or can you point me in the direction of where I could find some information to achieve this goal?

**jdelano** · 04-20-2024, 06:11 AM

VBA can do this for sure. You'll want to use Workbook_Open() and check today's date to see if it is the 25th (what happens if you open the file multiple times on the 25th?)

    If Day(Date()) = 25 then CopyData()

CopyData being the sub procedure with the actual code to copy and range data to the other workbook. Then delete the data in the current workbook.
https://www.exceldemy.com/excel-vba-...ther-workbook/

edit: fixed if statement, Day function

edit 2: Also, once you have some code, if you run into any issues, post your code with what issue you're having and where it is happening.

**sintek** · 04-20-2024, 06:15 AM

This will give you an idea...
You did not stipulate where the data A1:L14 is to be pasted in PR workbook...
Below code assumes Col A...

Sub Auto_Open()
Dim Path As String, Rng As Range
Path = ThisWorkbook.Path & "\" '! Change to Path housing PR book...
Set Rng = Range("A1:L14")
If Format(Date, "dd") = 20 Then '! Change to day required...
    With Workbooks.Open(Path & "PR.xlsx")
        Rng.Copy .ActiveSheet.Range("A" & Rows.Count).End(xlUp)(2)
        .Save
        .Close False
    End With
End If
Range("H2:H14").Copy Range("G2")
ActiveWorkbook.Save
End Sub