VBA to copy range of cells from one sheet and paste into the next available column of another sheet at a specific time each day, deleting the original

[Spoursy]

I have a range of data in a macro enabled workbook (L1:Q70) in Sheet23 that I want to copy and paste as values with source formatting into the next available empty column in Sheet41. I then want to delete the data in L1:q70 in Sheet23. I want to run this whole process an automated task at the same time every day. Is there a VBA solution to this. Thanks in anticipation. Spoursy

 

[excel_learnerz]

You can achieve this using VBA to copy the data from Sheet23 and paste it as values with source formatting into the next available empty column in Sheet41. You can then use Task Scheduler (Windows) or Automator (Mac) to run the process at a specific time every day.

Here's the VBA code for copying and pasting the data:

1. Press Alt + F11 to open the VBA editor.
2. Click on "Insert" > "Module" to insert a new module.
3. Paste the following code into the module:

Sub CopyPasteData()
Dim wsSource As Worksheet, wsDestination As Worksheet
Dim lastCol As Long
Dim rng As Range

Set wsSource = ThisWorkbook.Worksheets("Sheet23")
Set wsDestination = ThisWorkbook.Worksheets("Sheet41")

Set rng = wsSource.Range("L1:Q70")

lastCol = wsDestination.Cells(1, wsDestination.Columns.Count).End(xlToLeft).Column + 1

rng.Copy
wsDestination.Cells(1, lastCol).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
wsDestination.Cells(1, lastCol).PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

rng.ClearContents
End Sub

1. Close the VBA editor.

Now, you can manually run the CopyPasteData macro by pressing Alt + F8 and selecting it from the list. This will copy the data from Sheet23 and paste it as values with source formatting into the next available empty column in Sheet41. It will also clear the contents of the source range in Sheet23.

To run the macro automatically at a specific time every day, you can use Task Scheduler (Windows) or Automator (Mac) to open the Excel file and run the macro. Here are the guides for each platform:

• For Windows: Schedule a task with Windows Task Scheduler
• For Mac: Schedule a task with Automator and Calendar on macOS

Please note that you will need to adjust the steps in the guides to run your Excel file and the CopyPasteData macro instead of the examples provided.

Remember to save and backup your data before running the macro, as it will make changes to your worksheets that might be difficult to undo.

 

[Spoursy]

Thanks - This is absolutely brilliant and worked first time.